Index/Match from Multiple Sheets?
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.
Customer 1, Customer 2, Customer 3
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?
Help Article Resources
Check out the Formula Handbook template!