Auto filling cells across mutliple sheets

[Alec H]

Hi,

I am creating a customer database in a Excel 2000 pro workbook. I
comprises
of multiple worksheets each containing a different companies detail
(branc
hes, phone numbers, etc). I have ensured that in all sheets column A i
a nu
meric field and that each row has a unique number in this colum
(unique acr
oss all sheets).

Where I have got "stuck" is that I want to create a additiona
worksheet in
which I can manually designate a number in a cell (column A) and exce
will automatical
ly fill the remaining cells in that row from whichever sheet the numbe
corr
esponds.

Help please....

 

[Dave Peterson]

It sounds like you could use =vlookup().

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

 

[Alec H]

Thanks Dave,

Problem resolved

 

[Alec H]

Right, I now have vlookup working on the workbook (thanks Dave), howeve
this has now created 2 further problems for me......

1. How do I get vlookup to check several worksheets within a workboo
for results, at the moment I can only get the table array to look at
worksheet per vlookup cell. ie each of my customers is on a differen
worksheet.



2. How do I configure my "results" sheet to list multiple results for
search? ie Column 1 contains a unique number for each customer, column
contains a numeric area code (eg area 23 = SW England) and there may b
several customers in that area. I want to be able to search by eithe
customer number (vlookup seems to do this ok) OR area number (multipl
possible results)..........

:eek

 

[Dave Peterson]

#1. Without knowing how your worksheets are set up, you'll need separate
formulas that point to the other sheets.

If you have a cell that contains the worksheet name/customer name, you can embed
that into the =vlookup() formula using =indirect().

Say you have sheet names in row B1:E1 and you want to retrieve the value from
the sheet name in D1, you could use a formula like:

=VLOOKUP(A2,INDIRECT("'"&D$1&"'!a:e"),3,FALSE)

#2. =vlookup() will return the first match that it finds. You may be able to
use data|filter|autofilter to see the data when you have multiple results to
find.

If I want all the results to show up in a single cell, I use a user defined
function:


http://groups.google.co.uk/group/mi...author:peterson&rnum=1&hl=en#db28f1ba868980a8

or

http://snipurl.com/i7q1

The values come back separated by commas.

(There are instructions in that link that tell how to use it.)

 

[Alec H]

Thanks again Dave.....