L
lonnierudd via OfficeKB.com
I have an issue that I haven't been able to find an answer to, that I'm
hoping somebody can help me with. I have a pretty involved spreadsheet that
is used to calculate the residual value of various vehicles. I've been able
through either VBA or formulas to handle most of what I need to, but there's
one section that I'm struggling with. Each month we load a table into a
workbook that has a separate tab for 80+ cars. I've already worked out how to
get this into the spreadsheet, but each month various formulas that relate to
that table need to be updated. One of these is the problem. Each month the
month that relates to the first month in the table goes down one. For example,
when I load the table for July, month 1 in the table relates to August, when
I load the table for August month 1 relates to September, etc. Each month
through the end of 2009 also needs to move down one, meaning that what was
month 1 becomes month 2, what was month 2 becomes month 3, etc. The current
month has a formula that calculates the current month's value of the vehicle,
which also needs to be updated each month, meaning moving down one month,
with the formula (E15/$W$4)*$A$A2 or something like that. The next month is
(E16/$W$4)*$A$A2. I hope this is clear. Can somebody help me out with this?
Formula or VBA either one would be appreciated.
hoping somebody can help me with. I have a pretty involved spreadsheet that
is used to calculate the residual value of various vehicles. I've been able
through either VBA or formulas to handle most of what I need to, but there's
one section that I'm struggling with. Each month we load a table into a
workbook that has a separate tab for 80+ cars. I've already worked out how to
get this into the spreadsheet, but each month various formulas that relate to
that table need to be updated. One of these is the problem. Each month the
month that relates to the first month in the table goes down one. For example,
when I load the table for July, month 1 in the table relates to August, when
I load the table for August month 1 relates to September, etc. Each month
through the end of 2009 also needs to move down one, meaning that what was
month 1 becomes month 2, what was month 2 becomes month 3, etc. The current
month has a formula that calculates the current month's value of the vehicle,
which also needs to be updated each month, meaning moving down one month,
with the formula (E15/$W$4)*$A$A2 or something like that. The next month is
(E16/$W$4)*$A$A2. I hope this is clear. Can somebody help me out with this?
Formula or VBA either one would be appreciated.