+ Reply to Thread
Results 1 to 4 of 4

Help??

  1. #1
    Registered User
    Join Date
    02-02-2005
    Posts
    11

    Help??

    I have a spreadsheet with three different tables in it. All the tables are laid out the same as below but one is for 1yr, one is for 2 yrs and one is for 3yrs.

    20 25 30 35
    11 125 130 145 150
    10 126 131 146 151
    9 127 132 147 152

    The heading across the top is the discount and the one down is the cost. In the second sheet I have used drop down boxes and option buttons and then used a vlookup for all the other formulas I needed. I want to be able to select 30% discount and 10 cost equals 146. Please can anyone help? Thanks

  2. #2
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    First, define a name for each of your tables. For example, name your 1-year table Table1, your 2-year table Table2, and your 3-year table Table3...

    Insert > Name > Define

    Name: enter the table name, for example Table1

    Refers to: enter/select the range of cells housing your table

    Click Ok, and repeat for the other two tables

    Then try the following formula...

    =VLOOKUP(A1,CHOOSE(C1,Table1,Table2,Table3),MATCH(B1,{0,20,25,30,35},0),0)

    ...where A1 contains your drop-down menu for cost, B1 contains your drop-down menu for discount, and C1 is linked to your option buttons.

    Hope this helps!

    Quote Originally Posted by danielle
    I have a spreadsheet with three different tables in it. All the tables are laid out the same as below but one is for 1yr, one is for 2 yrs and one is for 3yrs.

    20 25 30 35
    11 125 130 145 150
    10 126 131 146 151
    9 127 132 147 152

    The heading across the top is the discount and the one down is the cost. In the second sheet I have used drop down boxes and option buttons and then used a vlookup for all the other formulas I needed. I want to be able to select 30% discount and 10 cost equals 146. Please can anyone help? Thanks

  3. #3
    Registered User
    Join Date
    02-02-2005
    Posts
    11
    Unfortunately this hasnt worked. It has bought up a figures but not the one I wanted. Any ideas?

  4. #4
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Quote Originally Posted by danielle
    Unfortunately this hasnt worked. It has bought up a figures but not the one I wanted. Any ideas?
    I'm not sure off hand what the problem might be. If you'd like, you can email me a sample of your file and I'll try to help. You can email me at [email protected]

    Cheers!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1