Excel Dynamic Range help 3

[bam720]

I have a named cell called "Retail), and I am trying to create a dynamic range based off of this cell, called RetailRange. Here's what I have so far:

=OFFSET(Retail,0,1,COUNTA(OFFSET(Retail,0,1,)),6)

This copies the first row straight across but doesn't count downward any cells. I am pretty sure that I am missing the destination part of the COUNTA argument, but I can figure out what to put in to make it work. For intensive purposes I am trying to copy cell B46 to G46 and whatever length it needs to be down. Retail refers to A46. Its named becuase is may not be always be in cell A46 becuase there are other dynamic ranges above it. Thanks for the help.

 

[AnotherHiggins]

Try this:
[tab]=OFFSET(Retail,0,1,COUNTA(B:B),6)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[bam720]

That counts all the rows from the start of my spread sheet in column B and then selects it below. I already have those cells accounted for. Basically it counted too many rows that were already accounted for. I want it to start at cell B46 and count down from there

 

[AnotherHiggins]

Ah.

=OFFSET(Retail,0,1,COUNTA(OFFSET(Retail,0,1,1,1):$B$65536),6)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Way to go,John!

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[bam720]

that was perfect

 

[AnotherHiggins]

Glad to help, bam720.

And thanks, Skip - praise from you is always especially gratifying. It's good to be back.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

As another method (also because I just don't like OFFSET/COUNTA)...

Name ranges:

BigStr
Refers to: =Rept("z",255)

BigNum
Refers to: =9.999999999E+307

MyRange
Refers to: Sheet1!$B$46:Index(Sheet1!$B:$G,Match(BigNum,Sheet1!$B:$B),6)

In the above, exchange BigNum and BigStr if the column contains numerics or strings (respectively) and change the $B:$B depending on what column houses the last value you wish to locate.

NB: This will not be a good [dynamic] range for Pivot Tables.

-----------
Regards,
Zack Barresse