Referencing the same cell on different sheets for a summary sheet

[JMillz]

I am trying to create a summary sheet that pulls information from all the work sheets in the work book.

Each sheet is identical from with the exception of the information and the naming convention is (#the the number)
The summary sheet has 5 columns Author (B8), Date Initiated (B17), Responder(B36), Date Responded (D36), Subject (B10).

I have over 100 individual sheets and am using =T('#1'!$B$8) to get the information in cell B8 on the first sheet. How do I pull the formula down so that I don't have to retype the sheet name in to every cell?

 

[pplstuff]

Try using the function indirect() instead. Very useful.

 

[Aladin Akyurek]

I am trying to create a summary sheet that pulls information from all the work sheets in the work book.

Each sheet is identical from with the exception of the information and the naming convention is (#the the number)
The summary sheet has 5 columns Author (B8), Date Initiated (B17), Responder(B36), Date Responded (D36), Subject (B10).

I have over 100 individual sheets and am using =T('#1'!$B$8) to get the information in cell B8 on the first sheet. How do I pull the formula down so that I don't have to retype the sheet name in to every cell?

Where do you enter this

=T('#1'!$B$8)

formula?

 

[JMillz]

=T('#1'!$B$8) is the formula i used in the summary sheet to get the authors name, which is in cell B8 of all the sheets and it is a formula that works.

What I want is to be able to drag it down but have it reference the next sheet. The naming convention for the sheets is #then the number. For example #1, #2, #3,#4 and so on till #100.

I don't want to have to retype the formula for each sheet. Just drag it down, but when I drag it, it just stays exactly the same.

I hope that explains what I want.

 

[Aladin Akyurek]

=T('#1'!$B$8) is the formula i used in the summary sheet to get the authors name, which is in cell B8 of all the sheets and it is a formula that works.

What I want is to be able to drag it down but have it reference the next sheet. The naming convention for the sheets is #then the number. For example #1, #2, #3,#4 and so on till #100.

I don't want to have to retype the formula for each sheet. Just drag it down, but when I drag it, it just stays exactly the same.

I hope that explains what I want.

You did not mention the cell location of the formula. Assuming that it is A2 (Adjust to suit), something like,

=T(INDIRECT("'#"&ROWS($A$2:A2)&"'!B8"))

 

[JMillz]

Awesome. Now how can I do this for the date columns?? Date initated (which is cell B17) and Date Responded (which is cell D36)

 

[Aladin Akyurek]

Awesome. Now how can I do this for the date columns?? Date initated (which is cell B17) and Date Responded (which is cell D36)

What are your current formulas regarding B17 and D36?

 

[JMillz]

=+'#1'!$b$17

=+'#1'!d36

 

[Aladin Akyurek]

=

+'#1'!$b$17

=+'#1'!d36

Maybe

=INDIRECT("'#"&ROWS($A$2:A2)&"'!B17")

=INDIRECT("'#"&ROWS($A$2:A2)&"'!D16")

that you are after...

 

[JMillz]

Thanks so much!! Now I'm curious.... how does "'#"&ROWS($A$2:A2)&" work to change the sheet?? I don't understand the logic.