Linking cells - when new rows are added to source sheet do I have to manually update links

SammyMorey

Hi all,

We are reaching the referenced cell limit in our sheet so I have separate sheets to do the required formulas. To do this I have needed to link the "unique identifier" cells to the intermediate sheets so the formulas know what data to match (then use an index match back into the source sheet to populate the required cells).

My question is - we currently have 1500 rows approx in the source sheet and I have linked all those cells in 3 batches. Although we are adding to this sheet daily, and my question is, every day will I need to update the linked cells to now include the next X number of rows added?

I thought maybe I can add heaps of blank rows at the bottom of the source sheet - but as the entries are populated by a form and there is a auto column "created" I think this will affect where the new entries are populated.

Kind of defetes the purpose having to update the linked cells on a daily basis... may aswell just copy and paste the unique identifier column into the intermediate sheets...

Any help would be appreciated.

SammyMorey

Hi Andre - I am already using cross sheet index match formulas - but I needed to get the unique Identifier into the sheets I was using to do all the formulas.

BUT I worked out I can use an auto number column in the source sheet - then (because I can predict what the auto numbers will be) use those as the unique identifier. Then I can pull through the "actual" unique identifier for the rest of the formulas.

THANKS :)

Andrée Starå

Hi @SammyMorey

I'd probably recommend setting up cross-sheet formulas instead. (VLOOKUP or INDEX/MATCH)

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

SammyMorey

Hi Andre - I am already using cross sheet index match formulas - but I needed to get the unique Identifier into the sheets I was using to do all the formulas.

BUT I worked out I can use an auto number column in the source sheet - then (because I can predict what the auto numbers will be) use those as the unique identifier. Then I can pull through the "actual" unique identifier for the rest of the formulas.

THANKS :)

Andrée Starå

@SammyMorey

Excellent!

That would have been my next suggestion.

You're more than welcome!

✅Please help the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!