Hi,
This is sortof the inverse of indirect(), which takes text and outputs a range.
I have looked but can not find out how to do this.
The reason I need this is that I have a sheet with the formula in a cell is:
=average(someRange) that returns the average.
someRange is calculated and I am not sure it is correct.
I’d like a function, someFunction(), such that,
if someRange is A10:B45
=someFunction(someRange) returns “A10:B45”
Thank you.
When you pass a range to a Cell Function, it will be passed the contents of the cells only, but not the other informations about the cell range. The name and the position of the range, the formatting properties will not be passed, therefore you can not “decode” these properties from a passed cell range by Cell Functions even if they are custom - macro based - cell functions.
Workaround: Create a custom cell function, and pass the Row and Column numbers of the top-left and the bottom-right cells of the range to it. Then you will able to get the textual reference of the passed cell range (the name, or the “A1” type reference by usage the relevant API functions.)
What information gain does such a function give you? Obviously you already know »someRange« beforehand!
As I said in OP I’m not sure I have calculated the range properly, I’d like to confirm that what I think I’m doing is actually what is happening.
Also it would be useful for debugging, I tend to put my formulas together in small bits seeing what each bit outputs to the next, but if a range is being output you can’t do this.
I have changed the way I approach the problem,
instead of using =AVERAGE(OFFSET(INDIRECT(ADDRESS(
I have done the offset and address part using string functions (my_text_range) in a separate column
and used, =Average(indirect(my_text_range))
Only problem is if I insert or delete a column, I will have to remember to modify the string calculation.
.
Zizi64
I don’t understand what you are saying.
If indirect doesnt return a range but passes the contents of the cells only, but not the other informations about the cell range,
why does =ROW(INDIRECT(“a10:b45”)) returns 10, first row of the range which row() isn’t supposed to know about.
for the same reason as =ROW(a10:b45) returns 10 (respectivly as array-formula a range with numbers from 10 to 45)
