Return Values Help Required 😁

Options
Mad
Mad ✭✭
edited 09/21/23 in Formulas and Functions

Hi all,

I am hoping that someone can offer advice or a solution to my following questions. I have attached screen grabs and all the information I think will be relevant, please see below

I have two sheets that I use as an asset tracker and the screen grab you see above has a form linked to populate the columns. I would like the "Asset Status" column to reflect the "Booking" column but they have different values. The "Booking" column has "In"/"Out"/"Maintenance" which I would like to represent as "Booked - In"/"Booked - Out"/"Maintenance" in the asset status column.

The second sheet is a database that I run a dashboard from and I want the "Asset Status" column to reflect the first sheet "Asset Status" as "Booked Out" or "Maintenance" if the "Serial Number" is found and if it isn't I want the second sheet "Asset Status" to display "Booked - In"

I currently have the following formula in the "Index Status" column =INDEX({Booking In/Out/Maintenance Range 3}, MATCH([Serial Number]@row, {Booking In/Out/Maintenance Range 1}, 0))

I have the following formula in the "Asset Status" column =IF(AND([Checked-Out Maintenance Serial]@row = "Found", [Checked-Out Maintenance Barcode]@row = "Found", [Index Status]@row = "Book - Out"), "Checked-Out/Maintenance", "Checked-In")

Thank you in advance for reading my query I really hope you can help

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Options

    For "Asset Status" to reflect Booking use formula:

    ="Booked - " + Booking@row

    Regarding:

    The second sheet is a database that I run a dashboard from and I want the "Asset Status" column to reflect the first sheet "Asset Status" as "Booked Out" or "Maintenance" if the "Serial Number" is found and if it isn't I want the second sheet "Asset Status" to display "Booked - In"

    From this I am not sure what would differentiate "Booked Out" or "Maintenance". If those are values in your source sheet than you can simply pull them in. If not, you could use a formula to manipulate the output based on the data coming in.

    To handle the second part (display "Booked - In" if the "Serial Number" is not found), I assume that if it the "Serial Number" is not found you are getting the #NO MATCH error, so you can nest your INDEX(MATCH()) formula in an IFERROR() with a return value of "Booked - In".

    Dan Palenchar | School of Sheets Solutions Consulting (Smartsheet Aligned Gold Partner)

    Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

    Smartsheet Tutorial Videos: schoolofsheets.com/youtube

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.

    PS - If you have a follow up response use @Dan Palenchar so I get notified of your reply!

  • Mad
    Mad ✭✭
    Options

    Hi Dan,

    Apologies for the delayed thank you, I had a long weekend which I have returned from. Cheers for taking the time to read through I appreciate your help on this although I am not sure how to implement your suggestions into my formulas as I tried to add IFERROR to the INDEX(MATCH) without success.

    In regards to the "Booked Out" or "Maintenance", this status is used to differentiate between which assets are "Booked Out" to individuals to use, which are sent away for "Maintenance" and/or "Booked - In" represents the asset is available to be "Booked Out" to a individual or sent away for "Maintenance". The second sheet runs a dashboard that displays how many assets are "Booked - In"/"Booked Out"/"Maintenance" and the first sheet is the form used for individuals to select the option they require.

    Ideally I would like to second sheet to update from the first automatically to eliminate the need for me to manually update one sheet from another and I know it can be done I simply do not have the technical ability to write the correct formula!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!