software

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 quot;stuckquot; 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: www.excelforum.com/member.php...oamp;userid=31042
View this thread: www.excelforum.com/showthread...hreadid=507154It sounds like you could use =vlookup().

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

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

--

Dave Peterson


Thanks Dave,

Problem resolved --
Alec H
------------------------------------------------------------------------
Alec H's Profile: www.excelforum.com/member.php...oamp;userid=31042
View this thread: www.excelforum.com/showthread...hreadid=507154
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 quot;resultsquot; 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: www.excelforum.com/member.php...oamp;userid=31042
View this thread: www.excelforum.com/showthread...hreadid=507154#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(quot;'quot;amp;D$1amp;quot;'!a:equot;),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:groups.google.co.uk/group/mic...28f1ba868980a8

or

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

--

Dave Peterson


Thanks again Dave.....--
Alec H
------------------------------------------------------------------------
Alec H's Profile: www.excelforum.com/member.php...oamp;userid=31042
View this thread: www.excelforum.com/showthread...hreadid=507154