using a drop down box to change the value in another box

[OTWarrior via OfficeKB.com]

My colleague has a workbook with multiple worksheets. the first sheet is a
sum of the data, and the other sheets have the data needed for the
calculations.

On the first sheet, I would like to have a drop down box that changes the
values in multiple rows (eg: C4-K4) depending on what is selected on the
drop down box (the drop down box value will be the names of the other sheets).


Would this be best done as a macro, in VB, or another way?

also, I thought of using a case select to choose the rows (ie: change the "C"
in "C4" to the next letter), but it has been awhile since I used them. would
they be the best solution rather than having LOADS of lines of code (sorta
answered my own question, but I don't know enough about coding in excel,
other than it may be similar to other office VB code)

 

[Bernie Deitrick]

You could use a bunch of INDIRECT functions, keyed to the cell with the sheet name (if the
'drop-dwon box is from Data Validation, using the list feature), along the lines of:

=INDIRECT("'" & A1 & "'!B3")
=INDIRECT("'" & A1 & "'!C3")

Etc...

HTH,
Bernie
MS Excel MVP