How to auto change sheet reference in copied formulas?

[jaedmar]

When copying cells down a column, Excel autmatically changes the row which is referenced in the formula. I don't want the row to change, but do want the sheet reference to change to the next sheet.

This is the formula I am using: =IFERROR(IF('1'!M29<45,"X",""),"")

I want to copy this formula down through 49 more cells. I want only the sheet reference to change from 1 to 2 to 3.... to 50.

How do I do that without manually changine each cell?

 

[devonknows]

For the Cell that you do not want to change you simply add the $ sign before each reference.

M29 would turn into $M$29.

Regards,

 

[jaedmar]

That works to keep those references from changing, but how do I make the sheet reference chance automatically? My sheets are numbered sequentially 1-50.

 

[devonknows]

Hi,

This cant be done with formula trickery you need to use the INDIRECT formula for this to work, what you need to do is have an additional column with all your sheet names in (Has to be exactly the same as your sheet names).

Id suggest creating a new sheet and hiding it to neaten things up, for this example i have named a new sheet "SheetNames" and started A1-A50 going up 1,2,3,4,5,6 etc

Then, use this formula - =INDIRECT('SheetNames'!A1&"!" & "M29")

Overview:
New Sheet Name it "SheetNames"
Fill Cells A1 to A50 with the numbers 1 to 50
on your sheet enter the formula =INDIRECT('SheetNames'!A1&"!" & "M29")
(There is how ever no need to put the $ symbol on this formula becuase it does not progress as normal formula would for your M29)

Regards

 

[baodinh]

In cells B1:B50, enter the values 1-50 or whatever the variable is for the sheet name. In cell A1, type in the formula =CONCATENATE("zIFERROR(IF('","&b1&'!M29<45,","""X",""",""","""),",""""")")
Copy to A1:A50. Select A1:A50. Copy then Paste Special Values
Find and replace z with =
You should now have formulas which link to sheets 1 through to 50. Make sure you have the sheets created before you do this otherwise you'll have a #REF error.

 

[jaedmar]

In cells B1:B50, enter the values 1-50 or whatever the variable is for the sheet name. In cell A1, type in the formula =CONCATENATE("zIFERROR(IF('","&b1&'!M29<45,","""X",""",""","""),",""""")")
Copy to A1:A50. Select A1:A50. Copy then Paste Special Values
Find and replace z with =
You should now have formulas which link to sheets 1 through to 50. Make sure you have the sheets created before you do this otherwise you'll have a #REF error.

Can anyone explain this any further for me? I am not sure if this will work for my need. I am trying to cut and past formulas with only the sheet references changing (sequentially from 1, 2, 3...100). My formula references the same sheet 6 times.

 

[Surendhar]

Can anyone explain this any further for me? I am not sure if this will work for my need. I am trying to cut and past formulas with only the sheet references changing (sequentially from 1, 2, 3...100). My formula references the same sheet 6 times.

you can follow one easiest way.For Example..,Enter a value in A1...A5 in sheet1.And the thing is you need to display the addition of all the cells in A6.You might have know the formula for that. The second thing,on sheet2 if you want to add the previous sheet A6 value in with the current sheet A6 value in cell A7, you just insert this formula - =A6+sheet1!A6 in A7.Like this you have to do in all the cells where ever you need.Now if u want to update the formula in the third sheet, you no need to type like what you did in sheet 2. Its so simple. Just Copy the cells in which you have entered formula in sheet2 and paste it in sheet3 at the required place. you can now unhide the formula by pressing the keys ctrl+~. After that Just press Ctrl+F and find for Sheet2 and click on find all and replace with sheet3 and click on replace all. now again press Ctrl+~ to hide your formula.Thats it..!!! It will be helpful I think.

 

[evalmodel]

you can follow one easiest way.For Example..,Enter a value in A1...A5 in sheet1.And the thing is you need to display the addition of all the cells in A6.You might have know the formula for that. The second thing,on sheet2 if you want to add the previous sheet A6 value in with the current sheet A6 value in cell A7, you just insert this formula - =A6+sheet1!A6 in A7.Like this you have to do in all the cells where ever you need.Now if u want to update the formula in the third sheet, you no need to type like what you did in sheet 2. Its so simple. Just Copy the cells in which you have entered formula in sheet2 and paste it in sheet3 at the required place. you can now unhide the formula by pressing the keys ctrl+~. After that Just press Ctrl+F and find for Sheet2 and click on find all and replace with sheet3 and click on replace all. now again press Ctrl+~ to hide your formula.Thats it..!!! It will be helpful I think.

Thanks a lot for this tip! The ability to see formulas in the sheet and replace like text is magical!! Created a login just to thank you!!

 

[sarah9876]

Thank you so much! I was trying to copy formulas across rows using same cell #s but from subsequent sheets and your suggestion worked. The only thing I had to change was that after creating the new sheet called "SheetNames" I had to put the other sheet names in the same row instead of the same column since I was trying to copy formulas across rows not columns on my original sheet (only difference from the original poster).

Overview:
New Sheet Named it "SheetNames"
in "SheetNames" filled Cells A1 to L1 with the names of the 12 sheets I wanted to use the values from
on my original sheet entered this formula in cell c3 and copied across the row until n3:
=INDIRECT('SheetNames'!A1&"!" & "C2")
or entered this in cell c8 and copied across the row until n8
=INDIRECT(SheetNames!A1&"!" & "i2")+INDIRECT(SheetNames!A1&"!" & "M2")
etc...

worked beautifully thank you again! saved me from having to do each column separately!

Hi,

This cant be done with formula trickery you need to use the INDIRECT formula for this to work, what you need to do is have an additional column with all your sheet names in (Has to be exactly the same as your sheet names).

Id suggest creating a new sheet and hiding it to neaten things up, for this example i have named a new sheet "SheetNames" and started A1-A50 going up 1,2,3,4,5,6 etc

Then, use this formula - =INDIRECT('SheetNames'!A1&"!" & "M29")

Overview:
New Sheet Name it "SheetNames"
Fill Cells A1 to A50 with the numbers 1 to 50
on your sheet enter the formula =INDIRECT('SheetNames'!A1&"!" & "M29")
(There is how ever no need to put the $ symbol on this formula becuase it does not progress as normal formula would for your M29)

Regards

 

[hadihakimzadeh]

When copying cells down a column, Excel autmatically changes the row which is referenced in the formula. I don't want the row to change, but do want the sheet reference to change to the next sheet.

This is the formula I am using: =IFERROR(IF('1'!M29<45,"X",""),"")

I want to copy this formula down through 49 more cells. I want only the sheet reference to change from 1 to 2 to 3.... to 50.

How do I do that without manually changine each cell?

hi, first of all sorry for my bad english(if it is!)
i know this is an old topic but this solution can be used by others with the same problem.
lets consider that your first cell that contains the formula is D3:

=IFERROR(IF(INDIRECT("'"&ROWS(D$3:D3)&"'!M29")<45,"X",""),"")

well, ROWS(D$3:D3) returns 1 and the result of INDIRECT("'"&ROWS(D$3:D3)&"'!M29") will be '1'!M29
and when you copy the formula down in D4, the formula will be like:

=IFERROR(IF(INDIRECT("'"&ROWS(D$3:D4)&"'!M29")<45,"X",""),"")

and ROWS(D$3:D4) returns 2 so the result of INDIRECT("'"&ROWS(D$3:D4)&"'!M29") will be '2'!M29
and you could copy the formula down as much as you want.

i saw this solution in youtube but i cant remember the publisher.
i hope this will be usefull.