Dynamic Ranges

[PAL]

Hello,

I am working with a spreadsheet with a many rows and columns (it is an
export from a database). Overtime the number of rows will grow. I would
like to create a dynamic range. To do this I highlighted the page and
defined a name like this:

=OFFSET(Export!$A$1,0,0,COUNTA(Export!$A:$A),1).

Is this correct? Or should I only highlight the first column?

My goal is ultimately to use the data from this page (which will be
refreshed periodically) on another worksheet that will include VLOOKUP and
some simple calculations.

To begin I need the first column from the Export Page on the next worksheet.
How do I do this. My thought was to reference the Export Cells. But not
sure how to make the column grow without referencing again.

Please advise.

 

[Teethless mama]

=OFFSET(Export!$A$1,0,0,COUNTA(Export!$A:$A),COUNTA(Export!$1:$1))

 

[PAL]

OK, Thanks.

Two questions then.

1. I assume on the next worksheet, I still always have to drag down to make
sure I have all the data? This is not done dynamically? I basically went in
A1 of the new worksheet "=Export" and dragged down.

2. VLOOKUP, doesn't seem to work. The defined name, "Export" doesn't show
up in the name dropdown list on the upper left. When I type in:

=VLOOKUP(1,Export,3,FALSE)

#Value! is returned.

Ideas

 

[T. Valko]

1:

Try this:

=INDEX(Export,ROWS(A$1:A1),1)

Copy down as needed.

2:

Hard to say why you're getting that error. There's nothing wrong with your
VLOOKUP formula.

The defined name, "Export" doesn't show up
in the name dropdown list on the upper left.

It won't because technically Export is a defined *formula*. The name box
only displays named static ranges. To see Export you'd have to go through
the menus Insert>Name>Define.