Hi

I am looking to use the formula =Sheet1!A6 in a column, with the formula
changing each cell you go down, to =Sheet2!A6, =sheet3!A6 etc.... so it is
the sheet that changes, not the cell the data is getting taken from....how do
i do this? I think it is with the $ symbol, but i cant get it to work! Any
help will be appreciated!

Thanks in advance

Fiona

I'm going to do this with a helper column so that you see what's happening.

A2 = 1
A3 = A2+1
A4 = A3 + 1

etc.
B2: =INDIRECT("Sheet"&A2&"!A6")
Copy down


"blinkgirl119" wrote:

Hi

I am looking to use the formula =Sheet1!A6 in a column, with the formula
changing each cell you go down, to =Sheet2!A6, =sheet3!A6 etc.... so it is
the sheet that changes, not the cell the data is getting taken from....how do
i do this? I think it is with the $ symbol, but i cant get it to work! Any
help will be appreciated!

Thanks in advance

Fiona

Or, if you'd rather not use a helper column, you can use the ROW() function.

=INDIRECT("Sheet"&ROW()&"!A6")

If placed in B2, it would return the value of Sheet2!A6. Since B2 is in row
2. You could also use ROW()+1 or ROW()-1 to adjust if the row your formula
is in doesn't match up with the sheet number you want to reference.

HTH,
Elkar


"Barb Reinhardt" wrote:

I'm going to do this with a helper column so that you see what's happening.

A2 = 1
A3 = A2+1
A4 = A3 + 1

etc.
B2: =INDIRECT("Sheet"&A2&"!A6")
Copy down


"blinkgirl119" wrote:

Hi

I am looking to use the formula =Sheet1!A6 in a column, with the formula
changing each cell you go down, to =Sheet2!A6, =sheet3!A6 etc.... so it is
the sheet that changes, not the cell the data is getting taken from....how do
i do this? I think it is with the $ symbol, but i cant get it to work! Any
help will be appreciated!

Thanks in advance

Fiona

I know im gonna sound thick amongst all you guys, but i dont get it!

Fee

"Barb Reinhardt" wrote:

I'm going to do this with a helper column so that you see what's happening.

A2 = 1
A3 = A2+1
A4 = A3 + 1

etc.
B2: =INDIRECT("Sheet"&A2&"!A6")
Copy down


"blinkgirl119" wrote:

Hi

I am looking to use the formula =Sheet1!A6 in a column, with the formula
changing each cell you go down, to =Sheet2!A6, =sheet3!A6 etc.... so it is
the sheet that changes, not the cell the data is getting taken from....how do
i do this? I think it is with the $ symbol, but i cant get it to work! Any
help will be appreciated!

Thanks in advance

Fiona

What don't you get? We all had to start somewhere.

"blinkgirl119" wrote:

I know im gonna sound thick amongst all you guys, but i dont get it!

Fee

"Barb Reinhardt" wrote:

I'm going to do this with a helper column so that you see what's happening.

A2 = 1
A3 = A2+1
A4 = A3 + 1

etc.
B2: =INDIRECT("Sheet"&A2&"!A6")
Copy down


"blinkgirl119" wrote:

Hi

I am looking to use the formula =Sheet1!A6 in a column, with the formula
changing each cell you go down, to =Sheet2!A6, =sheet3!A6 etc.... so it is
the sheet that changes, not the cell the data is getting taken from....how do
i do this? I think it is with the $ symbol, but i cant get it to work! Any
help will be appreciated!

Thanks in advance

Fiona

Thank you for being soo nice! I dont know how to do this formula at all!

Fee


"Barb Reinhardt" wrote:

What don't you get? We all had to start somewhere.

"blinkgirl119" wrote:

I know im gonna sound thick amongst all you guys, but i dont get it!

Fee

"Barb Reinhardt" wrote:

I'm going to do this with a helper column so that you see what's happening.

A2 = 1
A3 = A2+1
A4 = A3 + 1

etc.
B2: =INDIRECT("Sheet"&A2&"!A6")
Copy down


"blinkgirl119" wrote:

Hi

I am looking to use the formula =Sheet1!A6 in a column, with the formula
changing each cell you go down, to =Sheet2!A6, =sheet3!A6 etc.... so it is
the sheet that changes, not the cell the data is getting taken from....how do
i do this? I think it is with the $ symbol, but i cant get it to work! Any
help will be appreciated!

Thanks in advance

Fiona