Cross-Linking Sheets

jl.furstenberg

Hello,

I am trying to figure out the best way to cross link sheets. Ideally, I would like to be able to match columns across sheets, but my sheets are too large to do so.

Set-up:

Main sheet (sheet #1) with thousands of entries and hundreds of columns. From sheet #1, I am only pulling entries that match specific criteria. If an entry matches, I have an auto workflow to copy that row to a helper sheet (sheet #2).

My helper sheet (sheet #2) is linked to a third sheet (sheet #3). I only need a few columns from the helper sheet to pull into sheet #3. This is where I am stuck. I cannot select the columns, as there are way more than 500 entries.

What would be the best way to get only the columns I need into sheet #3? Would Vlookup or Index/match work better to get data from sheet #2 to sheet #3?

Matt Johnson

Hi @jl.furstenberg

Have you looked into Datamesh? If not, you should. While your work around idea above could work for a while, with large data sets you'll run into issues later. And if your role changes it could be difficult to pass this work flow down to the next "maintainer" of the solution.

In your last question about vlookup or index/match...always use index/match over vlookup. I will go so far as to say, don't use vlookup at all.

If Datamesh is not feasible for you, or you need advice on setting it up, reach back out and I'm sure this community will provide more assistance.

I hope that helps.

Matt

jl.furstenberg

Thank you for the quick reply, Matt!

DataMesh looks like a great option, but I am hoping for an interim solution before having to purchase another add-on/premium product. I will definitely keep it in mind though!

Matt Johnson

How many rows are we talking about here? I'm sort of thinking you remove Sheet 2 from the equation.

Everything is on sheet 1. Only a few columns from sheet 1 get populated as read only on sheet 3 via Index/Match formulas.

You set up an automatic alert to notify you when a new row is added to sheet 1 for you to manually add it to sheet 3. The column formula index match will pull in the other columns of information automatically.

Lastly, but probably firstly, is that you have a unique identifier as the value you are adding to sheet 3. Whether that's a project number or whatever. That's true for forumulas or for datamesh.

Let me know if you need help with the Index Match or if you want to bounce other ideas around.

Thanks,

Matt

jl.furstenberg

I think we are probably talking about 200 rows a year. Right now, sheet #1 has a unique identifier (request ID#), so that should work to pull into sheet #3.

I can set-up an alert for new entries with specified criteria, so I can identify the subset of requests I am looking to move/copy.

I am not super confident with index and match formulas, so any help would be greatly appreciated!