Combo box with linked cell automatic update

[Shan]

Hi, I have a list of projects on a sheet, every row in column D has a combo box which draws supplier name from the next sheet "SuppName". Dependent on the supplier name, there is a vlookup formula which brings in the spend by supplier in column E.
Since the combo box is on every row (in reality there is over 50 rows and growing), I had to manuallly copy, paste and change linked cell reference manually. Is there a way to make the linked cell reference change automatically when a new row is inserted? I have attached a sample file of what I am trying to achieve.

 

[NARAYANK991]

Hi ,

If I were you , I would use Data Validation.

Narayan

 

[Bhawani]

PFA. workbook hope its same what you want
PS : why you are using combo box instead of data validation, i mean validation will also do the same.

 

[Shan]

Hi ,

If I were you , I would use Data Validation.

Narayan

I tried data validation initially but supplier list is very long and I had to scroll down to select supplier, I decided combo box was the better option as it enables auto fill.

 

[Sathish KV]

Hi,

Instead of having multiple combobox have a single comb0box and while change, the active cell may be filled with the combobox content

 

[Shan]

Hi,

Instead of having multiple combobox have a single comb0box and while change, the active cell may be filled with the combobox content

Not sure how this would work especially since the users have to select different suppliers for different rows. How can you then link the combo box selection to the different cells. I can do this manually no problem, but am looking for an automated solution.

 

[Sathish KV]

Hi,

Check out the attachment

Select any cell then go to combobox and select or type a different supplier name the activecell will be filled with the combobox value

 

[Bhawani]

It is updating two cells
1. Active Cell
2. Cell D3

Regards,
Bhawani Patim

 

[Sathish KV]

It is strange!

Let me explore and comeback

 

[Sathish KV]

It is really strange!

But just delect the row 3 and you will have only the active cell updated

 

[Shan]

Hi,

Check out the attachment

Select any cell then go to combobox and select or type a different supplier name the activecell will be filled with the combobox value

Genius Sathish, thank you so much !