Extracting Data easily from database Excel 2003

[OzzyJim]

Hi All,
Have a large database (approx 30 columns, hundreds of lines) and would like
to run a quick report that pulls important related information from the
databse via a key descriptor i.e if I have simple data like

Number Description Cost Delivery Qty
1 Egg 25 Yes 300
2 Chicken 30 No 500

So by choosing a line number, the remaining line information returns,
Description, cost etc returns into appropriate label fileds as below

Number 2 (line number chosen to view)
Description Chicken (these fields populate automatically)
Cost 30 (these fields populate automatically)
Delivery no (these fields populate automatically)
Qty 500 (these fields populate automatically)

Any ideas will be appreciated
Cheers
OzzyJim

 

[L. Howard Kittle]

Hi Ozzy Jim,

You can do this with an array entered vlookup. Try this:

Select four cells on the same row and while still selected enter this
formula in the active cell.

=VLOOKUP(H1,B1:F2,{2,3,4,5},0)

Now commit with Ctrl+Shift+Enter.

Where H1 is the Number to lookup, (and can be a data valadation drop down
list of 1 to 50, etc.)
Where Description, Cost, Delivery, Qty is a range of B1:F2, expanded to suit
your data.

If your data table changes you will need to select the four cells containing
the formula and make your changes and again use the array enter method.

You also can do this using a more simple vlookup something like this in each
cell for for the four bits of info to be returned, assuming the same lookup
range as above. These are entered by a simple ENTER only.

=VLOOKUP(H1,B1:F2,2,0)
=VLOOKUP(H1,B1:F2,3,0)
=VLOOKUP(H1,B1:F2,4,0)
=VLOOKUP(H1,B1:F2,5,0)

HTH
Regards,
Howard