Save inputed data for each month

[MuchieMac]

Hi All

I am creating a project planning/resource allocation spreadsheet in which i am using v-lookup with a drop down pick list to select the months of the year.

The idea is to allocate a percentage to each project for each resource for each month (See attached sample file).

The problem i have is that when i select each month it is not saviing the previously inputed data for each month.

Example = In January John's time is split accross 4 projects which is 25% x 4, Now lets say in February John is only working on 2 projects 50% x 2, i need each months inputted data to be saved to that month, currently it will only carry over the last lot of data inputted for the previous month.

Hopefull this make sense.

Many thanks

[Richard Buttrey]

Hi,

I can't visualise what you expect to see when the month and data are changed.

Please create another area under the January table and manually add the results you expect to see. Clearly indicate which are the data cells that you have changed and which are ten result cells which you need automated.

[MuchieMac]

Hi Richard

Thanks for the reply.

The idea is to plan the resource allocation for the year.

The user will only input the percentage allocated (percent cell) and the days (underneath) will be automatically calculated.

The idea is to do this for each month however if i go back to a month that i have already allocated (Say from June back to January) it does not save what i have previously inputed for January (it just carries over).

So if i allocate January and move on to February, Februarys percentages should be 0% but it carries over Januarys allocation.

Hope this is clear.

Many thanks

[Richard Buttrey]

Hi Richard

Hope this is clear.

Many thanks

Sorry, but no it isn't. Please do as I said earlier and manually add the results you expect to see. A picture is worth a thousand words. Make it easy for us to help you.

[sourabhg98]

Yes, this is indeed very unclear. But I think you want a template to save data for a particular month and when other month is selected then data for that month is loaded into the spreadsheet.

Well for that, Excel isn't God that it would know by itself that this has to be done. You need to create a DataBase for that where data for all months is stored and then extract data for the selected month into the "Planned" sheet.

Here, you would require 2 macros. First for loading the data from DataBase to the Planned sheet. Next for saving the data and changes back to DataBase from Planned sheet.

Now, the macros provided below would consider each alternate row of data starting from the second row. So 2nd, then 4th, then 6th and so on... You can change the number of people in the macro.

Before you begin with working with the macros. First enter number of people and number of projects in all the macros.
Then run the macro named set_database which will set the database ready for use.

Now after pasting all these 3 macros in a module you need to create a private macro in the sheet module. So right click on the sheet "Planned" and click on view code. Now paste the 4th macro there.

So these 3 macros go in standard module-

Please Login or Register  to view this content.

The 4th macro goes in the Planned Sheet code-

Please Login or Register  to view this content.

Note- Column "T" has been used for keeping the list of all months for the dropdown and "U1" has been used as a helper cell for the macro.

- You cannot change number of people in the middle of the functioning of these macros. PLEASE BE SURE ABOUT THE NUMBER OF PEOPLE YOU WOULD HAVE AS IT CANNOT BE CHANGED, ONCE SETUP IS GENERATED. If you change the number of people and run setup macro it would clear all existing data in database. So to be on the safer side input a large number of people, like if you have 10 people enter 15 or 20 instead so in near future this doesn't create problems.

Check attached for an example.
Advanced-Resource-Planning(5).xlsm

Now try adding some data in the planned sheet and change the month from dropdown in Cell A1.