Hello everyone, I hope this is in the right location, I didn't know if it should go here or in the VGA forum.
I have a question and I don't even know if this is possible but I have been trying a few different things with no success. Any and all help would be greatly appreciated!
So first of all let me tell you what I am doing. I have 12 files, 1 for each month, that contain a stat sheet each. What I am trying to do is develop a yearly stat's sheet as a different file. I know I can link the cells easily but I don't want to have to do new formulas for every year.
Try to visualize this
File path: E:Years\2012\September.xslx The folder 2012 contains a files for each month of the year. Each workbook has a monthly stats sheet that auto calculates the entries as they are entered. In another location I have a yearly stats workbook that has a cell that links to the cell in each monthly stat sheet that contains the total. Thus calculating the yearly stats from each month.
Here's my problem;
We plan on keeping up with this format and I want to make the file idiot proof! and also make it so it can be carried on after I am done with it and I don;t want to go through every sheet and create a new yearly stat sheet.
Is there a way I can create a cell where a user can enter the folder name or path and have the formulas in the sheet follow it to the monthly workbooks for that year? So if I open the yearly stats sheet and its populated for 2012 all I would have to do is enter 2011 in the specified cell and the sheet would adjust to the stats from 2011?
I have attached the yearly stats sheet I created just in case you want to see the format.
I would like to do this with formulas if at all possible but if VGA is the only way it can be done that would be fine too. Again if I am crazy and there is no way to do this I apologize for wasting your time
D
I found this later and Think it would work great I just don't know how to adjust the code to do what I want. Macro file with button
I would prefer not to have to use the macro button approach but again I'll try anything.
Bookmarks