41

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.

Improve this question
2
  • 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? Commented Aug 27, 2012 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. Commented Aug 27, 2012 at 23:35

2 Answers 2

Reset to default
57

Use INDIRECT()

=SUM(INDIRECT(<start cell here> & ":" & <end cell here>))
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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?
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
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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.