Cell Function question

  • Thread starter Thread starter deltree
  • Start date Start date
D

deltree

I am using the Cell function nested in a bigger formula in my workboo
and I am trying to set up an array...but its not working. So I am jus
trying to make a simple array with any Cell function and it isn'
working.

Is this possible? I can't understand that Excel wouldn't let yo
evalutate a range in the Cell function.

i.e. =CELL("address", E10:E14)

Any comments are appreciated for a noob like me
 
deltree said:
I am using the Cell function nested in a bigger formula in my workbook
and I am trying to set up an array...but its not working. So I am just
trying to make a simple array with any Cell function and it isn't
working.

Is this possible? I can't understand that Excel wouldn't let you
evalutate a range in the Cell function.

i.e. =CELL("address", E10:E14)

Any comments are appreciated for a noob like me.
Click to expand...

No, it's not possible using CELL. If you want an array of addresses for each
cell in E10:E14, you need to use the ADDRESS function.

=ADDRESS(ROW(E10:E14),COLUMN(E10:E14))

CELL is designed to return one and only one result, and if fed a multiple
cell range, it uses only the top-left cell of that range.
 
Cell will always refer to the first cell so it will be E10,
you can use multiple cell functions or other formulas like offset and
address etc..

--

Regards,

Peo Sjoblom

deltree said:
I am using the Cell function nested in a bigger formula in my workbook
and I am trying to set up an array...but its not working. So I am just
trying to make a simple array with any Cell function and it isn't
working.

Is this possible? I can't understand that Excel wouldn't let you
evalutate a range in the Cell function.

i.e. =CELL("address", E10:E14)

Any comments are appreciated for a noob like me.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Hey, thats great. I didn't realize that the Cell function was working
that way. Just what I needed to know.

So I setup the Address function instead of the Cell function and its
almost working.

My function looks like this:

=INDIRECT(CONCATENATE(SheetName,"!",ADDRESS(ROW(BA7:BA25),
COLUMN(BA7:BD7))))

and it does not work as an array at this point.

I can setup an array with the nested Concatenate function:

=CONCATENATE(SheetName,"!",ADDRESS(ROW(BA7:BA25), COLUMN(BA7:BD7)))

this function works fine as an array but as soon as I nest it, it
doesn't work anymore.

Does anyone know if I am doing something wrong?
 
...
...
So I setup the Address function instead of the Cell function and its
almost working.

My function looks like this:

=INDIRECT(CONCATENATE(SheetName,"!",ADDRESS(ROW(BA7:BA25),
COLUMN(BA7:BD7))))

and it does not work as an array at this point.
Click to expand...

Mind telling us how this doesn't work? Does Excel prevent you from entering
this? Does Excel allow entry but return error values? If so, what error values?
Or does it return something else, not error values but also not what you
expected?
I can setup an array with the nested Concatenate function:

=CONCATENATE(SheetName,"!",ADDRESS(ROW(BA7:BA25), COLUMN(BA7:BD7)))

this function works fine as an array but as soon as I nest it, it
doesn't work anymore.

Does anyone know if I am doing something wrong?
Click to expand...

What you're doing wrong is not providing complete details of how this isn't
working. So I'm going to guess. First, if your worksheet names contain spaces,
they need to be enclosed in single quotes. Always best to use single quotes
regardless. So change one [also replacing the pointless CONCATENATE function
with the work-alike but much better concatenate operator, &, and making both ROW
and COLUMN refer to EXACTLY the same range],

=INDIRECT("'"&SheetName&"'!"&ADDRESS(ROW(BA7:BD25),COLUMN(BA7:BD25))))

But this still won't work because the result is an array of range references,
which is something Excel can't resolve as the final result to be stored in a
range. So change two (and I realize this seems nonsensical given what's been
written before in this thread),

=CELL("Contents",INDIRECT("'"&SheetName&"'!"&ADDRESS(ROW(BA7:BD25),
COLUMN(BA7:BD25)))))

Wrapping an array of range references inside CELL("Contents",...) converts it to
an array of the values of the top-left cells in each of the ranges in the array,
which is something Excel *CAN* handle as range values.

It's these completely opaque semantic shenanigans that make Excel so much fun to
use. If it did things sensibly, it'd be so much duller.
 
Harlan said:
Wrapping an array of range references inside CELL("Contents",...
converts it to
an array of the values of the top-left cells in each of the ranges i
the array,
which is something Excel *CAN* handle as range values.

It's these completely opaque semantic shenanigans that make Excel s
much fun to
use. If it did things sensibly, it'd be so much duller.
[/B]
Click to expand...

Thanks, Harlan!

After making both changes you requested, my formulas work perfectly
You really know your stuff. Thanks so much for your help
 
Back
Top