J
JimFor
Hi,
I'm working on a program which will calculate an inventory position at any
given date in the current year. I do not know how to stop using the VLOOKUP
function at one point of the program. The inventory data is for various
accounts. There are two groups of inventory data. A total of last year's
inventory totals grouped by Account Number is located on Sheet 1 of a
workbook. The second group of data is segmented by Account number/Order
Date/Purchase Date. It contains all the individual orders and purchaes for all
the accounts for the current year. It is located on Sheet 2.
I will arrive at an inventory total for any particular date by determining
how much inventory an account had during the previous year (Sheet1) and then
subtracting from that amount any orders from the current year and/or adding to
that amount any purchases made in the current year up to the desired date
(Sheet2). I will use the VLOOKUP function to go through a list of last year's
accounts to pull up last year's inventory total for a particular account.
However, I do not know how to code the program if an account has no inventory
at the end of the previous year. The program then has to exit the VLOOKUP part
of the program and get to the first transaction for that account in Sheet 2 and
start subtracting sales and adding purchases. So my question is, "If the
program cannot find any inventory for an account on Sheet 1, how do I code it
to leave the VLOOKUP function and start calculating transactions found on Sheet
2?"
Thanks
I'm working on a program which will calculate an inventory position at any
given date in the current year. I do not know how to stop using the VLOOKUP
function at one point of the program. The inventory data is for various
accounts. There are two groups of inventory data. A total of last year's
inventory totals grouped by Account Number is located on Sheet 1 of a
workbook. The second group of data is segmented by Account number/Order
Date/Purchase Date. It contains all the individual orders and purchaes for all
the accounts for the current year. It is located on Sheet 2.
I will arrive at an inventory total for any particular date by determining
how much inventory an account had during the previous year (Sheet1) and then
subtracting from that amount any orders from the current year and/or adding to
that amount any purchases made in the current year up to the desired date
(Sheet2). I will use the VLOOKUP function to go through a list of last year's
accounts to pull up last year's inventory total for a particular account.
However, I do not know how to code the program if an account has no inventory
at the end of the previous year. The program then has to exit the VLOOKUP part
of the program and get to the first transaction for that account in Sheet 2 and
start subtracting sales and adding purchases. So my question is, "If the
program cannot find any inventory for an account on Sheet 1, how do I code it
to leave the VLOOKUP function and start calculating transactions found on Sheet
2?"
Thanks