Results 1 to 3 of 3

20030717, 17:36 #1
 Join Date
 Jun 2001
 Location
 San Jose, California, USA
 Posts
 1,061
 Thanks
 0
 Thanked 0 Times in 0 Posts
switching formula source data (Excel 97/2000/2002)
I'm a dud at anything but the most trivial formulas so need some expert help here. <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>
I have a spreadsheet which I have been asked to support dynamically switching the data that a set of formulas refers to between two different worksheets (these contain the source data). This switching would occur when the user selects one of two options from a pair of radio buttons which are on the worksheet itself (not a form). If choice 1 is selected, the formulas should point to source sheet 1 else source sheet 2. I'm sure this can be done but don't know the formulas well enough to do it (I can do it in VBA but I'm sure that's overkill and more complex than needed).
I have the 'cell link' set to D17 for the radio button and it returns 1 or 2 based on which button is pushed so I guess I need an IF statement around each formula. There are lots of formulas I need to add this IF statement to was hoping I didn't have to edit each one. Something like:
IF (D17=1,"use price sheet 1", "use price sheet 2")
Both of these source sheets represent pricing data so by the user selection choice 1 or choice 2 they are selecting a different look up table for the prices. Obviously the above formula isn't going to work but the idea is to dynamically switch the source data sheet so the 'real' formulas can know which sheet to use. Is this what the INDIRECT formula is good for?
Is there a slick way to implement this given I already have these formulas set to look at a single priceing sheet?
Thnx, Deb

20030717, 18:57 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: switching formula source data (Excel 97/2000/2002)
Hi Deb,
This is indeed a case for the use of INDIRECT. It will still involve a massive change of formulas.
You state that D17 contains either 1 or 2, depending on the radio button pushed by the user. Put the following formula into another cell, say D18:<pre>=IF(D17=1,"Sheet1","Sheet2")</pre>
where Sheet1 and Sheet2 are the names of the two worksheets. Assume that you want to refer to cell A1 on either Sheet1 or Sheet2; you would use this formula:<pre>=INDIRECT("'"&D18&"'!A1")</pre>
If D17 =1, then D18 = "sheet1", so the formula will evaluate to<pre>=INDIRECT("'Sheet1'!A1")</pre>
which is equivalent to ='Sheet1'!A1. Strictly speaking, the single quotes are only necessary if the sheet names contain spaces, but it doesn't hurt to use them in all cases. Similarly for a sum formula:<pre>=SUM(INDIRECT("'"&D18&"'!C13:C37"))</pre>
Isn't it fun?

20030719, 07:17 #3
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: switching formula source data (Excel 97/2000/2002)
The very idea with IF should work...
Suppose you have PriceTableA in Sheet1 and PriceTableB in Sheet2, you can have for example...
=VLOOKUP(LookupValue,IF(D17=1,PriceTableA,PriceTab le2),ColIdx,MatchType)
or
=VLOOKUP(LookupValue,CHOOSE(D17,PriceTableA,PriceT able2),ColIdx,MatchType)Microsoft MVP  Excel