vlookup & if

[pantakos]

Hello,

I need to make an excel that contains about 5 sheets. At an other sheet I have to enter the value of a cell from a different sheet but using vlookup, I also have to check if the value is not empty (anything >0). So I have to combine vlookup and IF functions.
How can I use vlookup to enter values to a different sheet? I tried but no luck. I dont know much about excel I am technician PC (cable management etc).
Can I find an example somewhere?
If needed I can upload the excel as example.
Thank you!

 

[pantakos]

probably , but i have not used VBA since the 90's , so don't answer VBA questions, i think your example with various senarios and expected results with explanation as why , may help a lot to decide if VBA is the best solution

You have a point

Here is a smaller example

AUDIO TAB (SHEET)

SPEAKERS	STOCK	PRICE PER DAY	PCS
CLAIR BROS C12	16	€110,00	12
CLAIR BROS C8	16	€50,00
CLAIR BROS CS118	4	€50,00
L-ACOUSTICS V-DOSC	40	€80,00	15

LIGHTS TAB (SHEET)

MOVING LITES	STOCK	PRICE P/D	PCS
ROBE BMFL WASH BEAM	20	€150,00
CLAY PAKY MYTHOS -2	62	€100,00	3
CLAY PAKY SHARPY	16	€50,00	4
CLAY PAKY HPE 1500 SPOT	32	€80,00

INVOICE

	MANAGER
PRO-FORMA	CLIENT
DESCRIPTION	PCS	PRICE PER DAY	TOTAL
CLAIR BROS C12	12	€110,00	1.320,00 €
L-ACOUSTICS V-DOSC	15	€80,00	1.200,00 €
CLAY PAKY MYTHOS -2	3	€100,00	300,00 €
CLAY PAKY SHARPY	4	€50,00	200,00 €
			0,00 €
		TOTAL	3.020,00 €
		VAT	724,80 €
		FINAL TOTAL	3.744,80 €

So when user change PCS to an item (AUDIO, LIGHTS or more tabs) then this will be filled to invoice (DESCRIPTION, PCS, PRICE PER DAY), pressing a button or automatically.

Thank you @etaf !

 

[etaf]

OK
You manually Enter into the Audio Sheet & into the Light Sheet , The PCS, and then want the Profima filled in based on based on just the Items with an qty entered into the PCS column

Once they have copied across - will you then expect the proforma to remain filled , even if you changed items in the Audio & Light Sheet?

Any function put into the Proforma will update whenever a change is made.

I think you maybe looking for a VBA solution after all OR even power Query, which as i'm on a Mac , I dont have access to

 

[pantakos]

OK
You manually Enter into the Audio Sheet & into the Light Sheet , The PCS, and then want the Profima filled in based on based on just the Items with an qty entered into the PCS column

Once they have copied across - will you then expect the proforma to remain filled , even if you changed items in the Audio & Light Sheet?

Any function put into the Proforma will update whenever a change is made.

I think you maybe looking for a VBA solution after all OR even power Query, which as i'm on a Mac , I dont have access to

I manually enter them into Audio and Light shhet. The PCS filled to proforma (also description need to be filled also). If there is a change , the proforma will also change

So, from your experience, I need to use VBA ? or it can be done with excel functions ?

I dont know power Query ...

Thank you !

 

[etaf]

i think it needs VBA or power Query , i'm not able to do with functions , but others maybe able to