Simple Query in excel 1

[annettes]

Hi,

I have a workbook with few tabs "Product A", "Product B", Product C"..etc, in each worksheet, there're few columns, ie "Vendor", "Country", "Invoice#',"Discount", "Invoice date". The data will be updated daily.

I need to build a lookup function in another workbook.

Selection by Vendor, Country and invoice#, then after "Look up" button is clicked, it will automatically list down those related data with latest invoice date and the price after calculation.

How can it be done?

Do I need Macro? I don't know about VBA/VB programming.

If the selection is in combo box, how can I reference it to the database worksheet?


Many thanks in advance.

Best Regards,
Sue

 

[bronc]

you will have to start by setting a refernce to your data workbook in your "utility " workbook like this.
dim wbk as Workbook
set wbk = WorkBooks("NameOfYourWorkbook.xls")

and then setting a refernce to the particular sheets:

dim wks as Worksheet
set wks = etc

then you need to reference the Ranges on the the respective worksheets etc

then do a search in the ranges for the dates or whatever and find the corresponding entries.

this is all pretty standard stuff and think it would be a good idea to learn a bit of VBA esp if you're going to maintain it.
but you never now there may be some nice person on the forum who will write the whole shooting match for you

good luck

 

[bronc]

i forgot to mention that if you're not too keen on VBA , you may try Vlookup but its going to be quite limited. It works across workbooks. I student told me that it even works on unopened w/books. I told him only API's could delve into unopened objects like that but had to apologize when it did.

 

[SkipVought]

Sue,

This is your biggest problem...

"...a workbook with few tabs "Product A", "Product B", Product C"..etc..."

Maybe that's what you are stuck with, because someone in your company did not know what damage they were doing to defeat the plethora of data analysis and data reporting features contained in Excel, by chopping up the data into separate sheets.

If your data were ALL consolidated into ONE TABLE (sheet) with, at least, a new column for Product, most of your blood, sweat and tears would be behind you. As it is now, the problems related to the analysis and reporting of your data, a great corporate asset, will continue to plague you and your company. What a waste of time and effort! With a well designed table, you would be able to answer questions, like the one you posted, in a matter of seconds, and with no VBA code, using some simple query techniques available in MS Query. faq68-5829. It might even be as simple as applying an autofilter and making a selection, or wipping up a quick PivotTable. But NONE of those options are available to you to use in a simple, straight-forward form, with your current wrokbook structure.

Skip,

_{Just got a nuance...
to replace the old subtlety!}

 

[annettes]

Thanks to bronc, and SkipVought.

Bronc,
I think Vlookup is not sufficient for this that's why need to build a small query... anyway.. i will try my best.
By the way,

I have this combo box.. where i use .Additem to add to the list...
.Additem "A"
.Additem "B"
.Additem "C"

but everytime i click the combo box.. the item keep appending...
the first time I click..it display 3 item, then the 2nd it will display 6 item (A,B,C,A,B,C).. how am I going to do in order to refresh the list everytime click on it?

Thanks

 

[DPlank]

You need to ensure the items are added to the list only the once.

For this reason it would make sense to either:

1. Add the items in the Worksheet_Open or the Userform_Initialise event
or
2. If you need the list to be able to dynamically change while you have the workbook open you'll need to use the RemoveItem method to clear the control out before you use the AddItem to make up the amended list.

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[annettes]

Thanks DPlank, it works!

Now I am trying to create a combo box reference to column in other worksheet, what should I do?
Another thing is, my 2nd combo box need to depend on my 1st combo box, how can I control it?

Many thanks in advance

 

[DPlank]

You'll need to be more specific about what you're doing in reference to "column in other worksheet". Do you want the combo box to read values from another sheet in the same workbook, or is it another workbook?

The dependency isn't too hard to arrange though. In the _Change event for the first combo box, specify the list of items to be added to the second based on whatever conditions you have. For example:

Private Sub ComboBox1_Change()
With ComboBox2
    For Each ListItem In ComboBox2.ListCount
        ComboBox2.RemoveItem (ListItem)
    Next
End With
Select Case ComboBox1.Value
    Case "1"
        ComboBox2.AddItem ("Bill")
        ComboBox2.AddItem ("Ben")
        ComboBox2.AddItem ("Steve")
    Case "2"
        ComboBox2.AddItem ("Tom")
        ComboBox2.AddItem ("Dick")
        ComboBox2.AddItem ("Harry")
    Case "3"
        ComboBox2.AddItem ("Dave")
        ComboBox2.AddItem ("John")
        ComboBox2.AddItem ("Alex")
    Case Else
        ComboBox2.AddItem ("Colin")
        
End Select

End Sub

Obviously this is a simplistic example, but you should get the gist of it from this.
Should note this code is done from memory - syntax is not necessarily 100% as I don't have the ability to actually test it

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[annettes]

Hi Dave,

Thanks for your reply.
Can you tell me what's ListItem?How to do ListItem?

Thanks.

 

[PHV]

In a UserForm you may simply use this:
ComboBox2.Clear

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DPlank]

Knew I'd missed an easier way... Just couldn't remember it! Though the OP hasn't mentioned using a userform - does this work on controls added directly to a sheet as well?

And to answer Annettes question re ListItems:

In this case the ListItem is the variable used to refer to an item in the List (you could have called it ItemIndex, or even Bob - though calling it Bob isn't recommended). What that bit of code is doing is seeing how many items are in the list(ComboBox2.ListCount), then for each ListItem, executing the RemoveItem method.

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[annettes]

Hi Dave,

Thanks for explanation.
How am I list all the value in ListItem?
Is it something like this:

ListItem = "A" "B" "C" "D" "E"
when the below is execute, the A,B,C,D,E listed under the combo box will be removed?

For Each ListItem In ComboBox2.ListCount
ComboBox2.RemoveItem (ListItem)
Next

 

[DPlank]

That's pretty much it, but instead of ABCDE it'd be the index numbers of the items in the collection.

You don't need to specify what the ListItems are - VBA will know to work with each entry in the ListCount for the object.

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393