A
ak_edm
Column B contains a bunch of information for Monday, March 23rd. Column C
contains a bunch of information for Tuesday, March 24th and so on. The data
is set up so it’s easy to print out on a sheet, and every week the
information is overwritten with the new week’s information. Column B becomes
Monday, March 30th, column C becomes Tuesday, March 31st and so on. Every
week the dates are updated and the columns re-written.
I want to preserve the information so long-term trends etc can be analyzed.
So I’m thinking that I’ll pull the information into another sheet for each
day, and wow wouldn’t it be great if I could do this automatically. I’ll set
up another sheet where each row represents a date: row 1 will be March 23rd,
row 2 will be March 24th and so on. Then the information from Monday, March
23 (column B) can be placed into the first row, the information from Tuesday,
March 24(column C) can be placed into the second row, and so on. It would be
a real-time update so as the information for the current day changes then the
preserved information also changes, and the beauty is that once that day is
over and it’s the next day, then that preserved information from the day
before is no longer touched.
How do I do it? LOL.
You’d think the formula “=B1†would do what I need, but B1 will eventually
represent the next week. I don’t want to overwrite my preserved information
from the week before so I need a condition – an IF function based on date.
But an IF function requires a false action : IF true then copy, IF false
then…two quotes? A zero? Hmmm. No matter what I place into the function, the
false condition blanks my data. I cant find a way to leave the data alone.
I cannot preserve it.
The problem with using a formula to grab data from a fixed location such as
the numbers in a cell black is that the source numbers will eventually
change, and since the formula to grab that information will always remain
active, it too will eventually overwrite the original data I wanted to
preserve, which is what I don't want, or it's going to return a "" or zero
value because I tried to make it conditional on date March 23 only, which is
also what I don't want.
I need a "do nothing" so March 23 is preserved once it's written into the
row that preserves information for March 23 and it’s no longer March 23rd.
I'm thinking I need a macro, VBA, something.
I need a macro or VBA code to say :
IF today is March 23, 2009 then write to here, else leave this location
alone (don't write "" double quotes, don't write a zero, dont erase the
information you wrote earlier, just leave this location alone and move on,
whatever number that was brought to this location on March 23, 2009
just leave it alone, it must not be March 23, 2009 anymore so move on).
The next row, because it's a new day, will be similar : IF today is March
24, 2009 then write to here, else...yadda yadda yadda
And so on for the week of March 23, 2009.
Next week column B now represents March 30, 2009, and there's a new row for
preserving the information: IF today is March 30, 2009 then write to here,
else...yadda yadda yadda
Preservation Sheet:
Row 1 : March 23 – the information for March 23 goes here.
Row 2 : March 24 – the information for March 24 goes here.
…
Row 8 : March 30 – the information for March 30 goes here.
…
- Eric
contains a bunch of information for Tuesday, March 24th and so on. The data
is set up so it’s easy to print out on a sheet, and every week the
information is overwritten with the new week’s information. Column B becomes
Monday, March 30th, column C becomes Tuesday, March 31st and so on. Every
week the dates are updated and the columns re-written.
I want to preserve the information so long-term trends etc can be analyzed.
So I’m thinking that I’ll pull the information into another sheet for each
day, and wow wouldn’t it be great if I could do this automatically. I’ll set
up another sheet where each row represents a date: row 1 will be March 23rd,
row 2 will be March 24th and so on. Then the information from Monday, March
23 (column B) can be placed into the first row, the information from Tuesday,
March 24(column C) can be placed into the second row, and so on. It would be
a real-time update so as the information for the current day changes then the
preserved information also changes, and the beauty is that once that day is
over and it’s the next day, then that preserved information from the day
before is no longer touched.
How do I do it? LOL.
You’d think the formula “=B1†would do what I need, but B1 will eventually
represent the next week. I don’t want to overwrite my preserved information
from the week before so I need a condition – an IF function based on date.
But an IF function requires a false action : IF true then copy, IF false
then…two quotes? A zero? Hmmm. No matter what I place into the function, the
false condition blanks my data. I cant find a way to leave the data alone.
I cannot preserve it.
The problem with using a formula to grab data from a fixed location such as
the numbers in a cell black is that the source numbers will eventually
change, and since the formula to grab that information will always remain
active, it too will eventually overwrite the original data I wanted to
preserve, which is what I don't want, or it's going to return a "" or zero
value because I tried to make it conditional on date March 23 only, which is
also what I don't want.
I need a "do nothing" so March 23 is preserved once it's written into the
row that preserves information for March 23 and it’s no longer March 23rd.
I'm thinking I need a macro, VBA, something.
I need a macro or VBA code to say :
IF today is March 23, 2009 then write to here, else leave this location
alone (don't write "" double quotes, don't write a zero, dont erase the
information you wrote earlier, just leave this location alone and move on,
whatever number that was brought to this location on March 23, 2009
just leave it alone, it must not be March 23, 2009 anymore so move on).
The next row, because it's a new day, will be similar : IF today is March
24, 2009 then write to here, else...yadda yadda yadda
And so on for the week of March 23, 2009.
Next week column B now represents March 30, 2009, and there's a new row for
preserving the information: IF today is March 30, 2009 then write to here,
else...yadda yadda yadda
Preservation Sheet:
Row 1 : March 23 – the information for March 23 goes here.
Row 2 : March 24 – the information for March 24 goes here.
…
Row 8 : March 30 – the information for March 30 goes here.
…
- Eric