Cross-sheet & condition-based formula

eohlsson@optronicsinc.com

Hi all!

I need a formula that imports a reference number (auto-generated) from a column in sheet A (source) into a column in a separate Sheet B (destination), but only when a separate drop-down column in Sheet A changes to a specific value. Using a filtered report in lieu of Sheet B is not an option (at least not preferred) – I need both Sheet A and Sheet B to be a grid/sheet.

An =INDEX(COLLECT formula allows me to bring in the first reference number when the defined condition has been met, but duplicates that same reference number on subsequent rows.

I’d like for this formula to be a column formula in the destination column in Sheet B so that any and all unique reference numbers in Sheet A that match the condition will be automatically copied over to Sheet B.

Would anyone be able to confirm if this can be done, and – if so – what the formula syntax/example would look like?

Thanks!

Erik Ohlsson

Paul Newcome

You would have to insert a manually populated text/number column (called "Number" in this example) and enter the numbers one through whatever going down the column.

1

2

3

4

5

etc.

You are going to need to pre-populate as many rows as you think you will need (plus a suggested buffer just in case). Then you can reference this Number column in the second portion of the INDEX function to pull in the first, second, third, etc..

I suggest wrapping the whole thing in an IFERROR to keep unused rows clean until they get used.

=IFERROR(INDEX(COLLECT(………), Number@row), "")

Paul Newcome

You would have to insert a manually populated text/number column (called "Number" in this example) and enter the numbers one through whatever going down the column.

1

2

3

4

5

etc.

You are going to need to pre-populate as many rows as you think you will need (plus a suggested buffer just in case). Then you can reference this Number column in the second portion of the INDEX function to pull in the first, second, third, etc..

I suggest wrapping the whole thing in an IFERROR to keep unused rows clean until they get used.

=IFERROR(INDEX(COLLECT(………), Number@row), "")

eohlsson@optronicsinc.com

Great suggestion @Paul Newcome! Thanks a bunch!

Paul Newcome

Happy to help. 👍️