In a dynamic range name, I'd like to to refer to the entire column that
another named range is in. So in the name "Refers To" below, instead of
"Sheet1!$E:$E" I'd like to be able to say "the column in which the cell
Amount is":

=OFFSET(Amount,1,0):OFFSET(Amount,COUNTA(Sheet1!$E :$E)-1,0)

Thanks,

Doug

What exactly do you want for your reference?
How many rows up or down from the reference do you want to be?
How many columns left or right of the reference do you want to be?
How many rows do you want to include in the range?
How many columns do you want to include in the range?

"Doug Glancy" wrote:

In a dynamic range name, I'd like to to refer to the entire column that
another named range is in. So in the name "Refers To" below, instead of
"Sheet1!$E:$E" I'd like to be able to say "the column in which the cell
Amount is":

=OFFSET(Amount,1,0):OFFSET(Amount,COUNTA(Sheet1!$E :$E)-1,0)

Thanks,

Doug

Barb,

I want to refer to the entire column in which the cell named "Amount"
resides. This would translate to "E:E" in this case. Just one column, the
entire thing.

Thanks,

Doug

"Barb Reinhardt" wrote in message
...
What exactly do you want for your reference?
How many rows up or down from the reference do you want to be?
How many columns left or right of the reference do you want to be?
How many rows do you want to include in the range?
How many columns do you want to include in the range?

"Doug Glancy" wrote:

In a dynamic range name, I'd like to to refer to the entire column that
another named range is in. So in the name "Refers To" below, instead of
"Sheet1!$E:$E" I'd like to be able to say "the column in which the cell
Amount is":

=OFFSET(Amount,1,0):OFFSET(Amount,COUNTA(Sheet1!$E :$E)-1,0)

Thanks,

Doug

Roger, can we use EVALUATE ( ) in some fashion here?

If I am totally off, forgive me and have a good laugh.

Epinn

"Doug Glancy" wrote in message ...
In a dynamic range name, I'd like to to refer to the entire column that
another named range is in. So in the name "Refers To" below, instead of
"Sheet1!$E:$E" I'd like to be able to say "the column in which the cell
Amount is":

=OFFSET(Amount,1,0):OFFSET(Amount,COUNTA(Sheet1!$E :$E)-1,0)

Thanks,

Doug

Bit klunky, but ...

=OFFSET(A1,0,MAX(IF(C1:E65535="Amount Is:",COLUMN(C1:E65535)))-1,65535,1)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Doug Glancy" wrote in message
...
Barb,

I want to refer to the entire column in which the cell named "Amount"
resides. This would translate to "E:E" in this case. Just one column,
the
entire thing.

Thanks,

Doug

"Barb Reinhardt" wrote in
message
...
What exactly do you want for your reference?
How many rows up or down from the reference do you want to be?
How many columns left or right of the reference do you want to be?
How many rows do you want to include in the range?
How many columns do you want to include in the range?

"Doug Glancy" wrote:

In a dynamic range name, I'd like to to refer to the entire column that
another named range is in. So in the name "Refers To" below, instead
of
"Sheet1!$E:$E" I'd like to be able to say "the column in which the cell
Amount is":

=OFFSET(Amount,1,0):OFFSET(Amount,COUNTA(Sheet1!$E :$E)-1,0)

Thanks,

Doug

Hi Doug

Maybe
=INDEX(Amount,1,MATCH("Amount",Sheet1!$1:$!,0)):In dex(Amount,65535,MATCH("Amount",Sheet1!$1:$!,0)):

--
Regards

Roger Govier


"Doug Glancy" wrote in message
...
In a dynamic range name, I'd like to to refer to the entire column
that another named range is in. So in the name "Refers To" below,
instead of "Sheet1!$E:$E" I'd like to be able to say "the column in
which the cell Amount is":

=OFFSET(Amount,1,0):OFFSET(Amount,COUNTA(Sheet1!$E :$E)-1,0)

Thanks,

Doug

Doug Glancy wrote...
In a dynamic range name, I'd like to to refer to the entire column that
another named range is in. So in the name "Refers To" below, instead of
"Sheet1!$E:$E" I'd like to be able to say "the column in which the cell
Amount is":

=OFFSET(Amount,1,0):OFFSET(Amount,COUNTA(Sheet1!$ E:$E)-1,0)

=INDEX($1:$65536,0,COLUMN(INDEX(Amount,1,1,1)))

=ABSREF("C", Amount)

--
Regards,
Luc.

"Festina Lente"


"Doug Glancy" wrote:

In a dynamic range name, I'd like to to refer to the entire column that
another named range is in. So in the name "Refers To" below, instead of
"Sheet1!$E:$E" I'd like to be able to say "the column in which the cell
Amount is":

=OFFSET(Amount,1,0):OFFSET(Amount,COUNTA(Sheet1!$E :$E)-1,0)

Thanks,

Doug

Hi Doug

Reading Harlan's post, made me go back to my own posting where I
realised I had made some errors

Firstly, a number of typo's in my posting where I typed $! instead of
$1, and a surplus colon at the end
Should have been
=INDEX(Amount,1,MATCH("Amount",Sheet1!$1:$1,0)):In dex(Amount,65536,MATCH("Amount",Sheet1!$1:$1,0))

Secondly, I had misread your post and assumed that Amount was a named
range, as well as a column heading and that Amount as a named range was
perhaps $A:$Z. With such a named range, the amended formula will work

If there is no named range called Amount, and you need to reference the
whole sheet just looking for the column headed with the word Amount,
then try the following
=INDEX($1:$65536,1,MATCH("Amount",$1:$1,0)):Index( $1:$65536,65536,MATCH("Amount",$1:$1,0))

I could not get Harlan's suggestion to work, but modifying it to the
following
=INDEX(Sheet4!$1:$65536,0,MATCH("amount",Sheet4!$1 :$1,0))
did work, and is a far better solution than my suggestion.

I am indebted to Harlan for making me "think" once again.

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi Doug

Maybe
=INDEX(Amount,1,MATCH("Amount",Sheet1!$1:$!,0)):In dex(Amount,65535,MATCH("Amount",Sheet1!$1:$!,0)):

--
Regards

Roger Govier


"Doug Glancy" wrote in message
...
In a dynamic range name, I'd like to to refer to the entire column
that another named range is in. So in the name "Refers To" below,
instead of "Sheet1!$E:$E" I'd like to be able to say "the column in
which the cell Amount is":

=OFFSET(Amount,1,0):OFFSET(Amount,COUNTA(Sheet1!$E :$E)-1,0)

Thanks,

Doug

Roger Govier wrote...
....
I could not get Harlan's suggestion to work, but modifying it to the
....

If Amount is a named range, then my formula works. For example, if I
name E4:F6 Amount, the formula

=MID(CELL("Address",(A1,INDEX($1:$65536,0,COLUMN(I NDEX(Amount,1,1,1))))),6,256)

correctly returns "$E:$E".

Harlan,

Thanks to you and everybody else who responded. I realize that my original
post was not as clear as I thought, but you are correct "Amount" is a named
range. And your solution works.

Does that mean that there is no way to do it without using 65536, i.e., for
Excel 12 (not that I'm planning on upgrading anytime soon).

This is more pickiness than anything at this point - I just like to try to
make my range names as foolproof and readable as possible. I suppose the
original was more foolproof, in that "E:E" would adjust to any changes I can
think of, whereas yours is more readable, because it maintains the reference
to "Amount."

hth,

Doug

"Harlan Grove" wrote in message
ups.com...
Roger Govier wrote...
...
I could not get Harlan's suggestion to work, but modifying it to the
...

If Amount is a named range, then my formula works. For example, if I
name E4:F6 Amount, the formula

=MID(CELL("Address",(A1,INDEX($1:$65536,0,COLUMN(I NDEX(Amount,1,1,1))))),6,256)

correctly returns "$E:$E".