Multiple Source Data for what-if-funtion?

  • Thread starter Thread starter w/*me
  • Start date Start date
W

w/*me

Hi, i am trying to create a formula that has the What If Function
(maybe?). So I am strugling with trying to link a single cell to import
data from another excel spreedsheet file (used as it's "database")
stored in the same folder. If a user types a "tool id number" into cell
D31 it will fill in data to the other cells for that sheet from the
database.

example, hope it makes sense:
cell D31 from book1.xls is the only unlocked cell that the 'givin' user
has access to and types the tool# there.
column A, cells A2 through A51 from book2.xls (database) are the "=if"
cells. ie: tool#'s 1-50.
Rows B2 through B11 for A2 are the tool descriptions (data that needs
to be exported to cells of book1.xls)

is this workable with 50+ tool variables?

werking dealie: 'Looky' (http://q3.fragsrus.com/boris/book.jpg)

thx guys
 
One way is to use vlookup on
the "Database" table in Book2.xls, Sheet2

With Book2.xls open

and assuming your database is in A2:N1000 in Sheet2, Book2.xls

In Book1.xls, Sheet1
--------------------------
Try these sample vlookup formulae below
to put in some target cells:

E31: =VLOOKUP($D$31,[Book2.xls]Sheet2!$A$2:$N$1000,10,FALSE)
As E31 is for NUM FLTS which is column #10 in the table - 3rd param = 10

G29: =VLOOKUP($D$31,[Book2.xls]Sheet2!$A$2:$N$1000,8,FALSE)
As G29 is for STICKOUT which is column #8 in the table - 3rd param = 8

I29: =VLOOKUP($D$31,[Book2.xls]Sheet2!$A$2:$N$1000,9,FALSE)
As I29 is for GAGE LEN which is column #9 in the table - 3rd param = 9

And so on, just copy the vlookup and
change the 3rd param of the vlookup
to suit the rest of the 50+? target cells / column value to extract
then format each target cell to taste, if desired/necessary

To prevent problems, would suggest that
merge cells be avoided, for example what you have
for TAPE NO's USED ON in H31.

Instead use
format > cells > alignment tab > center across selection (under horizontal)
for the same desired effect.

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email
 
No way, thanks Max, works like a charm! :) . I have the Microsoft Pres
Running Excel2k, but got lost on what section to even start in.

how about a text file output for stickout and gage length (colums H
I) for CATIA interpitation, a basic macro?

thx agai
 
you're welcome!

and as for your follow-on Q...
how about a text file output for stickout and gage length (colums H &
I) for CATIA interpitation, a basic macro?
Click to expand...

afraid I've reached my level of incompetency <g>

suggest you put in a post in
microsoft.public.excel.programming

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email
-------------------------------------------
 
Back
Top