Return Values Help Required 😁

Mad

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

SoS | Dan Palenchar

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

Mad

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!