Excel VBA drop down populate cells

[Sandro1985]

i am currently trying the following.

if you select under column "K" (Equip/Serv) "Lumpsum" i want the user to be able to manually enter a value in column "P" Unit price.

if the user selects from the drop down menu in column "K" example "Lifting Gear" the value in "P" should be automatically populated from the DATA sheet.

For the "Lumpsum", the formular at present will be overwritten when keying in manually a value.

If not wrong, the above could be achieved with VBA, just looking around to figure out whether someone is able to help on this portion.

Template.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Customer Name - Job Summary																Last Update:	12/04/2024
2																	Update By:
3
4
5	ID	Job Date	Month	Year	Start Time	End Time	Orderd By	SMP Job #	SMP Invoice	Customer PO#	Equp. / Serv.	Qty	Place of Work	From	To	Unit Rate	Lumpsum	Remarks
6	1										Lifting Gear					500.00	-
7	2										Lumpsum					-	-
8	3										Lifting Team					1,000.00	-
9	Total																-
Work summary

Cell Formulas
Range	Formula
R1	R1	=TODAY()
C6:C8	C6	=IF([@[Job Date]]="","",TEXT([@[Job Date]],"mmmm"))
D6:D8	D6	=IF([@[Job Date]]="","",YEAR([@[Job Date]]))
A6:A8	A6	=ROW()-5
P6:P8	P6	=IFERROR(VLOOKUP([@[Equp. / Serv.]],DATA!A:B,2,FALSE),"")
Q6:Q8	Q6	=IFERROR([@[Unit Rate]]*[@Qty],"")
Q9	Q9	=SUBTOTAL(109,[Lumpsum])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N6:O8	Expression	=$M6<>""	text	NO
M6:M8	Expression	=$N6<>""	text	NO

Cells with Data Validation
Cell	Allow	Criteria
K6:K8	List	=$T6#

 

[SanjayGMusafir]

If not wrong, the above could be achieved with VBA, just looking around to figure out whether someone is able to help on this portion.

Welcome to the forum

Simply add an IF condition like

=IF([@[Equp. / Serv.]]="Lumpsum","",IFERROR(VLOOKUP([@[Equp. / Serv.]],DATA!A:B,2,FALSE),""))

This way it should give you a blank (appearing) cell in column P to manually input value, else it shall perform VLOOKUP

Another advise, rather than using VLOOKUP use XLOOKUP, it gives you more flexibility.

 

[Fluff]

This way it should give you a blank (appearing) cell in column P to manually input value,

But the formula would still be lost if you enter something manually.

 

[SanjayGMusafir]

But the formula would still be lost if you enter something manually.

Yes @Fluff . As we know any manual entry would overwrite the column formula in that cell, be it any mode. So thought of giving him an alternate solution though it's already within his IFERROR formula.

Regards

 

[Fluff]

I don't see how that is an alternative solution, as it does not allow the OP to do what they are asking for.