Home |
Search |
Today's Posts |
#1
|
|||
|
|||
v lookup problem
Hello everyone,
My query is fairly complexed so instead of explaining the formula I will explain the scenario I am trying to accompolish. I am trying to monitor our outstanding balances with our current suppliers against agreed credit limits. To do this effectively I am downloading data from sage into 1 spreadsheet daily (and refreshing) which contains line by line purchase order references with amounts etc and then on a master spreadsheet I have created a grand total columns per supplier calculating what we then have available to spend (actual v's credit limit). Here comes the difficult bit......... I am then have a seperate spreadsheet which requisition users use when placing orders. In column A they will enter the Purchase Order reference and amount line by line. As these orders are classed as "open orders" I also need to add these balances to my master spreadsheet in a seperate column as "open orders". However, if we have received an invoice for this open order (which will appear in the download from sage) I want to avoid duplication of bringing a value into my open orders column of the master spreadsheet as it has already been accounted for. In essence I need invoices, open orders without duplication of adding in an open order which has already been accounted for in the invoices from Sage. The only way I can do this is by using the purchase order number as a reference but need a vlookup formula (i think ??) to execute this. Your help would be very much appreciated!!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
v lookup problem
On Tuesday, March 6, 2012 7:42:39 AM UTC-6, ryan501 wrote:
Hello everyone, My query is fairly complexed so instead of explaining the formula I will explain the scenario I am trying to accompolish. I am trying to monitor our outstanding balances with our current suppliers against agreed credit limits. To do this effectively I am downloading data from sage into 1 spreadsheet daily (and refreshing) which contains line by line purchase order references with amounts etc and then on a master spreadsheet I have created a grand total columns per supplier calculating what we then have available to spend (actual v's credit limit). Here comes the difficult bit......... I am then have a seperate spreadsheet which requisition users use when placing orders. In column A they will enter the Purchase Order reference and amount line by line. As these orders are classed as "open orders" I also need to add these balances to my master spreadsheet in a seperate column as "open orders". However, if we have received an invoice for this open order (which will appear in the download from sage) I want to avoid duplication of bringing a value into my open orders column of the master spreadsheet as it has already been accounted for. In essence I need invoices, open orders without duplication of adding in an open order which has already been accounted for in the invoices from Sage. The only way I can do this is by using the purchase order number as a reference but need a vlookup formula (i think ??) to execute this. Your help would be very much appreciated!!!! -- ryan501 Send your file to me personally and I'll take a look later. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP Problem | Excel Discussion (Misc queries) | |||
Lookup problem | Excel Worksheet Functions | |||
LookUp Problem | Excel Worksheet Functions | |||
lookup problem | Excel Worksheet Functions | |||
Lookup Problem | Excel Worksheet Functions |