Changing worksheet ref in formula automatically

[Mdlane74]

I'm having an issue getting the worksheet ref to change to the next sheet automatically as I copy them to the next row. Table 10 is my sheet name. Followed by 11,12,....229.
Using formula:
=if(B$4="",if((or('table 10'!$B9="comment",'table 10'!$B9=""),"",B$2&" / "&'table 10'!$B9),"")

 

[Joe4]

Welcome to the Board!

You can use the INDIRECT function to build your formula dynamically, and you can incorporate the ROW function to handle your increasing number (as the ROW number the formula is being placed in is increasing also).

Let's say that the formula being posted above is being put in row 3 amd being copied down. So you want the formula in row 3 to reference Table 10, the formula in row 4 to reference Table 11, etc.
So the difference between the row number and the table number is always 7.

Your formula should look something like this:

[COLOR=#333333]=if(B$4="",if((or(INDIRECT("'table " & ROW()+7 & "'!$B9")="comment",INDIRECT("'table " &[/COLOR][COLOR=#333333] ROW()+7 &[/COLOR][COLOR=#333333] "'!$B9")=""),"",B$2&" / "&INDIRECT("'table " [/COLOR][COLOR=#333333]& ROW()+7 & "[/COLOR][COLOR=#333333]'!$B9")),"")[/COLOR]

Basically, you are just replacing each range reference of:

[COLOR=#333333]'table 10'!$B9[/COLOR]

with

[COLOR=#333333]INDIRECT("'table " & ROW()+7 & "[/COLOR][COLOR=#333333]'!$B9")[/COLOR]

See here for more information on INDIRECT: http://www.cpearson.com/excel/indirect.htm

 

[Mdlane74]

Welcome to the Board!

You can use the INDIRECT function to build your formula dynamically, and you can incorporate the ROW function to handle your increasing number (as the ROW number the formula is being placed in is increasing also).

Let's say that the formula being posted above is being put in row 3 amd being copied down. So you want the formula in row 3 to reference Table 10, the formula in row 4 to reference Table 11, etc.
So the difference between the row number and the table number is always 7.

Your formula should look something like this:

[COLOR=#333333]=if(B$4="",if((or(INDIRECT("'table " & ROW()+7 & "'!$B9")="comment",INDIRECT("'table " &[/COLOR][COLOR=#333333] ROW()+7 &[/COLOR][COLOR=#333333] "'!$B9")=""),"",B$2&" / "&INDIRECT("'table " [/COLOR][COLOR=#333333]& ROW()+7 & "[/COLOR][COLOR=#333333]'!$B9")),"")[/COLOR]

Basically, you are just replacing each range reference of:

[COLOR=#333333]'table 10'!$B9[/COLOR]

with

[COLOR=#333333]INDIRECT('table " & ROW()+7 & "[/COLOR][COLOR=#333333]'!$B9")[/COLOR]

See here for more information on INDIRECT: http://www.cpearson.com/excel/indirect.htm

This is a now working for the rows, but I can't copy down now in columns. B9 needs to be able to change to B10,B11,...B51

 

[Joe4]

This is a now working for the rows, but I can't copy down now in columns. B9 needs to be able to change to B10,B11,...B51

Just use the same concept - making use of the ROW function.

Basically, all you are doing with INDIRECT is building the range reference in a string, and then surrounding it in an INDIRECT function. So to make sure it works, just create a formula first of the inner workings, and see what it returns.

So the current one we have now is:

[COLOR=#333333]"'table " & ROW()+7 & "[/COLOR][COLOR=#333333]'!$B9"[/COLOR]

So, if you just put an equal sign in front of it and return it as its own function, you can see what it returns. And you can drag it down the column to see how it changes.

To follow our original example, if the original formula is going in row 3, then the difference between the row number in the range reference (9) and the row number where the formula is going is 6.
So change the above to:

"'table " & ROW()+7 & "'!$B" & [COLOR=#ff0000]ROW()+6[/COLOR]

and that should do what you want!

 

[Mdlane74]

Im getting
Table10 B9. Table10 B9. Table10 B9
Table11 B10. Table11 B10. Table11 B10

I want
Table10 B9. Table11 B9. Table12 B9
Table10 B10. Table11 B10. Table12 B10

Is this possible?