Copy/Paste Formula that increments page names and pulls data from same cell

**austinrob** · 01-27-2010, 02:48 AM

Anyone, (please)

I have a summary sheet and 31 additional sheets numbered 1-31. The sheet names 1-31 represent calander days in a given month.

The Summary sheet reads left to right and formula I use increments for the sheet name. 1,2,3,4 etc...

Example: Starting A1 through AE1, I would insert ='1'!F2; B1: insert ='2'!F2; C1: insert ='3'!F2 etc...etc... till AE1: insert ='31'!F2.

As you can see, I am pulling data from multiple sheets, but the same cell each time. It is very time consuming to go to each cell and just alter the page/sheet name (31 times)

Question:

What formula can I enter in A1 that I can drag/copy through to cell AE1 ...... and pulls data from the same cell from multiple pages?

Thanks In Advance!!

**pike** · 01-27-2010, 03:17 AM

Hi austinrob
Try the volitile function

=INDIRECT("Sheet" &COLUMN()& "!F2")

**rwgrietveld** · 01-27-2010, 03:38 AM

Pike is right, just wanted to put Your forumula here as you have to adjust Pike's

Please Login or Register  to view this content.

Copy paste this into the SUM!A1 cell.

Note: You see " ' " (double, single, double)

**pike** · 01-27-2010, 03:48 AM

Thanks rwgrietveld
my oversite

**austinrob** · 01-27-2010, 02:58 PM

Originally Posted by pike

Hi austinrob
Try the volitile function

Thanks for the quick response. I tried the formula and it does work; however, there a small issue remains.

Initially, my example showed the summary sheet starts at A1 through AE1. I used this example to make it easier for the reader to understand. Using moreless the same example on the coversheet, but start at F4 through AF4 to accomplish the same goal.

Using the formula provided, I thought I could simply modify it to something like this:

=INDIRECT("'"&COLUMN(F4)&"'!F2")

But it did not work the same. (partially)

The results of my formula change actually showed results, however the results started showing from sheet name "4" and not starting in sheet called "1". If I delete columns A-E, so the formula starts from A4.... Then the results do start from sheet "1" and consecutive pages.

Question: Is there a way to modify the formula so I do not have to start in column A, and increment sheet names through the copy/drag?

*******
I uploaded a screen shot of my excel as an attachment. Please focus on rows 6 and 8. Note if I start at C6, the data pulled is starts 1/3/2010. (I want it to pull data starting from 1/1/2010).

Using the same formula but starting at A8, it pulls data starting from 1/1/2010. This is what I want - but I need a formula that allows for me to be further in column wise on the summary sheet.

Thanks in advance!!

and Thanks for the quick responses!!!

Rob