Results 1 to 3 of 3
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    San Jose, California, USA
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 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?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    The Hague, Netherlands
    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)


    =VLOOKUP(LookupValue,CHOOSE(D17,PriceTableA,PriceT able2),ColIdx,MatchType)
    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts