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

=sheet2!b3

=sheet3!b3

=sheet4!b3I 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?

Thanks

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!B3If 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.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History