dynamic range name

  • Thread starter Thread starter Doug Glancy
  • Start date Start date
D

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
 
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?
 
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
 
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
 
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)
 
Hi Doug

Maybe
=INDEX(Amount,1,MATCH("Amount",Sheet1!$1:$!,0)):Index(Amount,65535,MATCH("Amount",Sheet1!$1:$!,0)):
 
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)
Click to expand...

=INDEX($1:$65536,0,COLUMN(INDEX(Amount,1,1,1)))
 
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.
 
Roger Govier wrote...
....
I could not get Harlan's suggestion to work, but modifying it to the
Click to expand...
....

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".
 
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
 
Back
Top