Formula to lookup values across multiple sheets

[mnsportsfan]

I have attached a small example of what I'm looking to do. In the example I have 2 sheets for each period and one for the total. I'm looking for a way to pull the data from period 1 and 2 and put it on the total page. In the example it will pull data based on the employee name that is in cell B1 and the period that is in cell B2. Once the employee name and period are in it will pull data that matches and insert into cells E4:F17 in the appropriate date. Is this doable?

[Pauleyb]

Yes, it's possible. It's just a nuisance in that you have a lot of 'gotchas' in your request.

First is spanning across sheets. That can be resolved using INDIRECT as described here: https://exceljet.net/formula/dynamic...heet-reference

Next is the desire to convert from data placed in a single row to multiple rows and two columns. This is usually achieved using OFFSET and the ROW() and COLUMN() functions. You use ROW() and COLUMN() to nicely increment as you use the same formula in the table. Example: Putting this in cell Total!H4

Please Login or Register  to view this content.

and then dragging down will transpose the row data into a column data. Adding and multiplying in the offsets will allow you to build the table you want.

Finally, there may be some formatting needed if you are not always 14 rows.

[FlameRetired]

Next is the desire to convert from data placed in a single row to multiple rows and two columns.

I agree with Pauleyb.

Here's an INDEX/MATCH way to do it. In E4 filled down and across.

Please Login or Register  to view this content.