B
BeSmart
Hi all
I'm working on a table of data where we update each months data AND hold
last months data.
Example Table:
Jan_10 Feb_10 Mar_10 Apr_10 May_10 etc...
Prod 1 55 10 0 0 0
0
Prod 2 5 0 0 0 0
0
Prod 3 0 10 0 0 0
0
Prod 4 55 10 0 0 0
0
etc..
All figures for forward months (e.g. from Feb onwards) are hlookups or
vlookups formulas that source data from other worksheets within this
workbook. e.g. enter Feb_10 into M1 and the lookups work for Feb_10 column.
(Feb_10 is entered as text).
But, when we change the lookup in M1 to Feb_10 - we loose Jan_10 data from
the table (goes back to 0's).
I currently have to manually copy/paste the old months data (in order to
keep it).
How do I write a macro that will :
- lookup the range and select the last column of formulas that show a result
- then copy/paste values that column.
Range for data table is M4:AM80 - each column is a month (rolling years)
with the last column totalling them.
Note: Sometimes some formula results will be 0 (zero), but not in all cells
in the column. e.g. use If "total value of the column" >0 then ...
I would really appreciate any help with this.
I'm working on a table of data where we update each months data AND hold
last months data.
Example Table:
Jan_10 Feb_10 Mar_10 Apr_10 May_10 etc...
Prod 1 55 10 0 0 0
0
Prod 2 5 0 0 0 0
0
Prod 3 0 10 0 0 0
0
Prod 4 55 10 0 0 0
0
etc..
All figures for forward months (e.g. from Feb onwards) are hlookups or
vlookups formulas that source data from other worksheets within this
workbook. e.g. enter Feb_10 into M1 and the lookups work for Feb_10 column.
(Feb_10 is entered as text).
But, when we change the lookup in M1 to Feb_10 - we loose Jan_10 data from
the table (goes back to 0's).
I currently have to manually copy/paste the old months data (in order to
keep it).
How do I write a macro that will :
- lookup the range and select the last column of formulas that show a result
- then copy/paste values that column.
Range for data table is M4:AM80 - each column is a month (rolling years)
with the last column totalling them.
Note: Sometimes some formula results will be 0 (zero), but not in all cells
in the column. e.g. use If "total value of the column" >0 then ...
I would really appreciate any help with this.