Modifying formula with a macro

[SeaWitchVA]

I would appreciate a bit of help; I'm sure this is much simplier than I'm making it out but I'm having a bad blonde day.

I have multiple columns in a master worksheet. Row one of each column holds the name of a supplimentary worksheet that I need to reference. The spreadsheet looks something like this (I've displayed the formulas).

wABC wXYZ wMAD
=wABA!$B$1 =wXYZ!$B$1 =wMAD!$B$1

Currently each time I add or edit a row, I copy the formula then edit the worksheet name. I'd like to grab the name from row one and edit the formula using a macro.

Any suggestions on how I can accomplish this?

Thanks,
SW

 

[Norie]

Why not use INDIRECT?

=INDIRECT("'"&A1&"'!B1")

 

[SeaWitchVA]

Norie - I'm speechless (something that rarely happens). That's EXACTLY what I was looking for. I'd never heard of INDIRECT command before. So simple and to think of all those hours wasted so far.

You're awesome, thanks!