How to make a sheet pull from other sheets but one value at a time?

**Gunner 14** · 06-04-2012, 08:48 AM

Hello,

I would like to ask you if anyone knows how to make one sheet pull a lot of values from a lot of sheets? This is the problem:

Let's say that at work I get monthly reviews of certain businesses. And in each of those excel woorkbooks I get sheets that have one day per month (each sheet has values of each day, for example: 1st Jan is one sheet, the 2nd of Jan is on the other sheet etc.) And now, for example, in Sheet "Jan 1st" in A1 I have certain data and in Sheet "Jan 2nd" I have that same certain data on Jan 2nd and so on...

Is there anything that can help me to make a Sheet "January" in which I will put that data in each column that doesn't require me to go to every single cell and reference it to the cell in the other sheets? In Sheet "January" in A1 I want the data from Sheet "Jan 1st", in A2 I want that same data from Sheet "Jan 2nd", in A3 I want that same data from Sheet "Jan 3rd" and so on...

Thank you!

**wallyeye** · 06-04-2012, 11:32 AM

Within a workbook, you can use indirect referencing fairly easily. Assuming you have the first of the month in cell D2, A1 could look like this:

=indirect("'" & format(D2 + row() -1,"mmm dd") & "'!A1")

One issue you have is that you are using the terminals ("st", "nd", "rd", "th"). You would need to either build this into the format statement, or drop it from the sheet name, I would recommend dropping the terminals. The Row() -1 would allow you to autofill the formula down, and would increment the date to 2, 3, 4, etc.