Excel Q - I would like to change a row reference on an entire sheet without doing a find an replace. Help.?

Question

I have a master spreadsheet that individual sheets point back to. The master sheet is a summary and there is an individual sheet that references the information in a particular row on the master. Instead of copying an individual sheet and then changing doing a find and replace throughout I would like to be able to just change maybe one cell to point to the new row and all the references on the sheet get updated.

Answer 1

Ok you can do this but its a bit difficult to explain, so i'm just giving you an example and you figure it out:

Assuming you have a workbook called 'Master' with differing series of numbers in columns B, C & D starting from 2 downwards in Sheet1..

In your target workbook we intend to reference this data in for example Column 'B'

So we use B1 to point to the column (eg ="C")
we use B2 to point to the starting row (eg ="3")
in B3 type the formula:
=INDIRECT( CONCATENATE( "[Master] Sheet1!", B$1 ,B$2 + ROW()-3))
pull the formula down along column and you will get the corresponding series. Try changing the reference addresses in Cells B1/B2 to different columns,rows and your data will update accordingly.
Obviously to understand what we're doing in above formula, the B$1 & the B$2 are relative to the address anchor points while the "3" has to be coded to starting row of the paste data. If you also want that flexible, use B3 for that start point and replace in formula accordingly.

I hope you understood this, if not mail me and I will send you example sheets

Answer 2

Just highlight the entire sheet by clicking in the upper left hand corner box. Then start as if doing a find and replace by doing EDIT REPLACE and filling in the old value and the new value. But then click on REPLACE ALL. It will do every occurrence and say how many it found.

Answer 3

I think I got you here
You want the other sheets to refer to the master one in a changeable way
Well, I still need more info about that.

Send me mail through Yahoo! Answers with your file or some details if I got you here as you need, then I will be glad to teach you how to change that references

Read my profile, I am the XLMan