How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask NewITZone Your Own Question
NewITZone, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 90
Experience:  I have been using Office Pro for 10 years and implemented applications for business use.
Type Your Microsoft Office Question Here...
NewITZone is online now
A new question is answered every 9 seconds

Excel spreadsheet - leave calendar with Gnatt chart that

Customer Question

Excel spreadsheet - leave calendar with Gnatt chart that filters by month/year.
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Jason Carlamere replied 1 year ago.

Hello - My name is ***** ***** I will be helping you. In order to better assist you I need to know exactly what you are trying to accomplish. Can you give me more details as to what you are trying to do.

Customer: replied 1 year ago.
I've created a standard excel template to help track leave. I'm able to use the combo box to filter the correct months/year automatically however when I enter data it doesn't filter with the months. My goal is to track leave request for each month for the next 5 plus years.
Expert:  Jason Carlamere replied 1 year ago.

Hello Thank you for providing your spreadsheet. I have a better understanding now of what you are trying to accomplish. Just so that I am clear, what you are trying to do is put an x or something for a day that an employee with be out and then when you change the month you are looking for that data to be cleared. Then when you return back to that month you want to see the data that was previously entered.

Although this will able to be accomplished using multiple tabs (one for each month) it is not how the drop down calendar list is used and the functionality that you are requesting is not supported by excel. The reason being is excel does not have data storage in the sense that you can dynamically store or pull data that is not on a given sheet.

You can accomplish what you are asking in excel with some VBA scripting. Here are your options.

  • Add VBA scripting so that when you select a month and year a new hidden sheet is created so that the data can be stored for later retrieval when you change months.
  • Add VBA so that when you enter data in the main sheet for an employee and change months the data gets copied to the individual months sheets that was created above.
  • Add VBA so that when you change back to the year month selected the data is pulled from the above created sheet and copied into your main sheet your viewing.

Basically - what you are asking for can be accomplished with some VBA scripting but can not be done easily through some excel configuration. I know this is probably not what you wanted to hear. I hope this helps you. If you have any further questions, I will be happy to help.

Related Microsoft Office Questions