Formula to alter calculation dependant on drop down list value

Hi,
This is a continuation of a previous question that we sorted....

I have a spreadsheet that users fill in, they should use a comma to separate the different values that they might enter into a cell but they don't always, also they don't always use a Capital letter at the start of their code, hence some of the specifics we have had to create in the formula as it stands.

I can't alter the data collection section of the sheet but can add more sheets, reposition the reporting etc.

What I would like is the ability to pick a day and time from the drop down lists in the report section and have that alter the relevant results in the report section.
My gut says that some sort of lookup table would help but i can't figure it out. Can anyone help?

Data 2.jpg

Thoughts on this:

Have the data in a single table i.e Monday C5:H18, Tuesday C 19:H32 etc

Create a "working" table so that data for a given day is "copied" to this table based on the day selected.

Base your formulae on this table

Sounds like a good plan - or at least the start of one.

I have created a sheet with the data copied over as you suggested. Now how would you suggest for me to alter the formula so it knows when to look at the relevant section of that table based on the drop down selections?

This looks amazing thank you.

Might need to do a little tweaking but its really close to what i'm after.

one "tweak" is to remove the headings in the combined table for days Tuesday to Friday so you have contiguous data for a week. Put the day in the leftmost column (at least on the 6 hour line).

You will need to amend a formula or two but nothing major.

Re WEEKS: a sheet per week or all weels on one sheete; down columns - approximately 3000 rows - OR across columns - approximately 350 columns.

One (not major) advantage is that it removes the need for the INDIRECT used in the formula to "grab" the data fron a WEEK sheet to the "Workarea" sheet.

Whatever you do, avoid having data in "blocks" as per your original format . Simply columns (Fields) and rows (Records) format is the "gold" standard.

You've done an amazing job with this thank you.
Looking through your formulas is definitely increasing my own knowledge.
Unfortunately the original format was something that was already 'in the wild' and i just needed to do this to be able to work with it. If they were looking to repeat i would completely alter the way it was set up.

One last calculation you might (almost definitely will) be able to help with. How could i count all the instances of any code (not just a specific one) within the 'Work Area' given that whole issue of them not always having a comma or being in the right case?

Certainly.
So I've added a box out (in Red) which shows how many of all codes were entered that day. The formula should come back with, in this instance, the answer 27.

I tried a variation of the earlier formula but obviously it needs a reference to be able to count. I tried both "*" as a wild card and also pointing it to the lookup table of all the codes but neither seemed to work.

I (probably wrongly) assumed a variation of this code would work but i just can't work out what to swap the O25 reference for so it looks for all codes and not just the one in O25

=(LEN(UPPER(L5)&","&UPPER(M5)&","&UPPER(N5)&","&UPPER(O5)&","&UPPER(P5)&","&UPPER(Q5)&","&UPPER(L6)&","&UPPER(M6)&","&UPPER(N6)&","&UPPER(O6)&","&UPPER(P6)&","&UPPER(Q6)&","&UPPER(L7)&","&UPPER(M7)&","&UPPER(N7)&","&UPPER(O7)&","&UPPER(P7)&","&UPPER(Q7)&","&UPPER(L8)&","&UPPER(M8)&","&UPPER(N8)&","&UPPER(O8)&","&UPPER(P8)&","&UPPER(Q8)&","&UPPER(L9)&","&UPPER(M9)&","&UPPER(N9)&","&UPPER(O9)&","&UPPER(P9)&","&UPPER(Q9)&","&UPPER(L10)&","&UPPER(M10)&","&UPPER(N10)&","&UPPER(O10)&","&UPPER(P10)&","&UPPER(Q10)&","&UPPER(L11)&","&UPPER(M11)&","&UPPER(N11)&","&UPPER(O11)&","&UPPER(P11)&","&UPPER(Q11)&","&UPPER(L12)&","&UPPER(M12)&","&UPPER(N12)&","&UPPER(O12)&","&UPPER(P12)&","&UPPER(Q12)&","&UPPER(L13)&","&UPPER(M13)&","&UPPER(N13)&","&UPPER(O13)&","&UPPER(P13)&","&UPPER(Q13)&","&UPPER(L14)&","&UPPER(M14)&","&UPPER(N14)&","&UPPER(O14)&","&UPPER(P14)&","&UPPER(Q14)&","&UPPER(L15)&","&UPPER(M15)&","&UPPER(N15)&","&UPPER(O15)&","&UPPER(P15)&","&UPPER(Q15)&","&UPPER(L16)&","&UPPER(M16)&","&UPPER(N16)&","&UPPER(O16)&","&UPPER(P16)&","&UPPER(Q16)&","&UPPER(L17)&","&UPPER(M17)&","&UPPER(N17)&","&UPPER(O17)&","&UPPER(P17)&","&UPPER(Q17)&","&UPPER(L18)&","&UPPER(M18)&","&UPPER(N18)&","&UPPER(O18)&","&UPPER(P18)&","&UPPER(Q18))-LEN(SUBSTITUTE(UPPER(L5)&","&UPPER(M5)&","&UPPER(N5)&","&UPPER(O5)&","&UPPER(P5)&","&UPPER(Q5)&","&UPPER(L6)&","&UPPER(M6)&","&UPPER(N6)&","&UPPER(O6)&","&UPPER(P6)&","&UPPER(Q6)&","&UPPER(L7)&","&UPPER(M7)&","&UPPER(N7)&","&UPPER(O7)&","&UPPER(P7)&","&UPPER(Q7)&","&UPPER(L8)&","&UPPER(M8)&","&UPPER(N8)&","&UPPER(O8)&","&UPPER(P8)&","&UPPER(Q8)&","&UPPER(L9)&","&UPPER(M9)&","&UPPER(N9)&","&UPPER(O9)&","&UPPER(P9)&","&UPPER(Q9)&","&UPPER(L10)&","&UPPER(M10)&","&UPPER(N10)&","&UPPER(O10)&","&UPPER(P10)&","&UPPER(Q10)&","&UPPER(L11)&","&UPPER(M11)&","&UPPER(N11)&","&UPPER(O11)&","&UPPER(P11)&","&UPPER(Q11)&","&UPPER(L12)&","&UPPER(M12)&","&UPPER(N12)&","&UPPER(O12)&","&UPPER(P12)&","&UPPER(Q12)&","&UPPER(L13)&","&UPPER(M13)&","&UPPER(N13)&","&UPPER(O13)&","&UPPER(P13)&","&UPPER(Q13)&","&UPPER(L14)&","&UPPER(M14)&","&UPPER(N14)&","&UPPER(O14)&","&UPPER(P14)&","&UPPER(Q14)&","&UPPER(L15)&","&UPPER(M15)&","&UPPER(N15)&","&UPPER(O15)&","&UPPER(P15)&","&UPPER(Q15)&","&UPPER(L16)&","&UPPER(M16)&","&UPPER(N16)&","&UPPER(O16)&","&UPPER(P16)&","&UPPER(Q16)&","&UPPER(L17)&","&UPPER(M17)&","&UPPER(N17)&","&UPPER(O17)&","&UPPER(P17)&","&UPPER(Q17)&","&UPPER(L18)&","&UPPER(M18)&","&UPPER(N18)&","&UPPER(O18)&","&UPPER(P18)&","&UPPER(Q18),O25,"")))/2

Data 3.jpg

See my (updated) reply #8 as our replies crossed.

Genius!

Thank you.

You've been a huge help.

You are very welcome. I enjoyed this because it was more helping with an application rather just simply providing formulae.

And well done to you for the application you developed.

Hope it all goes well. You deserve a pay rise!!!

If only - I'm a poor NHS worker, no pay rises here.
Suspect the next thing will be to try and collate the responses from this workbook, think its gone out to about 15 people, will need to copy their sheets into my workbook and then display the info in both those straight tables i've (you've) been creating and then probably some run charts etc to do some comparisons.
Feel free to offer suggestions

Just spotted a flaw in the formula - Some of those codes are more than two characters long, eg C25, P10 etc
The counts then don't work because its splitting everything down to 2 characters.

Good spot!

Do we have a mixture of code types i.e. C10, P5 can appear in the same Day ?

Yes. Any number of different codes can all appear within the same cell (time window).

Oh and to make it more fun I've just found out that actually its more like 40 people using this sheet that will need to be collated

So the new calculation means the the daily overall count works but the hourly break down ones don't.

I do have the latest version of excel.

Unfortunately i can't alter this form for the moment but going forward i definitely would

Sorry for the length of time its taken to come back to you. Had a call and also had to create a new bit of code as something went wrong.
I think I've got it now though. Would appreciate you taking a look to see though.

The idea of the Workbook is to understand workload across all of the 40 users, both as individuals and as a team in total. This obviously helps to be able to see those individuals but i'm not sure how i'll collate it for publishing the entire teams results.

Attached is version with modifications: mainly SUBSTITUTE in columns S to W

Add all daily data for "WEEK 1"

That looks great, thank you.
Need to have a play with the idea of how to add the weekly data together, Thought i could count the instances within the 'transposed' data (B3:H82) but its having issues counting that due to either the lack of commas or the case sensitivity of the code, plus i think it just counts the first instance of code in a cell.

Hi,
I think we would need to create another "Work Area" for all the weeks data to drop into, rather than just the daily one, i had just been trying to calculate the counts using the index info that is in that left-hand side (in blue - B3:H82) but that, as i said in my last post, wasn't working properly whereas with the work area we were able to add the various formula to make it more manageable.
In theory the data should be being entered by the end user directly into the original spreadsheet however i suspect that some (many) of them may be entering the data into printed versions of it and i'll need to enter it by hand after the two week period. There is only two weeks worth of data being captured so we won't need a monthly or more summary - Just the daily (which we have) and then a weekly (possibly a total for the two weeks too). The weekly summaries just need the overall count for each code.
I have no aversion to VBA other than my lack of knowledge of it

Are you willing to have a go at the weekly version?

Options:

Choose "Daily" or "Weekly" if you only need a snapshot ... so you can use the same workarea but different formulae for filling the data, with modified dasboard

(VBA might be useful to fill in the workarea if weekly to avoid long formula: thinking on the go here!)

Or have a completely separate Weekly workarea set up as you currently have the for "Daily" with its own dashboard?

I think a totally separate weekly work area is the way forward - in fact possibly creating two rather than selecting which week you want given that there is only the two weeks.
If i copy the calculations sheet and extend the work area down and then create a final sheet which will display the info separately.

Keep in touch if you have any problems but I am sure you will manage O.K.

LOL you have far more confidence in my talents than i do.

I'll mark this topic as solved again. Thank you for all you're help.

Hi John,

Spotted a small issue with this today (i'm just starting to get the original sheets from the users so was about to start filling it in) when the count column comes across a number with a 0 it counts it as if the zero wasnt there.

eg:

Attachment 723154

The code its counting is C1

Any ideas?

No file:see Yellow Banner

was just an image - is this working?

No - I need the workbookAttach a sample workbook (not image).so that we do not have to manually key in your data to do any testing.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

No problem, the workbook is the one you sent back last week.

The problem is it counting codes similar to C25 or C10 in the C2 or C1 counts.

The original requirements have slightly changed though now so it might make it easier to create, could i pm you my email address?

I changed to formula in S to add a comma to the end of the string

=SUBSTITUTE(L5&","&M5&","&N5&","&O5&","&P5&","&Q5," ","")&","

in U

=SUM(LEN(T5)-LEN(SUBSTITUTE(T5,$O$25 & ",","")))/LEN($O$25 &",")

so we compare "string" with added comma e.g "C1," "C10,"

However if you don't have a comma between entries it will fail e.g "C25C25" is present in Week 1 - need "C25,C25". I changed to the latter.