Can I declare a Variable?

Question

Level 3

697 points

Can I declare a Variable?

I have many different formulas in my sheet that refer to the same range of cells. That range of cells will change from year to year. I want to use a separate cell that contains the range.

I would like to then refer to that cell (as a variable) in the formulas. That way I only need to change the value of the variable each time the range of cells change.

This is an example just to illustrate my question. It isn't the actual table.

There will be many formulas and I don't want to have to edit each of them every time the range of cells changes.

How do i do this?

Posted on Apr 4, 2023 1:37 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,948 points

Apr 4, 2023 2:07 PM in response to DLR

=SUM(INDIRECT(E5))

INDIRECT is kind of a slow function. If "many" formulas is thousands or tens of thousands, you might notice it being slow. I hope Apple one day figures out how to make Numbers as lightning quick with calculations as Excel is but, until then, we have to consider the speed of certain functions when there are a lot of them.

You might try rethinking how your spreadsheet works so you don't have to use a variable range like this.

Reply

Answer 2

DLR Author

Level 3

697 points

Apr 4, 2023 3:04 PM in response to Badunit

I am so happy I decided to post the question. I had Googled it so many different ways and found nothing. You answered it in one short simple line.

Thank you for the help and thank you for the slowness warning. Won't be a problem for me as it is less than 20 formulas and speed is not a concern.

Reply