How to retrieve a cells data when the cell doesn’t exist yet

Chris Martin

Hi There .

Maybe not possible but hoping for some advice.

We have an order input sheet. this has a transaction id column that automatically assigns a number. When this number is assigned the details are moved to a holding sheet.

My idea is this

I would like the ability for different departments to have their own sheet when working with this order. For example that picking is the first to get it using index match of the transaction id and add more data ,The holding sheet also has index formulas to bring that data back so an orders info is all on the same row. and so on.

My problem is i’m not sure how to have the picking sheet reference a transaction id that doesn’t exist yet. EG if the order sheet is creating the id how can i create formula that grabs the next id when it’s created?

the reason i’m doing this is to try to avoid the moving or copying of rows as this brings across lots of columns that are not relevant for various departments, im also trying to use card view to simply the look of the data and even though rows are hidden they still show up in card view.

Sorry if im not making my self clear

If you have questions fire away.

Thanks in advance

Chris

Genevieve P.

Hi @Chris Martin

There isn't a way to pick up a unique reference that doesn't exist on either sheets - it won't autopopulate into your second sheet when it's created in the source.

This means that the way I would do this is to manually pre-fill the second sheet with a number of Transaction IDs, even if they don't exist yet.

Then as the IDs are added to the source sheet, the data will automatically be pulled through using an INDEX(MATCH column formula, referencing the ID in both sheets.

You can wrap it in an IFERROR so for all the rows that don't have an ID in the source you see blank, instead of an error.

=IFERROR(INDEX({Column to bring back}, MATCH([Transaction ID]@row, {Transaction ID Source Sheet}, 0)), "")

An alternative would be to use Reports. Reports will automatically bring in new rows as they're added in the Source Sheet, as long as they meet your filter criteria. You can also make updates in a Report which will automatically update the source sheet, and as of last month you can use Card View in Reports.

Cheers,

Genevieve