Replace a similar cell reference across multiple cells

[ellistyle]

I have a series of data in which all cells point to a value in other sheets (e.g. Sheet A is summary page and sheets 1, 2 & 3 are the source). How can I create a corresponding series of data that point to the same place, but in a different column. So if the first row of data point to A1 on sheets 1-3, I want to easily copy or create a new series referencing C1 on the same sheets.


Do I need VBA magic for that?

 

[fred]

do you mean you want to create a pull down menu to pull the data from Sheets 1 to 3 and on a certain column?


say you have 10 data from A1 to A10 on sheets 1 to 3.


on the summary page you want to create a pulldown menu pointing to Sheet1, Sheet2 and Sheet 3.


Then you can use indirect ( pulldown cell&"!A1", true). but remember to change A1 to A2, A3...A10 accordingly as you get the list.


if you have multiple columns on sheets 1 to 3 you can use the same mentality to expand to include different column headings. you just need to make sure you have the pulldown menu covering all the columns on sheets 1 to 3.


Hope this help.

 

[ellistyle]

Not quite, but interesting.

In the first column of my summary page, each cell references "Sheet1!A1", "Sheet2!A1" or "Sheet3!A1" (Except I actually have about 20 sheets with several entries). I want to easily change each A1 to C1 without editing each cell individually. There are several entries on the summary page so I can't just grab the handle and copy.

 

[fred]

you can still use the indirect function. instead of choosing the sheet you can have the pulldown menu to be A, B, C, ... so you can compare all the columns D, for instance.


INDIRECT(Sheetname&"!"&pulldown cell & 1, TRUE) and replace 1 with 2, 3, 4, etc as you expand the list down.