Am I pushing the limits of Excel 2000, or... 
Author Message
 Am I pushing the limits of Excel 2000, or...

do I gotta do some learnin'?

I'd like to have my primary workbook perform look-ups in rows of
two or three other workbooks.

For example, let's say I have a 15" rim and I'd like to have a
24K gold hub cap.  From my primary workbook, I'd like to perform
a lookup referencing/pulling information from another workbook.

So, when I click on a cell in the primary, a dialog box would
comeup asking what my search criteria are, I enter '15"' rim
and 'Gold'.

The look-up workbook would have:

Tiawan Mfg     Special Gold Hub Cap      $1,200      14"   Gold
Korea Mfg      Super Gold Hub Cap        $1,100      14"   Silver
Japan Mfg      Pretty Gold Hub Cap       $1,400      15"   Gold
Singapore Mfg  Nice Gold Hub Cap         $1,100      15"   Bronze

So, from my dialogue box, the look-up would return and place into
the primary workbooks' sheet: Japan Mfg, Pretty Gold Hub Cap, $1,400.

I checked in the help on Excel and wasted time trying tables and
external data, I've checked on usenet and didn't find anything clear.
Is this a job for VBA?  Should I learn that?

So, I figure I'd ask here.  Any help appreciated!

Thanks,
Dan



Sat, 04 Jun 2005 11:08:51 GMT  
 Am I pushing the limits of Excel 2000, or...
Hi Dan,

you could do most of what you are describing with standard worksheet
functions, but only if you did without the dialog box.

As you no doubt suspect, VBA would be the way to go if you want to
automate this solution with an nice interface to boot.

Would you like some help with the lookup functions, or are you going
to implement the full solution?

Regards, Thomas.

Quote:

> I'd like to have my primary workbook perform look-ups in rows of
> two or three other workbooks.

> For example, let's say I have a 15" rim and I'd like to have a
> 24K gold hub cap.  From my primary workbook, I'd like to perform
> a lookup referencing/pulling information from another workbook.

> So, when I click on a cell in the primary, a dialog box would
> comeup asking what my search criteria are, I enter '15"' rim
> and 'Gold'.



Sat, 04 Jun 2005 21:31:10 GMT  
 Am I pushing the limits of Excel 2000, or...
Try the vlookup and the hlookup features

However you are limited to critial from the first column in vlookup or the
first row in hlookup

      1 2 3
        Imput
      test1 21 Pete   test10 #N/A  =VLOOKUP(+G3,data1,2,FALSE)
      test2 22 Bob   test3 23

      test3 23 Sam  test5 25
      test4 24 Dave  sam #N/A    2nd column of table
      test5 25 Ray  TEST111 #N/A
      test6 26 Martin  test1 test1  =VLOOKUP(+G3,data1,1,FALSE) Column 1
values
      test7 27 Julie  test2 Bob   =VLOOKUP(+G3,data1,3,FALSE) column 3
values
      test8 28 Ray  test8 28
      test9 39 Ren
        test9 Ren correct =LOOKUP(+G11,data1) works if item is in table
        test10 Pete  FALSE
        test2 Bob  correct
        test12 Pete  FALSE


Quote:
> do I gotta do some learnin'?

> I'd like to have my primary workbook perform look-ups in rows of
> two or three other workbooks.

> For example, let's say I have a 15" rim and I'd like to have a
> 24K gold hub cap.  From my primary workbook, I'd like to perform
> a lookup referencing/pulling information from another workbook.

> So, when I click on a cell in the primary, a dialog box would
> comeup asking what my search criteria are, I enter '15"' rim
> and 'Gold'.

> The look-up workbook would have:

> Tiawan Mfg     Special Gold Hub Cap      $1,200      14"   Gold
> Korea Mfg      Super Gold Hub Cap        $1,100      14"   Silver
> Japan Mfg      Pretty Gold Hub Cap       $1,400      15"   Gold
> Singapore Mfg  Nice Gold Hub Cap         $1,100      15"   Bronze

> So, from my dialogue box, the look-up would return and place into
> the primary workbooks' sheet: Japan Mfg, Pretty Gold Hub Cap, $1,400.

> I checked in the help on Excel and wasted time trying tables and
> external data, I've checked on usenet and didn't find anything clear.
> Is this a job for VBA?  Should I learn that?

> So, I figure I'd ask here.  Any help appreciated!

> Thanks,
> Dan

begin 666 clip_image001.gif



`
end


Mon, 06 Jun 2005 03:23:46 GMT  
 Am I pushing the limits of Excel 2000, or...

Quote:

> do I gotta do some learnin'?

> I'd like to have my primary workbook perform look-ups in rows of
> two or three other workbooks.

> For example, let's say I have a 15" rim and I'd like to have a
> 24K gold hub cap.  From my primary workbook, I'd like to perform
> a lookup referencing/pulling information from another workbook.

> So, when I click on a cell in the primary, a dialog box would
> comeup asking what my search criteria are, I enter '15"' rim
> and 'Gold'.

> The look-up workbook would have:

> Tiawan Mfg     Special Gold Hub Cap      $1,200      14"   Gold
> Korea Mfg      Super Gold Hub Cap        $1,100      14"   Silver
> Japan Mfg      Pretty Gold Hub Cap       $1,400      15"   Gold
> Singapore Mfg  Nice Gold Hub Cap         $1,100      15"   Bronze

> So, from my dialogue box, the look-up would return and place into
> the primary workbooks' sheet: Japan Mfg, Pretty Gold Hub Cap, $1,400.

> I checked in the help on Excel and wasted time trying tables and
> external data, I've checked on usenet and didn't find anything clear.
> Is this a job for VBA?  Should I learn that?

> So, I figure I'd ask here.  Any help appreciated!

> Thanks,
> Dan

Dan,

Try this:

In your look-up workbook, insert a new Column A. In the rows with data
insert the function "=D1&E1" the result of which should be the text
string "14"Gold".

Now back on your lookup sheet, title your lookup cells -- rim size
(A1) and hub cap metal (A2) -- for example. Now, type 15" in B1 and
Gold in B2.

Now you can use a Vlookup to find the row in your lookup workbook and
it'll look something like this:
  =VLOOKUP(B1&B2,LookupSheet!A1:F4,2,FALSE) and return: Japan Mfg

Where:
 B1&B2 concatenate your search criteria,
 LookupSheet is the sheet where your lookup data is,
 2 is 1 column from where the string 15"Gold is found, and
 FALSE assures an EXACT match.

Good Luck



Tue, 07 Jun 2005 01:21:49 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Am I pushing the limits of Excel 2000, or...

2. Pushing Data from Excel to Access (2000)

3. Pushing the Limit

4. Access pushed to it's limits?

5. Pushing Publisher to the Limits

6. Push QT transition only pushes from top...HELP

7. 65,536 record import limit in Excel Windows 2000

8. How to overcome 1024 character limit in Excel 2000

9. Excel 2000 column limit

10. How do I delete the limit number of blank lines in Excel 2000


 
Powered by phpBB® Forum Software © phpBB Group