40

I'd like to know how to pull cell references from the value of another cell and insert them into a formula.

For a simple example:

In cell A1 I have this: COUNT(B4:H4)

Instead of choosing the range when I set up the formula, I'd like this range to be dynamic, based on the value in other cells.

So, I want to type in cell references: in cell B4, I'd type in C5, and in H4 I'd type in C8. How can I set up my formula to look inside cells B4 and H4, but then return the range that is defined by the values in these cells (C5:C8 in this case...) So in the end, cell A1 would return the count of C5:C8

I hope that makes sense. It seems like something pretty simple but I can't seem to find info on it.

starball
  • 20,030
  • 7
  • 43
  • 238
KrazyKiddo8
  • 445
  • 1
  • 5
  • 8
  • I'm not sure I understand, do you mean you want the sum of `C5:C8` in cell `B4`? I got lost when you said "then return the range that is defined by the values in these cells" You want a cell to return a range? – just eric Aug 27 '12 at 23:23
  • Sorry, I just edited to clarify. I want the count of C5:C8 in cell A1. I want my formula to be pointing to cells `B4` and `H4` which will actually contain the text `"C5"` and `"C8"`, respectively. – KrazyKiddo8 Aug 27 '12 at 23:35

2 Answers2

56

Use INDIRECT()

=SUM(INDIRECT(<start cell here> & ":" & <end cell here>))
ApplePie
  • 8,814
  • 5
  • 39
  • 60
  • 5
    How would you do the same if the formula contained cells from a different sheet and you had to take the sheet from a value in another cell? – kkoolpatz Apr 18 '16 at 15:55
  • 1
    The answer for kkoolpatz is: =SUM(INDIRECT(A2&"!A1")) where A2 contains the sheet name. In gnumeric you need to add the ' (quote), enclosed in double quotes. E.g.: =max(indirect("'"&A2&"'"&"!K:K")) to find the max of column K in the sheet whose name is in cell A2 – MastroGeppetto Oct 16 '18 at 16:45
0

There is a non-volatile way to do it using INDEX and MAKEARRAY, something like:

=COUNT( INDEX(C:C, MAKEARRAY(B4-H4+1, 1, LAMBDA(r,c,B4+r-1))) )

Except that now B4 and H4 need only contain the row numbers of the start and end i.e. '5' and '8' in your example. This is only simple if you are always talking about single columns you want to process - multiple row-and-column ranges would be possible, but the formula would not be friendly!

Anyway, this is only relevant if you are going to do it a lot in your spreadsheet, when INDIRECT would start to slow you down because it always recalculates.

RobBaker
  • 137
  • 11