dynamic range name

[Doug Glancy]

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

 

[Guest]

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]

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

 

[Epinn]

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

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

Epinn

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

 

[Bob Phillips]

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)

 

[Roger Govier]

Hi Doug

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

 

[Harlan Grove]

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)))

 

[Guest]

=ABSREF("C", Amount)

 

[Roger Govier]

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)):Index(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.

 

[Harlan Grove]

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(INDEX(Amount,1,1,1))))),6,256)

correctly returns "$E:$E".

 

[Doug Glancy]

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