vlookup & if

pantakos

Board Regular
Joined
Oct 10, 2012
Messages
88
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

Board Regular
Joined
Oct 10, 2012
Messages
88
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 STOCKPRICE PER DAYPCS
CLAIR BROS C1216€110,0012
CLAIR BROS C8 16€50,00
CLAIR BROS CS1184€50,00
L-ACOUSTICS V-DOSC40€80,0015


LIGHTS TAB (SHEET)

MOVING LITESSTOCKPRICE P/DPCS
ROBE BMFL WASH BEAM20€150,00
CLAY PAKY MYTHOS -2 62€100,003
CLAY PAKY SHARPY16€50,004
CLAY PAKY HPE 1500 SPOT32€80,00


INVOICE

MANAGER
PRO-FORMA CLIENT
DESCRIPTIONPCSPRICE PER DAYTOTAL
CLAIR BROS C1212€110,001.320,00 €
L-ACOUSTICS V-DOSC15€80,001.200,00 €
CLAY PAKY MYTHOS -2 3€100,00300,00 €
CLAY PAKY SHARPY4€50,00200,00 €
0,00 €
TOTAL3.020,00 €
VAT724,80 €
FINAL TOTAL3.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 !
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,113
Office Version
  1. 365
Platform
  1. MacOS
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

Board Regular
Joined
Oct 10, 2012
Messages
88
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

Well-known Member
Joined
Oct 24, 2012
Messages
5,113
Office Version
  1. 365
Platform
  1. MacOS
i think it needs VBA or power Query , i'm not able to do with functions , but others maybe able to
 

Forum statistics

Threads
1,148,242
Messages
5,745,588
Members
423,963
Latest member
lwilson3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top