Modifying formula with a macro

SeaWitchVA

New Member
Joined
Oct 4, 2005
Messages
9
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
Why not use INDIRECT?

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

SeaWitchVA

New Member
Joined
Oct 4, 2005
Messages
9
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,670
Messages
5,573,560
Members
412,537
Latest member
Mohamed_5966
Top