Linking info in workbooks based on a variable

[jexcelnewb]

I am trying to figure out how to link information from a master/summary workbook to several individual workbooks.

I have looked around the forum but couldn't find anything that totally answered my question.

Here is a brief summary:
I have a master workbook that has info on 100's of companies including name, address, phone, fax etc.

It is in a table with headers:
Example:
Firm Type Address City State Zip Sector
Company ABC, 123 A street, San Francisco, CA 94233, manufacturing
Company BBB, 525 B Street, Chicago, IL 50551, sales
Company CCC, 585 C Street, New York, NY 10551, sales
Company DDD, 645 D Street, Los Angeles. CA 90032, transport

Of course, the sheet is in rows and columns with each company its own row, and each heading its own column.

I also have a separate worksheet for each state with the exact same info as the master list, but only for firms in that state. I.E. Alabama Company List, Alaska Company List, Arkansas Company List etc.

The variable in the example above is the State. So how do I get the California Company List to autopopulate all the info from the master worksheet for any firms with CA as their State? I might also do the same thing for the Sector, but it seems the same principal would apply.


How do I create these state -by-state lists and have them update whenever I update the master list? I will be changing the master including adding new companies and deleting old ones.

So while only updating the master list, I would like the workbooks from each state to automatically fill in all the information for companies in that state.

Do I need to create a macro and run it each time? Or is there a way to format my state workbooks so that they will automatically reflect the data from the master each time it is edited or saved? I also want to keep the same formatting as the master list.

Any help would be greatly appreciated!

Thanks

[6StringJazzer]

Offhand I can think of three ways to do this. I would first like to know why it's necessary to have 50 workbooks, one for each state, when they replicate the same data in a master list. You'll see why in suggestion #1.

1. Don't do it. Just use the master list and filter on the State column to show whatever state you're interested in seeing. This is a couple of clicks.

2. Use formulas incorporating external references and MATCH (and probably a helper column giving the row of the MATCH result). Each MATCH results in a row number that tells you where the desired state is found. Then the next MATCH starts at the row where the previous MATCH found the state.

3. Use VBA so that you click a button and the list is refreshed. Two sub-alternatives:

a. Write code in the master to generate 50 files
b. Write code in a state file to refresh the data from the master

[jexcelnewb]

Thanks for your reply.

1. As silly as it sounds, I do need 50 separate workbooks. In order to safeguard our data, people are given only state or regional sections to work on. Every week, I will update each state workbook, save it, and email it out to different individuals.

2. It sounds like this may be a good way to go. I am not very skilled in working with MATCH. What I have figured out uses an equation like the following (with NY changed to CA or TX etc.) in each of the 50 workbooks for all cells and columns:
=IF([MASTERWORKBOOKTEST.xls]Sheet1!$D4="NY",IF([MASTERWORKBOOKTEST.xls]Sheet1!A4="","",[MASTERWORKBOOKTEST.xls]Sheet1!A4),"")

That essentially gets all the information to my state workbooks, but it also leaves me with blank rows for all companies in other states. I can use a table to sort the state column and then erase all those with no state data (ie all that aren't in NY). Unfortunately, I don't get the same formatting (for example website urls are lost) as on the master. In addition, it is not the mostly automated process I am looking to develop.


3. The third option might be the best. I have some limited experience with vba and do not know how to begin writing a code to generate 50 files from the master. However, if you could point me to some resources for how I would do this it would be great. Because I only generate the workbooks 1x week, this seems like a totally appropriate solution. Of course the other solution you proposed (3b) might work just as well.

Thank you!

[6StringJazzer]

This file has some sample data. Go to Sheet2 and click the button to generate state files.

This method sorts the data by state first. This simplifies the logic for the programming considerably.

The files are written to the same directory holding the master.

If there are no records for a state, no file is generated.

Wrong version of attachment removed

[jexcelnewb]

Thank you. That seems like exactly what I need. The only problem is I don't know what you mean click the button on sheet2. The cells with state abbreviations just appear to be text. I am sure it is something obvious, but I am feeling kind of dumb. Can you elaborate on what I am supposed to do to generate the new files? Sorry, and thanks!

[6StringJazzer]

Disaster has struck. Somehow I have uploaded the wrong version. I will check my other machine and try to get this corrected and upload the correct version. Sorry!

[6StringJazzer]

Phew! Here we go, I used the wrong directory.

[jexcelnewb]

Wow. That is perfect and exactly what I need. Thank you so much for taking the time to do that. Hopefully it will help others as well.

I have one additional question, if you don't mind. My master workbook contains some website urls and emails. These are all formatted as links. Is there anyway to keep the formatting from the master list (if I could match column widths that would be great as well, but not totally necessary). I did find a macro to turn websites into urls, but could not find one for emails.

[6StringJazzer]

Here is a revision that copies links. The code here doesn't care about the link format, it just copies it. But for an email link you want it to look like

mailto:[email protected]

I also added a progress form. Since this could take a while it gives the user something to reassure him that something is happening.

I added copying over column widths.

It is not difficult to copy formats but you have to code each one separately, and there are a whole of things you can format. So please specify which type of formatting you want to copy. Examples are:

• Font style
• Font size
• Font color
• Bold
• Italic
• Fill color
• Fill pattern
• Borders

There are more.

I guess I could use copy & paste, which would bring over formats, although I think it takes longer. I would rather target the formatting you're interested in, if you know exactly what it is.

Also please take this as a lesson in specifying all requirements up front

[jexcelnewb]

Actually the primary formatting I needed was the urls, so the macro you included for that is perfect. I have been playing around with the macros and they are exactly what I was looking for. Thank you so much for your considerable effort.