Copying a sheet# and Cell & I want to change just the sheet

December 7, 2012 at 11:29:35
Specs: Windows 7
my cell formula is =sheet1!b3
and I want to copy this to three other locations but I want to just change the sheet# so the three new locations would be

I know if I have two cell # and I only want one to change I would have it read a1?b?3. can I do something like this to hold my cell number and just have my sheet number change?


See More: Copying a sheet# and Cell & I want to change just the sheet

Report •

December 7, 2012 at 12:40:48
Are you "copying" these formulas or dragging them to AutoFill?

If you are dragging them down (or over), you could use a combination of the INDIRECT and ROW (or COLUMN) functions.

You can read up on these functions in the Excel Help files, but in general the INDIRECT function is used when you want to use text in a formula to represent a cell reference and the ROW and COLUMN functions simply return the Row or Column number in which the function resides.

For example, if you put =ROW() anywhere in Row 5, it will return 5. If you put =COLUMN() anywhere in Column C it will return 3.

Since these functions return numbers, you can perform operations on them.

e.g. if you out in this formula in Sheet1!A1, it will be equivalent to =Sheet2!B3

=INDIRECT("SHEET" & ROW()+1 & "!B3")

ROW() will return 1
ROW()+1 will return 2
"SHEET" & ROW()+1 & "!B3" will return the text string Sheet2!B3
The INDIRECT function will use that text string and return the value in Sheet2!B3

If you drag this down, the ROW() function will increment with each row, thereby incrementing only the Sheet number, since the rest of the text will remain constant.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question