Dynamic Range and lookup

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I have a list on a worksheet with fields in A1 to A3 and values in B1 to D3.
Each day i'd like to add a new row that would go straight down page.

What function would be best to lookup a value from the list? Even more
important, how can i find the last record in the list without changing the
range that a formula would need to find the last record?

thanks for any input.
 
Scott,

=VLOOKUP(F12,OFFSET(A1,0,0,COUNTA(A:A),4),2)

F12 contains the value being looked up. There can be no holes in column A,
or it will retrieve a value from the wrong row.
 
Select the entire range A1 to D4. The empty, extra row is important!
Click on Insert>Name>Define and give it a name like 'data'. As you
add data, always add immediately after the last row containing data
with no empty rows (this will insert before the blank row which is the
end of your range, and automatically extend your range by one row.)
This is the beauty of naming ranges.

The function you will use is vlookup. Say cell F8 contains the value
you want to lookup in column A to find in column B. In F9 (or any
cell) type:

=vlookup(F8,data,2)

The vlookup function works like this: vlookup(the value you want to
find in the FIRST column of the range, the range, the column in the
range of the corresponding value you want to return).

The same example as above to return the value from column C is:

=vlookup(F8,data,3)

Whatever is in cell F8 is what it will try to find in column A. The
lookup values must be in the left-most column. These formulae can be
on any sheet; the range 'data' always refers to the same table.

Got it?

It's a great way to use Excel - put the data in a plain table on one
sheet and make a 'pretty' version on another.

If you need more help, keep posting in this same thread. We will make
it work for you!

-Mike


I have a list on a worksheet with fields in A1 to A3 and values in B1 to D3.
Each day i'd like to add a new row that would go straight down page.

What function would be best to lookup a value from the list? Even more
important, how can i find the last record in the list without changing the
range that a formula would need to find the last record?

thanks for any input.
Click to expand...

Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers
 
Select the entire range A1 to D4. The empty, extra row is important!
Click on Insert>Name>Define and give it a name like 'data'. As you
add data, always insert a row (or a group of cells) for the new data
immediately after the last row containing data. This will insert
before the blank row which is the end of your range, and automatically
extend your range by one row. This is the beauty of naming ranges.

The function you will use is vlookup. Say cell F8 contains the value
you want to lookup in column A to find in column B. In F9 (or any
cell) type:

=vlookup(F8,data,2)

The vlookup function works like this: vlookup(the value you want to
find in the FIRST column of the range, the range, the column in the
range of the corresponding value you want to return).

The same example as above to return the value from column C is:

=vlookup(F8,data,3)

Whatever is in cell F8 is what it will try to find in column A. The
lookup values must be in the left-most column. These formulae can be
on any sheet; the range 'data' always refers to the same table.

Got it?

It's a great way to use Excel - put the data in a plain table on one
sheet and make a 'pretty' version on another.

If you need more help, keep posting in this same thread. We will make
it work for you!

-Mike


I have a list on a worksheet with fields in A1 to A3 and values in B1 to D3.
Each day i'd like to add a new row that would go straight down page.

What function would be best to lookup a value from the list? Even more
important, how can i find the last record in the list without changing the
range that a formula would need to find the last record?

thanks for any input.
Click to expand...

Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers
 
could you give me more details on exactly what
=VLOOKUP(F12,OFFSET(A1,0,0,COUNTA(A:A),4),2)
is doing? i understand vlookup, but not the extra formulas.
 
Hi

Define a dynamic named range (Insert.Name.Define) p.e. MyTable
MyTable=OFFSET(SheetName!$A$1,,,COUNTIF(SheetName!$A:$A,"<>"),4)
The formula creates a range with A1 as leftmost uppe cell, 4 columns wide,
and as much rows, as much nonempty cells are in column A, deep. There
mustn't be any gaps in column A, or any other data except those belonging to
table. When your table has header row, then dynamic range is defined as
MyTable=OFFSET(SheetName!$A$2,,,COUNTIF(SheetName!$A:$A,"<>")-1,4)


Formulas to retrieve values (with key value p.e. in cell X1 on any sheet)
are now:
=VLOOKUP(X1,MyTable,3,FALSE)
=VLOOKUP(X1,MyTable,4,FALSE)

Dynamic named ranges, describer as above has another uses too. P.e. you can
format the cell with key value for lookup formula (X1) as dropdown list.
Define another dynamic range
MyList=OFFSET(SheetName!$A$1,,,COUNTIF(SheetName!$A:$A,"<>"),1)
or
MyList=OFFSET(SheetName!$A$2,,,COUNTIF(SheetName!$A:$A,"<>")-1,1)
Select the cell. From menu select Data.Validation.List, and into source
field enter the formula
=MyList
 
where do i put the code below?

MyTable=OFFSET(SheetName!$A$2,,,COUNTIF(SheetName!$A:$A,"<>")-1,4)
 
Hi Scott

as Avri wrote: Goto the menu "Insert - Name -Define" chosse MyTable as
name and enter the formula

HTH
Frank
 
Hi

Range name (MyTable) into field 'Names in workbook'
The formula (equals sign included) into field 'Refers to'


Arvi Laanemets
 
Scott,

The OFFSET(A1,0,0,COUNTA(A:A),4) part is a dynamic range. It's not defined
as a name, as suggested in other posts. It's just put directly in the
vlookup for the second argument, which is the table to look in.
 
Back
Top