Index Match formula work around for Dynamic View

Andrew Taglauer

Hello,

I am trying to find a solution for a sheet that has multiple lines with the same store#'s and inventory on multiple rows. This is not a shared sheet to our group, so I want to be able to index match this information to another sheet so we can use the info for internal purposes. I dont have the knowledge of how to use the formula when there are the multiple same store numbers in the row headers that would error out on a single result.

Hollie Green

You have your row ID as all the same number it needs to increase by 1 so it matches your auto row numbers on your other sheet.

If you already had the row id for another purpose on the sheet then create one that is called something else so it can increase by 1 for each row.

Andrée Starå

@Andrew Taglauer

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

You'd add the auto-number column in the source sheet, then add the same series manually in the destination sheet for as many rows as you need.

Make sense?

Hollie Green

Are you trying to bring in all of the data in these columns or just one instance of the data?

Andrée Starå

Hi @Andrew Taglauer

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

Would that work/help?

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 support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrew Taglauer

@Hollie Green Correct, I want to be able to bring all the data over to another sheet and customize the sheet for our team.

Andrew Taglauer

@Andrée Starå That sounds like a great option. Unfortunately, I am a little lost on how this would work. If I share my sheet with you would you be able to build this example or is there a training I could take to understand this better? I removed all the confidential/sensitive information from the worksheet as well.

Hollie Green

If you have other columns you want to keep private from the larger group then you can add a helper column to this sheet choose the # Auto Number type column when you add it. When you save it it will automatically number the rows.

On your destination sheet you would add a column and create a row Id column and and number the rows so they match your main sheet you can continue to add rows going up by 1 number for as many potential rows as you may have. Add as many rows as you think you may need for additional data being added to the main sheet. when you add data to the main sheet it will automatically use the next higher number as the row number.

You can then use an index collect or index match to bring over the data as it will only have 1 result. You will have to create your references

below is an example of an index collect to bring in the equipment type.

=Index(Collect({Equipment Type},{Auto Row}=[Row ID]@row),1)

If you do not have other columns that you do not want to share it is much easier you can create an automation that will copy the entire row over to the other sheet.

Andrée Starå

@Andrew Taglauer

Did Hollies excellent description solve it, or do you still need help?

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

Andrew Taglauer

@Andrée Starå It makes sense and will give it a shot and will let you know.

Andrew Taglauer

@Andrée Starå @Hollie Green I am getting a #incorrect argument on the destination sheet when I use index/collect. When I use index/match I get a #no match in the cell. In my original request I have rows that are the same store but have some variations in the rows. Would that make a difference in this formula or maybe I have the formula incorrect. My goal here is to query a store and it brings up all the store rows with the associated equipment and status. Each store could have 30-40 rows with the same store number but different equipment in the criteria.

Hollie Green

You have your row ID as all the same number it needs to increase by 1 so it matches your auto row numbers on your other sheet.

If you already had the row id for another purpose on the sheet then create one that is called something else so it can increase by 1 for each row.

Andrée Starå

@Andrew Taglauer

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

You'd add the auto-number column in the source sheet, then add the same series manually in the destination sheet for as many rows as you need.

Make sense?