Drop Down List Issues

[SierraCharlie]

Hi everyone,

I was hoping to get some help with a spreadsheet I have been trying to develop for work, have spent hours and hours watching videos and reading tutorials but can not get my formulas to work properly and am hoping its even possible.

In this particular sheet I am tracking opening and closing times for a number of different areas, these areas are in separate sheets labelled January, February, March etc and this is where the main source of data is, at the end of the column's is a total calculating the amount of hours it was open and then an average for the month.

What I was hoping to achieve was in the tab 'Performance Tracker', I have listed all the areas and then there are tables with Daily Target Hours, Total of Hours Open, YTD of Total Hours Open, Daily Average and YTD Daily Average and from there I wanted a drop down list with the relevant reporting month to show data from the particular month and the information change as you select different months in the Hours Open and the Daily Average Hours, I was hoping YTD would remain and there would be a separate formula simply adding up the total for the year.

It would also be beneficial if Daily Average Hours changed to red when below the target hours and green if it was above.

Im not sure if this is all possible or if I'm asking too much from Excel but any assistance would be greatly appreciated.

Thank you for any help.


SC

[Glenn Kennedy]

Work in progress.

Your proposed set up is illogical. it is a mishmash of a monthly report and an area report. I propose this> see sheet. I have done the first half of the job.

1. The Year (A3) is needed to count days per month (Leap years...)

2. Select month from DD A6

3. The YTD Daily Average Hours look odd as we are mid-way through the month and there are dummy data throuh to the end of the month.

4. I have done nothing to the lower table yet, other than scope it out.

5. What is the purpose of the sheet: data?

Views?

[SierraCharlie]

Thanks Glenn for your help, it's definitely close to how I wanted to present the information.

You are right about the data, I carried over some sample data from 2020 but have now removed this to make more sense.
The purpose of the sheet is definitely data collection and improving on a current process. Currently, we had no formula to calculate all these opening and closing times that we were reporting back on so I was hopping to firstly put in place the correct formulas and then be able to easily extract that information per month and per area.
Issue I also been having is that Area 7, although hours are recorded as normal, we need to display only the required 1 hour per day and same with Area 8 for 3.5 hours (contract stipulation)...

[Glenn Kennedy]

You said:

Issue I also been having is that Area 7, although hours are recorded as normal, we need to display only the required 1 hour per day and same with Area 8 for 3.5 hours (contract stipulation)...

Where does Excel find out this information, where is it recorded?

EXACTLY what do you expect to see, where do you expect to see it? Manually overtype what you expect to see and shade the cells you have overtyped and repost.

make sure you answer ALL questions.

[SierraCharlie]

Hi GK, please see attached with some additional comments...

[Glenn Kennedy]

You didn't answer my questions. If you don't do that, I can't help.

You then went on to say:

"I put in some test data for the year but it seems to be quite high and possibly multiplying?".. What test data? Where?

Read what it says in Post 4 and do what it says!! Otherwise we're playing guessing games... and that is a waste of everyone's time.