EXCEL LOOK UP 2

[maeling]

Hello,
I have two workbooks in excel.
workbook one is blank and will be filled in by an end user.

Workbook two is a data source of;
column 1 column 2
manufacturer model
sony vaio
hp prosario

What I am trying to do is - when an end users types Sony into workbook 1 column 1 it enters the data from workbook 2 column 2 into workbook 1 column 2. Does this make sense ? Is it achievable in Excel ?

 

[xlbo]

what are you actually trying to achieve ??
which of the 3 options should be returned next to "sony" ? and how would this be decided ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[maeling]

The ideal solution for this would be; the end user types in sony and all the multple options are displayed in a list to select from. Once the list is visible, the end user would know which one to select by using a mouse click or keystroke. The industry I am refering to has many names for one machine e.g phone, telephone, dog & bone! It is a requirement that the same description is always used as the input will form part of a database. The difficulty is when a manafacturer like sony produces more than one piece of equipment e.g laptop, telephone etc.
Thanks

 

[xlbo]

would possibly be do-able via VBA and queries but even then it would be not too easy.

I know you have said Access is not an option but I would suggest that you go back to your boss and stress that this will not be easy to achieve in Excel (unless you have someone there who knows VBA) whereas in Access, this would be a piece of cake...



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KLewisBPM]

I have to agree with Geoff, i can get the list box showing all of the data but i cannot get it to filter that data by what is entered in column 1.

Perhaps you could store your data in Access and still use an excel front end?? Using microsoft query to update each time column 1 is filled in.



Kind Regards

Kelley Lewis

 

[maeling]

OK thanks for all your help. The reason access isn't an option is because this document is used on customer sites. The customers do not always have access. Thanks again.

 

[KLewisBPM]

If you build an Access DB could you install the Access runtime files? I don't know much about runtime files but i've heard you can install them without the need for having Access??

Anyone else know about that?

Kind Regards

Kelley Lewis

 

[maeling]

This was available in the past however I'm not sure if runtime licenses exist anymore. I heard of a site having to purchase several hundred Access licenses to accomodate a runtime process they had.

 

[xlbo]

If you know any VBA, it is certainly do-able

You would need to code on the woksheet_CHANGE event

The code would need to run a SQL query over the 2nd worksheet and thn create a data>validation list in the cell adjacent to the one that was changed, based on the query results (recordset)

User then picks from list

Problem is that if you are not familiar with VBA, this will be tricky to implement and more to the point, hard for you to maintain effectively...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

Could you re-design workbook2 to have a single column for each manufacturer? Then you could easily create named range for each manufacturer (Insert,Name, Define..) and a named range for your Manufacturers (the column headings).

Data validation would then enable you to use the Manufacturers range as the source for data validation list.

So when the user uses this he selects, say, Sony.
Now, it must be possible to create a Worksheet change event to make the named range "Sony" the source for a second data validation giving your list of possible products.

..............................................
If you can't change the structure of Workbook2 then you could achieve similar using advance filters but I think this would be more messy. Both workbooks would need to be open and you would use events in workbook1 to trigger code that caused advanced filter to be applied in workbook2.

In workbook2 you create a new sheet and use:
Data, advance filter, filter to new location, unique items only

To get your manufacturers you just need a single column heading "manufacturer" as the "CopyTo" range and no criteria)
This will give you a unique list of manufacturers to use in your first dropdown - maybe a datavalidation list. If the masterdata gets updated regularly then you will want to consider dynamic rangename to reference this list and maybe a BeforeSAve event to trigger a repeat of the advance filter

To get your list of possible products for this manufacturer you need to have a further sheet in workbook2. This should have a 2 cell criteria range with a fieldname of "manufacturer" and a value which you set as equal to the results in Workbook1 (by a simple formula or, moe likely, in your code). The "CopyTo range" just needs to contain the heading "product". Create a dynamic named range "MyManufacturers" to make the results easy to reference.

Use an event in workbook1 to trap a change in manufacturer and then trigger this second advanced filter in workbook2. You will definitely need the results range to use a dynamic named range "MyProducts" as the number of products will vary each time.

In workbook1 you just use data validation, list and refer to the relevant named ranges "MyManufacturers" for cell A3 and "MyProducts" for cell B3. [Of course these are actually in Workbook2 but to avoid those messy references develop the solution, without code within workbook2. Then move the sheet into Workbook1, then develop the code to automate the solution.]

I do hope that this thread can give you an idea about a possible solution.

Thanks,

Gavin

 

[Gavona]

Alight error in my post above:
If anyone follows the second option in my post be aware that you should NOT use dynamic range names in workbook2 as these will not evaluate when they are referenced by workbook1 (they might if workbook2 is open but I would need to test that).

The simple alternate solution is, whenever you have refreshed the advance filter results to use some code in the form
range(MyProducts).CurrentRegion.Name = "MyProducts".

Also with the second option:
I said:
"Use an event in workbook1 to trap a change in manufacturer and then trigger this second advanced filter in workbook2. You will definitely need the results range to use a dynamic named range "MyProducts" as the number of products will vary each time."
It would be much easier and I suspect more efficient, to have code that ensures that all the product lists for each manufacturer exist permanently in workbook2. To achieve this you would loop through the manufacturer list executing an advanced filter and saving the results as a range with the name of the manufacturer. Workbook 1 would then work as for the first option with no need to have code that opens and manipulates workbook2.

Thanks,

Gavin

 

[shetlandbob]

xlbo

Out of curiosity how would you get a SQL command to look up data in the second excel workbook (I know VBA, v little in SQL)

Thanks

Robert Cumming

http://www.consultantengineer.co.uk

 

[xlbo]

Easiest way is to record yourself doing it but basically, you use the "Excel Files" ODBC connection. The file being read FROM must have a RANGE NAME that covers the data being retrieved - the RANGE NAME in conjunction with the FILE NAME is then used as the TABLE name in the FROM clause of the SQL. eg:

"ODBC;DSN=Excel Files;DBQ=[b]C:\Test.xls[/b];DefaultDir=C:;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" 
        .CommandText = "SELECT test_name.FieldName, test_name.FieldValue FROM [b]`C:\Test`.test_name test_name[/b]"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Just a thought, but if WB2 has the Data, and WB1 wants what sounds like Dependent Lists via Data Validation, then why not have a helper sheet in WB1 link directly to WB2, and have it link way down further than it needs to cater for additions to the list (I know it's kludgy, but it's an option).

That gives you the raw data from WB2 inside WB1.

Now on that same helper sheet you can use formulas to give you a unique list of items from that list with no gaps in the unique list.

Using formulas again you can then set up dynamic names to reference the lists of unique items, and then once you have that you can simply use Dependent Lists via Data Validation for getting you what you need can't you?


Direct links to the workbook would be straight links, eg

='C:\Documents and Settings\Me\My Documents\[Test Me Source.xls]Sheet1'!A1

This brings a list of data into your helper sheet that looks like this:-

Col A
1 aaa
2 b
3 c
4 d
5 e
6 s
7 d
8 a
9 s
10 e
11 d
12 w
13 s
14 d
15 f
16 v
17 d
18 f
19 e
20 s
21 d
22 f
23 g
24 h
25 n
26 b
27 g
28 0 Nothing to bring across so just returns 0
29 0 !! !! !! !!
30 0 !! !! !! !!
31 0 !! !! !! !!


Helper column (Col B) next to it in B, with a 1 in B2, and then in B3 put this and copy down:-

=IF(COUNTIF(A$2:A3,A3)=1,MAX($B$2:B2)+1,"")

This gives you something like this:-

aaa aaa1
b 1
c 2
d 3
e 4
s 5
d
a 6
s
e
d
w 7
s
d
f 8
v 9
d

Then use a final helper column to give you the unique list, eg in Col C in C2 put the following formula and copy down:-

=IF(ISNA(INDEX(A:A,MATCH(ROW()-1,B:B,0))),"",INDEX(A:A,MATCH(ROW()-1,B:B,0)))

This gives you the following in Col C:-

aaa2
b
c
d
e
s
a
w
f
v
g
h
n
0

Now just set up a named formula to give you the data from Col C dynamically, and then use the following instructions from Debra Dalgleish's website for Dependent lists:-

http://www.contextures.com/xlDataVal02.html

Regards
Ken.............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Gavona]

Ken,
Really clear and I now know how to produce unique lists via formulae. Thanks.
I am however intrigued. What is the advantage of having this helper sheet in wb1 rather than wb2? Instead of having multiple refeences from 1 wb to the other you could get away with a single reference in the data validation rules bit.

I think also that your answer doesn't quite address how to get the lists for the second dropdown - the models for that manufacturer. It got me thinking though. Why not have a pivot table in wb1. Could look something like this, and set the options to 'refresh on open'. Pivot table would not need to recalculate every time a cell changed in wb1 etc. Challenge then is to produce your named ranges - probably with an on_open event.

Count of model
manufacturer model Total
hp prosario 1
sony ps1 1
ps2 1
vaio 1
Grand Total 4



Thanks,

Gavin

 

[KenWright]

Was trying to get away without using code if possible. Data Validation requires the target to be in the same workbook (I think), and in fact can only be referenced from another sheet by means of a named range.

As for the second lists, that is what the link i gave does. Debra Dalgleish explains how to make a completely dependent list via data Validation, such that when you choose say a manufacturer from the first list using DV, you then have the option of choosing just the products for that manufacturer from a second DV list. I didn't detail any of that because Debra has a comprehensive explanation with pictures on her site - Is that what you meant?

As for the PT - hadn't thought about that, and to be honest off the top of my head have no idea if it pulls OK from a closed workbook? I know the formulas do so that is why i went that way, but the PT might be a better option.

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Gavona]

Thanks again Ken. That makes sense - and I didn't spot that bit on Debra's site.
Pivots can refresh from a closed workbook - not too slow either.





Thanks,

Gavin