#1
|
|||
|
|||
Looking up a value with user changing the lookup coloum
I have a spreadsheet which has
Column A - List of Products we sell Column B - 2022 Prices Column C - 2023 Prices Column D - 2024 Prices i want to create a spreadsheet where the user work out how much the total product costs. In simple terms, if the User Selects 'Product A' and then '2022' - both drop down lists - then the result will be the price of the product in 2022, (answer from cell B2) if the User Selects 'Product B ' and then '2023' - both drop down lists - then the result will be the price of the product B in 2023 , (answer will be in cell C3) any ideas how I use VLoookup or similar in that way! |
#2
|
||||
|
||||
Please post a sample sheet ( no pics please) with some data and manually added expected results. Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Hi, see in attached file a possible solution...
|
#4
|
|||
|
|||
Attached example sheet.
I have entered examples of the result I want in S11 and S12 given the inputs. |
#5
|
|||
|
|||
Hi, see the attached file (with an auxiliary column) ...
|
#6
|
|||
|
|||
My advice is - redesign your data table. E.g. your price table will be like;
Column A - Product; Column B - Year; Column C - Price. If you want to use Data Validation Lists to select product or year (either in your prices table, or in your report sheet), you also need according worksheets with products list and years list. Whenever a price for new product or year must be registered, this product or year must be registered in those tables at first. After that, the user can add new price to prices table. The additional bonus - users can't enter products or years with typos. On your report sheet, you'll have data validation lists to select product and year, and the wanted price is calculated like as (I use Defined Table syntax in my example,you can replace Defined Table column references with standard range references wen you want. And in my example I defined cells with Data Validation Lists as Defined Names RepProduct and RepYear): Code:
=SUMIFS(PriceTable[Price],PriceTable[Product], RepProduct, PriceTable[Year], RepYear) |
#7
|
|||
|
|||
Thank you - that works.
Need to sort out what happens if you want to add the NDE surcharge - but it's a help! |
#8
|
|||
|
|||
What's the NDE surcharge mean?
|
#9
|
|||
|
|||
Certain vehicles has an additional surcharge - if you look at the data you will see that for some cars with a particular emission figure there is an additional % to be applied
|
#10
|
|||
|
|||
I hope, I interpreted the data in the table correctly.
|
#11
|
|||
|
|||
Sorry - it's not the too added together - the surcharge in your example is 4% - so the answer should be 30.
|
#12
|
|||
|
|||
Maybe it will be good now.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help with lookup / v lookup big failure | mda99das | Excel | 4 | 11-04-2022 09:38 AM |
User 1 cannot open Office files from File Explorer (it hangs forever); user 2 has no problem! | BillN | Office | 0 | 09-23-2020 08:55 AM |
LOOKUP - Complex lookup with 2 lookups in 1 cell | sglandon | Excel | 6 | 05-05-2016 09:44 AM |
How can I prevent a User from changing the font format set by the Powerpoint template? | Ochimus | PowerPoint | 1 | 06-29-2015 04:53 AM |
How can I get Outlook from one user to another w/o a .pst file (primary user on computer is corrupt) | Tenmakk | Outlook | 0 | 03-01-2015 12:49 PM |