Index/Match from Multiple Sheets?

KGetchell

Is it possible to create a Index/Match formula that allows you to Match a unique value that could be found in one of multiple sheets?

Scenario: On the 1st of each month, we publish our performance projections for the month, which are based on the current values of several fields. Because those values can change during the course of the month, we need the ability to take a "snapshot" of the data at the 1st of the month and track how the data changes through out the month.

We have 30 individual project tracking sheets (1 for each customer) that all have the same column headers/data types.

Sheets:

Customer 1, Customer 2, Customer 3

Columns:

Column 1, Column 2

I can easily create a report that pulls Columns 1 and 2 from Sheets Customer 1, Customer 2, and Customer 3, but it does not give me the ability to "lock" in certain data points at the 1st of the month.

My initial thought was to run a report, save to a new Sheet and then create cell references to pull in the real-time data from the source sheets. (SEE BELOW) But, needing to pull in 3 different data points from 30 source sheets (and repeat every month) seems to be very time consuming.

Perhaps there is a better way?

THANK YOU!

Genevieve P.

Hi @KGetchell

The way I would take a "snapshot" on the 1st of the Month would be to use one Copy Row workflow in each of your 30 sheets to send the data to a tracking sheet. This will copy/paste the data so it's static for that one day.

Then yes, I would create 30 x 3 separate formulas... but the exact same 3 formulas in each of the individual 30 sheets. I would use an INDEX(MATCH to bring back the data from this newly Copied row so you can display the "locked" value in a cell next to the changing value in the original sheet.

This means that in your Report you can select both the Current columns and the Static ones across all 30 sheets so your data is displayed side-to-side. You could even set up Charts based on the values showing if you have the columns next to each other like this.

Would this work for you? Let me know if you'd like to see screen captures of what I'm describing, I understand that sometimes it's easier to understand with visuals.

Cheers,

Genevieve