Let’s say I have cells A1:A100 filled with numbers. In cell B1 I have =sum(A1:A100). But let’s say that at any given time I may want it to just be sum(A1:A79) or sum(A1:A62) or something other than those 100 cells. What I’d like to do, and my colleagues and I have encountered scenarios where this would be helpful over the years, is put a number into, say, cell C1 and then have B1 say =sum(A1:A(C1)). The problem is that you can’t write a formula like that. I want to say, “Give me the sum of the numbers showing up in column A from cell #1 through another cell that I’ve designated elsewhere.” If you just read this and understand the concept, I’ll bet that a bunch of us have wondered the same thing.
Put “=SUM(INDIRECT(“A1:A” & C1))” in cell B1 and you should be fine
Off the top of my head, what if your cells A1:A1000 (the target cells) had a validity function attached, such that if their place in the list was outside a specified range, they blank out and don’t factor into your sum equation… - Keep your =SUM(A1:1000) permanent - apply the contingency to your data (or an adjacent column), contingent on whatever number you put into cell C1 - As you alter C1 (e.g. change to 167), only cells A1:A167 are valid, and factor into your SUM function Haven’t tried it yet, but would that work?
Brilliant. Thanks to both of you. This concept plagues me on probably 1 out of 15 spreadsheets. Much needed! -Lurker
ahd1976 Wrote: ------------------------------------------------------- > Put “=SUM(INDIRECT(“A1:A” & C1))” in cell B1 and > you should be fine Wow dude. I don’t need this now, but it will definitely come in handy someday.
how do I take a part of the data in a cell and paste in cell # 2? For example: Cell A1 = Up 2.20 (5.27%) I want in Cell B1 = 2.20 (and leave the other part out). How do I do that without manually entering it?
This is a little trickier… you can use LEFT() and RIGHT() functions to help. You can do something like =IF(LEFT(A1,2)=“Up”, , … The trick would be that you would also have to use FIND() to get the location of the left parentheses and use LEN() to get the length of the field. You could take the RIGHT side of a LEFT that only goes to the cell just before the parentheses. In other words, find the parentheses, take the LEFT of the whole cell only up to the blank before the parens, then take the right side of that chopping off the "Up " text. It would be messy but very doable. Of course you would need to make nested IFs to handle cases for Up, Down, and whatever else you might have sitting there. I’m not sure if there is an easier way that doesn’t use more cells.