Auto filling cells across mutliple sheets

Hi,

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

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

Help please.....

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 wrote:
>
>
>
> Hi,
>
> I am creating a customer database in a Excel 2000 pro workbook. It
> comprises
> of multiple worksheets each containing a different companies details
> (branc
> hes, phone numbers, etc). I have ensured that in all sheets column A is
> a nu
> meric field and that each row has a unique number in this column
> (unique acr
> oss all sheets).
>
> Where I have got "stuck" is that I want to create a additional
> worksheet in
> which I can manually designate a number in a cell (column A) and excel
> will automatical
> ly fill the remaining cells in that row from whichever sheet the number
> corr
> esponds.
>
> Help please.....
>
> --
> Alec H
> ------------------------------------------------------------------------
> Alec H's Profile: http://www.excelforum.com/member.php...o&userid=31042
> View this thread: http://www.excelforum.com/showthread...hreadid=507154

--

Dave Peterson

Thanks Dave,

Problem resolved

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

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



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

#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/mic...28f1ba868980a8

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 wrote:
>
> Right, I now have vlookup working on the workbook (thanks Dave), however
> this has now created 2 further problems for me......
>
> 1. How do I get vlookup to check several worksheets within a workbook
> for results, at the moment I can only get the table array to look at 1
> worksheet per vlookup cell. ie each of my customers is on a different
> worksheet.
>
> 2. How do I configure my "results" sheet to list multiple results for a
> search? ie Column 1 contains a unique number for each customer, column 2
> contains a numeric area code (eg area 23 = SW England) and there may be
> several customers in that area. I want to be able to search by either
> customer number (vlookup seems to do this ok) OR area number (multiple
> possible results)..........
>
>
>
> --
> Alec H
> ------------------------------------------------------------------------
> Alec H's Profile: http://www.excelforum.com/member.php...o&userid=31042
> View this thread: http://www.excelforum.com/showthread...hreadid=507154

--

Dave Peterson

Thanks again Dave.....