v lookup problem

[ryan501]

Hello everyone,

My query is fairly complexed so instead of explaining the formula I wil
explain the scenario I am trying to accompolish.

I am trying to monitor our outstanding balances with our curren
suppliers against agreed credit limits.

To do this effectively I am downloading data from sage into
spreadsheet daily (and refreshing) which contains line by line purchas
order references with amounts etc and then on a master spreadsheet
have created a grand total columns per supplier calculating what we the
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 whe
placing orders. In column A they will enter the Purchase Order referenc
and amount line by line.

As these orders are classed as "open orders" I also need to add thes
balances to my master spreadsheet in a seperate column as "open orders"
However, if we have received an invoice for this open order (which wil
appear in the download from sage) I want to avoid duplication o
bringing a value into my open orders column of the master spreadsheet a
it has already been accounted for.

In essence I need invoices, open orders without duplication of adding i
an open order which has already been accounted for in the invoices fro
Sage.

The only way I can do this is by using the purchase order number as
reference but need a vlookup formula (i think ??) to execute this.

Your help would be very much appreciated!!!

 

[Don Guillett]

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!!!!

Send your file to me personally and I'll take a look later.