I have two worksheets. One has a list of brokers and 50 columns for the different states. Each state column contains the license numbers for the brokers who are licensed for that state. If the broker isn't licensed, the cell in that row is blank. Or, it could say "Pending" or "Inactive."



My second worksheet has a separate tab for each state, and lists the brokers and their license numbers. The same information displays, but it's just organized differently.

I need to set it up so that we only input the information on one of the worksheets.

I thought an array was the best way to go, but I never used an array before, and I couldn't seem to get it to work between two worksheets. That's just one problem.

I've been reading the About.com tutorial on how to do this, and as I understand it, the VLOOKUP dialog requires me to put the first column in Lookup_value. But my lookup value is going to be whatever is in the State column off to the right. So I'm confused about what goes in the Look_up value field, and what goes in the Col_index_num field. I'm probably confused about the two remaining fields as well. The examples in the tutorial don't match up with what I'm trying to do, and I can't wrap my head around the logic yet to be able to customize.

And then, of course, there's the issue of sending the information off to that second worksheet.

Am I on the right track? Or should I be doing something different?

Thank you!

if you upload a sample, it will be easier for me...

I've attached a mockup of one of the spreadsheets. The other one would be too complicated to reproduce as a dummy file - it has 50 tabs for the states, and it contains names and licensing information I would have to replace.

The state tab on the second worksheet must display broker information (the brown section), along with whatever appears in the state column of the first worksheet. If the cell in the State column is blank for that broker, nothing transfers to the state tab in the other worksheet.

The format for the second spreadsheet can just rely on the input from the first spreadsheet - it doesn't matter how it displays. The column for Alabama (AL) must transfer licensing data to the other spreadsheet's AL tab, and so on.

It also doesn't matter which worksheet requires user input - if it's easier to input into the tabbed worksheet and have the state columns in the other worksheet capture the data from its applicable tab, that's fine too (and I'm playing around with it). Whatever works - the only stipulation is that the user must input into one worksheet, not two.

Thank you!!

Attached Files

TestLicenses.xlsx (49.2 KB, 11 views)

There is something unclear:
when you say " i have 2 worksheets, one with..." you mean 2 worksheets in the same workbook,
or, more probably , you meant " 2 workbooks ", the second workbook contains 50 worksheets named with states name?
Check the samples attached, ( you need to sort worksheets after updating values), for a macro to update all worksheets, you can try this: (will sort after column C-ID number)

Sub SortAllWks()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("A5:E175").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("C5:C175"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange Range("A4:E175")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next ws
End Sub

All the work can be done with a macro, as an alternative..

Attached Files

New Folder.zip (47.9 KB, 10 views)