Microsoft master sheet how and excel or access?

I have 21 branches and I'm trying to create a master table for expenses (another one will be made for sales and etc) for a 12 month period. All months and branches will be the same format so I thought it would be easy

What I originally thought would work is to create an excell workbook for each branch and a worksheet in that book for each month then I could link these sheets to a master workbook for me. My master book would have 12 sheets (1 for every month) then on each sheet I could create some type of drop down list for the branches when I selected the branch from that list it would pull that branches information from the other workbook for that month. I would also like it so if I changed something on the master list it showed on the workbook and vice versa. I don't care about tracking changes.

This is not easy and I have found nothing regarding it for excel or access on how to do this. Is there another way? Is this even possible? This must be possible

3 Answers

Relevance
  • Anonymous
    9 years ago
    Favorite Answer

    This sounds like a pretty tough task for a couple reasons:

    1. Your drop down logic would need to contain logic to switch between different files. With 21 file extensions and names, these formulas would likely be too long and complex for Excel to evaluate. You would need some VBA code to control this function.

    2. The ability to have one file read and write to the same file is tough--I can't imagine how you would do this with formulas alone. Again, you'd need VBA for this. You could also consider using an Access Database, but I would imagine version control between the 21 files to the database to the summary (and backwards too) would be pretty nightmarish.

    That is not to say this task is impossible! You will need a substantial amount of VBA, though. I have built tools that have performed similar functions. They are generally quite complicated to build and take quite a bit of time to develop. If you decide to go with this route, you'd probably have better luck at a heavy Excel user message board, like MrExcel.com.

    Sounds like a neat project though!

    • Commenter avatarLogin to reply the answers
  • 9 years ago

    Take a look at using Pivot Tables. Pivot Tables are very good at summarizing massive amounts of information while keeping the data source simple. Th help file has some very good examples that should give you some ideas on how to handle your task.

    Source(s): Experience
    • Commenter avatarLogin to reply the answers
  • Anonymous
    9 years ago

    all things are possible

    • Commenter avatarLogin to reply the answers
Still have questions? Get your answers by asking now.