Auto filling cells across mutliple sheets
 

:confused:

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

Auto filling cells across mutliple sheets
 

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:

:confused:

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

Auto filling cells across mutliple sheets
 

Thanks Dave,

Problem resolved :)


--
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

Auto filling cells across mutliple sheets
 

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)..........

:eek:


--
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

Auto filling cells across mutliple sheets
 

#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)..........

:eek:

--
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

Auto filling cells across mutliple sheets
 

Thanks again Dave.....:)


--
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