VBA with vlookup across multiple worksheets

[shawnnai]

I have multiple worksheets with data for which I would like to write a macro to search and "retrieve" based on the State/County/Municipality and Client selected. I have created the drop down menus that should drive the data being searched, but I'm not confident in my very limited abilities to take this to the next level.

End goal: User should be able to select from the dependent drop down lists (State/County/Municipality) and/or Client, click "Retrieve Data" button and pull in data from the appropriate worksheet driven by the selection in the drop down lists.

[mikeTRON]

Have you thought about using VBA as a processor to lay the data out in such a way that you can then use very simple formulas to retrieve the data you want. Basically you can hit PROCESS and it will do some work in the background (assuming you update this thing on some sort of cadence) and then your formulas can all be very simple vlookups or sumifs dependent on your drop down selections?

You can also use the VBA to autopopulate the DataValidation lists, although I have been using a pivot table and just refreshing them all to get the complete list.


I ask because I was trying to do the exact thing you mentioned, use VBA to grab data for a report, and I ended up getting frustrated dealing with auditing more complex code, where I then switched to process the data and lay it out in such a way that is it VERY easy to get what I need with VERY simple formulas.

[mikeTRON]

Actually, why can't you use formulas for what you are trying to accomplish now?

Also, your requests are far too vague, I suggest you ask specific questions for specific solutions. For example, what cells are you trying to populate, and from what data source? You leave us up to guess where it all comes from. I suggest you post a sample resolution with data (that can be typed,and not driven by a formula) so we know what you are trying to accomplish.

[shawnnai]

First of all, thank you for your feedback. I've forgotten that I've been staring at this for hours and that you are not in my head to understand what I want. I've realized based on what you said that all I need to do is use a vlookup formula (?) to get the data that I need. i'll still use a VBA to input these formuals in case the end user should accidentally delete them and because I want the visual cue of clearing the form and retrieving new data to reduce any confusion for the intended 40+ users of the final product.

So what I really need is:

1) Each cell in Column A of Request Form represents a column heading in the corresponding tab (State Specs = RecordingStateInfo, Recording Entity Specs = RecordingEntities, Client Specifics = RecordingClientInfos.
2) Each cell in Columb B of Request Form tab should yield the data from the corresponding column that matches the cell in Colum A based on the options selected from the drop down list in B3 (Selecting Connecticut in B3 should yield text from C2 of RecordingStateInfo in cell B9). I have used formula: =vlookup(B3, RecordingStateInfo!B2:E5, 3, FALSE) and get result 0 in B9 of Request Form. Is it because I have a drop down list instead of free text?
3) Selecting Connecticut in B3, Fairfield in B4 and Danbury in B5 on Request Form tab should yield text from F6 on RecordingEntities tab in cell B16 on Request Form tab. How do I use a vlookup formula to validate three layers of information (since I will have identical county or municipality names but unique once matched with a corresponding state name).

I think that's it for now. Thank you again for your help!

[mikeTRON]

So your text is not helping much more.

What specifically are you asking for help with? Everything from start to finish? I really dont know where you want help and I dont feel like guessing my way through everything you have as blanks.

[mikeTRON]

I suggest you post a sample resolution with data (that can be typed,and not driven by a formula) so we know what you are trying to accomplish.

Post this from an excel worksheet, and I might be able to help more...