Greetings,
I have a worksheet with about 45 functions, they all are the same as the function below, except for the text string.
=SUMPRODUCT(--(Data!C:C= "Direct Mail - Acquisition"),--(Data!A:A>DATE(2009,10,1)),--(Data!A:A<=DATE(2009,12,31)),Data!B:B)
Is there a way to change the date range en masse, rather than having to go into each one and change the date range from 10-1-09 to 12-31-09, to 10-1-08 to 12-31-08?
Thanks a lot for your help!
Cheers,
Bryce
Last edited by bkatzman; 04-02-2010 at 10:13 AM.
It is possible by using Edit > Replace
Find
Replace=SUMPRODUCT(--(Data!C:C= "Direct Mail - Acquisition"),--(Data!A:A>DATE(2009,10,1)),--(Data!A:A<=DATE(2009,12,31)),Data!B:B)
Replace all=SUMPRODUCT(--(Data!C:C= "Direct Mail - Acquisition"),--(Data!A:A>DATE(2009,12,31)),--(Data!A:A<=DATE(2008,12,31)),Data!B:B)
Or is your problem more complicated than that?
That was it. I should have been more optimistic that it would have worked!
BTW - A separate issue, Is there any way to copy and paste functions exactly as they are? I am finding that when I copy and paste the following Excel changs the column references, (i.e., C:C to F:F, etc..) even when I use paste special>functions...
=SUMPRODUCT(--(Data!C:C= "Direct Mail - Acquisition"),--(Data!A:A>DATE(2009,12,31)),--(Data!A:A<=DATE(2008,12,31)),Data!B:B)
Thanks a lot!!!
Last edited by shg; 04-02-2010 at 12:42 PM. Reason: deleted spurious quote
You can make you references absolute:
=SUMPRODUCT(--(Data!$C:$C= "Direct Mail - Acquisition"),--(Data!$A:$A>DATE(2009,12,31)),--(Data!$A:$A<=DATE(2008,12,31)),Data!$B:$B)
or highlight the range, do a find replace for = with ##, copy and paste and then reverse the find replace.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Brilliant! Thanks so much. Here's to Brian Clough!
Last edited by shg; 04-02-2010 at 12:42 PM. Reason: deleted spurious quote
Use absolute references in your formulae this way when you paste it will refer to the correct column or row, or both. the $ sign "anchors" the reference
e.g.
To add $ to refs put the cursor into the cell ref eg A2 and press f4 and you will see $A$2 press f4 again and A$2 and again $A2=SUMPRODUCT(--(Data!$C:$C= "Direct Mail - Acquisition"),--(Data!$A:$A>DATE(2009,12,31)),--(Data!$A:$A<=DATE(2008,12,31)),Data!$B:$B)
Cheers
Excellent idea. I will use it as well!
Last edited by shg; 04-02-2010 at 12:42 PM. Reason: deleted spurious quote
For your future reference
No need to quote full posts in your replies.
Just quote relevant bits, if any at all.
Glad to have helped.
P.S.
Something we in Scotland always puzzled over
Could Brian Cloughs' head fit in Jimmy Hills' mouth?
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks