Is there a formula?
Posted by Kim on October 09, 2001 5:09 AM
I have a file that contains several worksheets. One of these is an input worksheet in which the rows are product names and columns are months. Each month I input the cost of sales $ by product into this sheet. Another of the worksheets is the final report. Rather than going in each month and changing the final report worksheet formulas so that it looks at the current month vs last month, is there a formula I can enter in my report such that it will know to look at the input worksheet, current month figures?
Posted by Chris on October 09, 2001 6:12 AM
Kim
Assuming you work on a Jan to Dec column heading why not try this:
Use one cell in your workbook as the control cell in which you type the number month you are in eg. 4 for April or 12 for Dec etc. Name this cell control.
Lets assume you have product x with a data sheet set up for it for January to December in the range b2:m2. (each month you input the data into these cells)
In your report worksheet use this formula for the current month data for product x:
=index(b2:m2,,control)
and this formula for the prior month result
=index(b2:m2,,control-1)
This formula will look at the results for January to December for product x and will take the current months information from this range for the first formula and will take the current month minus 1 for the prior month information.
Hope this is what you were wanting.
Chris
Posted by IML on October 09, 2001 6:41 AM
Lo-Tech
Also consider adding blank columns directly next to your old data, and then copying the whole column over. If your workbook is set up uniformily, this should work. Then simply delete the old columns. Or, if they are set up exactly the same, you may be able to use find and replace to change, for example all b's to c's.
Just some thing else to consider.
Good luck
