Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a list of items (cells A1 to A7) that are counted in cell B2 (whose value is currently 7), B2's formula is something like =COUNTA(A1:A10000). That way if I add some value in cell A8, the count is updated to 8 in cell B2. And so on and so forth when values are filled in cells A9, A10, ... etc, the count in cell B2 always keeps track of the current number of items in the list.
It would be very handy to have the ability of creating a named range, called items for instance, based on the count in cell B2 using

=OFFSET(sheet!$A$1,0,0,sheet!$B$2,1)

or

=ArrayFormula(OFFSET(sheet!$A$1,0,0,sheet!$B$2,1))

to define the named range.

That way items could be used anywhere and would get dynamically updated when the item list is modified. This is the way it can be done in Excel, but what would be the corresponding approach using Google speadsheets?

share|improve this question
    
Perhaps you can share a doc with us and show us the expected result. – Jacob Jan Tuinstra Mar 27 '13 at 11:08
up vote 4 down vote accepted
  1. You could use a simple formula to build a string defining the Range, name that location, and then access it with the INDIRECT Function in your formulas. E.g. for your question above.

    Put in any cell (e.g. B3) a formula like:

    = "Sheet!$A$1:$A$" & B2
    
  2. Name Cell B3 as "items" as a named range.

  3. Then whenever you would have used "items" in a formula just use

    INDIRECT(items)
    

It is not quite the same as a dynamic named range, but almost as neat and certainly as effective.

share|improve this answer
    
That's quite witty, albeit not exactly what I was hoping for (as it is possible in Excel). But it is close enough to accept your suggestion as the solution! Thanks. – mks-d Jan 29 at 4:58

Could this be helpful? http://anandexcels.wordpress.com/tag/offset-function-in-google-docs/

If it is really just A1:A7 then maybe you could select the first 100 cells in Column A or something.

share|improve this answer
1  
No I'm afraid this just explains what the offset function is. I am more than confortable with it, I just want to know if a named range can be defined from such as function to make it dynamic. – mks-d Mar 26 '13 at 7:44
2  
Please include the relevant information from that link in your answer. If the page you're linking to should go away some day, your answer will be left useless. – Vidar S. Ramdal Jan 26 at 8:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.