View Full Version : Excel formula help!
12-11-2008, 12:38 AM
I need to change the range referenced in a formula depending on the value in another cell.
To simplify: =Sum(A1:A99) but where it says A99, I need that to be based on the value of cell B1.
So if Cell B1 = "A99"
Then it would be Sum of A1 - A99
If cell B1 = "A9999"
Then it would be sum of A1 - A9999
Any help would be appreciated!
12-11-2008, 06:51 AM
Where B1:B18 is a list of a 1,2,3...
D1 is 99 or 9999 in your example
and C1:C18 is a list of all possible cells you might want to sum
12-11-2008, 03:06 PM
Thanks for your reply...
Let me get more specific, because what you say works, but not in my situation...
I am actually using SUMPRODUCT function, which means I have to specify the exact range of data (i can't simply specify the entire column or it will return an error)
So my formula is as follows:
So everywhere there is a range "$A$1124" i want to refrence that value in a cell... because depeding on the data set it may be $A$9999, or $A$300, etc.
If it were just one formula i could just update it manually, but there are lots of them.
12-11-2008, 04:31 PM
I'm not sure I get your formula. Sumproduct seems to want to take a list of arrays for me and you seem to have fed it one expression in which you're trying to AND a bunch of boolean expressions.
Maybe this will answer your question: I don't think you can dynamically specify ranges. You have to be clever and sneaky about this sort of thing like creating a derived column where you replace the values you don't want to sum with 0.
12-11-2008, 05:35 PM
Well, I took your advice and got sneaky...
I just filled in 'dummy' data that is outside of the date range that I will every be querying for… I put in 9999 rows of dummy data in and set my formula to always check all the way from $A1:$A9999 …. Works like a charm too!
12-11-2008, 05:48 PM
Well, apparently im the dummy. I set the formulas as A9999 and it works, even if the cells are blank. Which means I can put in a data set of any size smaller than that and it will work :)
12-11-2008, 08:39 PM
:) Glad I could help
12-12-2008, 06:15 AM
I think I blew a fuse in my brain.
Powered by vBulletin® Version 4.1.12 Copyright © 2013 vBulletin Solutions, Inc. All rights reserved.