3 dropdown list, update on each other respectively...

Hello, could you not use vlookup?

If need be, apply the vlookup when they select the item...

so, in the sheet code (not a module)

Please Login or Register  to view this content.

Where G7 is the location of the drop down menu.

then somewhere else, use something like this

Please Login or Register  to view this content.

Hi aldredd,

Thanks for your reply, basically i 've tried what you have demonstrated to me, i coded as

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Did i put in the wrong function? I've create an excel list with a populated data, It's appreciated if you can take a look and advise me on this...

btw, i save the file as excel 2003 workbook

Thank you so much!
Regards,

Originally Posted by aldredd

Hello, could you not use vlookup?

If need be, apply the vlookup when they select the item...

so, in the sheet code (not a module)

Please Login or Register  to view this content.

Where G7 is the location of the drop down menu.

then somewhere else, use something like this

Please Login or Register  to view this content.

Hi Kimyap,

you got the vlookup part slightly wrong..

it should look something like...

Please Login or Register  to view this content.

You were trying to change the range in the list sheet, rather than the range on the category sheet (in Bold)
You were also referencing the source range incorrectly as well; you used

Please Login or Register  to view this content.

but to refer to a range in a different sheet, you first specify the sheet, then the range...

Please Login or Register  to view this content.

Once you have that bit, you can add the line to get the second part number...

Please Login or Register  to view this content.

so your finished sub looks like

Please Login or Register  to view this content.

then add another line in your Worksheet_Change event to look for the other changes...

Please Login or Register  to view this content.

Now, I can see 2 foreseeable problems..

1. you wont be able to do a vlookup against the second part number to get the first part number or product name, as you can't vlookup to the left. For this, you will need to either do a Index / Match or Offset / Match (both do the same) just search 'Left vLookup' for help on this
2. I don't know what will happen when you add all the other 'worksheet change' bits. Because, when you change the first part code, it changes the cell C4, and if there is also a trigger on this cell, it will try to to do the vlookup and so on, so it may go into a bit of a frenzied loop - I'm not sure. If it does cause a problem, try using application.calculation = xlManual to turn off calculation, and then application.calculation = xlAutomatic to turn it back on once it's done the lookups

OK, I decided to have a bit of a play with this - to settle my own curiosity you could say

Anyway, I was right in saying you cant lookup to the left - but offset wasn't the answer - but found a similar solution.

More importantly, I was correct about the frenzied looping thing - the code will go on infinity, re calculating everything over & over again.

Luckily, I tinkered, and found the solution!

Please Login or Register  to view this content.

Ok, this bit is just a tidier way of doing the IF statements.

Please Login or Register  to view this content.

Here we have the 2 chucks of code / subs

As you see, the last one uses a formula to find the row you're looking for, and then find the correct match that way.
Most importantly, each sub checks to see if the cell you're working on matches the cell the code relates to, and only continues if this is correct - thus fixing our looping problem!

have fun!

Yooooh aldredd,

You are the great, making thing simple and easier, the code is running fine... Thank You...

BTW, this is just a stating for my workbook, i'm working on the xls and database now...

Good luck...

Originally Posted by aldredd

OK, I decided to have a bit of a play with this - to settle my own curiosity you could say

Anyway, I was right in saying you cant lookup to the left - but offset wasn't the answer - but found a similar solution.

More importantly, I was correct about the frenzied looping thing - the code will go on infinity, re calculating everything over & over again.

Luckily, I tinkered, and found the solution!

Please Login or Register  to view this content.

Ok, this bit is just a tidier way of doing the IF statements.

Please Login or Register  to view this content.

Here we have the 2 chucks of code / subs

As you see, the last one uses a formula to find the row you're looking for, and then find the correct match that way.
Most importantly, each sub checks to see if the cell you're working on matches the cell the code relates to, and only continues if this is correct - thus fixing our looping problem!

have fun!