vlookup modification

  • Thread starter Thread starter David
  • Start date Start date
D

David

Group,

I was helped with a vlookup formula that worked on the 1st version of this
workbook that contained a form worksheet1 w/data validation list(names), and
only 1 data worksheet2.

origional vlookup;
=VLOOKUP(B2,'Information Sheet'!$A$1:$E$100,2,0)
I now need more than 1 data worksheet, I have the modified the workbook and
am now using combo boxes instead of the data validation list.
******************************************************************

worksheet1 is a "Form" with 2 comboboxes, cmb1 is a list of
worksheets(wks2-wks?), cmb2 gets populated with the list of names from the
worksheet selected in cmb1

worksheets2 - worksheets? contain ;

column A list of names
column B numbers
column C numbers
column D numbers
column E numbers

I have the combo boxes working, how do I modify the origional vlookup
formula to work with the combo boxes?

Thanks, David
 
Hi David
if your combobox stores the values in cell B3 (that is the worksheet
name you selected) try
=VLOOKUP(B2,INDIRECT("'" & B3 & "'!$A$1:$E$100"),2,0)
 
Thanks Frank, it works great, I am looking at the print routine because it
specifies a worksheet.

Thoughts?

Dim wks_active As Worksheet
Dim wks_list As Worksheet
Dim rng_list As Range
Dim cell As Range

Set wks_active = ActiveSheet
Set wks_list = Worksheets("Information Sheet")
Set rng_list = wks_list.Range("A2:A500")

For Each cell In rng_list
If cell.Value <> "" Then
wks_active.Range("B2").Value = cell.Value
wks_active.PrintOut
End If
Next


*********************************************************
 
The solution was to replace;

Set wks_list = Worksheets("Information Sheet")

with

Set wks_list = Worksheets(sheet1.cmb1.value)

this allows me to print from a unlimited number of data sheets without
naming them in the print routine, I thank you for all your help.

David
 
Back
Top