Dropdown table

[Edgecrusher]

Hi,

Problem is:

I have to make a dropdown list which will contain Customer (10 of them), and after selection his product price will appear underneath. There are about 80 products. Person viewing it should be able to pick customer from drop down list and than that customer's product prices will appear bellow - that customer will be the base. On cell next to base customer, person should have an option to pick again any customer with his list price, but that list price will be shown as index of his price/base customer price. Any customer can be picked as base.

Should look like this:

1 2 3 4 5
A Products Customer 1 Customer 2 Customer 3 Customer 4
B Product 1 21,15 3% 1,4% 2%
C Product 2 17,99 -8% 4% 0%
D Product 3 7,68 1% 2,5% -14%
E Product 4 1,01 4% 4% -3%


Any ideas? I've tried with macros (epic fail - don't know much about them), Access - don't know if it's possible to do it in it. Pivot - I haven't find an option to calculate this.

Thanx in advance, any advice will be helpfull for brainstorming this.

[NBVC]

For the drop down lists, see here on how to create....

http://www.contextures.com/xlDataVal01.html

Then to get relevant data, see how with Vlookup() function...

http://www.contextures.com/xlFunctions02.html

If you need further help, post a workbook example.

[Edgecrusher]

Thanks for response.

I've managed to get first step down. I made drop down list. Now I have a problem with combining that list with data. Attached is example.

So what I need to have is if I pick from drop down list customer 2 he will be base customer with prices that are bellow him on reference sheet.

Now, if I pick customer 1 next to him, it should show that customer, but prices will be shown as change in percentage points according to base customer.

[NBVC]

Not sure what you mean exactly by "showing change in percentage points"

If you apply this formula in D3:

Please Login or Register  to view this content.

and copy down and across the 4 columns, then it will pull the relevant numbers from the Reference table...

... I guess you can manipulate the formula in the 2nd to 4th columns to do the math you want based on the pulled results and the results of the 1st column.

[Edgecrusher]

I don't know why but I can't manage to get that formula working...

Nevermind about the calculation, I might confused you with my wish, I can allways make additional formula in a row next by it.

If you can please help me with vlookup function here so it will give back values (prices) when i select wanted customer on a drop down list? So I just need to combine customer with his prices when selected on drop down.

Also I haven't thanked you so far so Thank you.

[NBVC]

See attached..

I applied the formula D3:G7

When you select the customer from your drop down menus, the appropriate data should populate beneath.

[Edgecrusher]

Thanks alot, I'll try to get it working on bigger scale.

Edit: After some adjustments, it finally works. You saved my life .