Help in creating table with offset and index and other formulas

[mmslimunga]

Hi,

I am new here. I want to create an excel sheet with a series of formulas

1. there are four different 4 categories ( with each category having sub categories between 2 and 14) precisely Inquiry, complaint, Incident and Security.

2. users have to put in how many request were raised in the different subcategories found in the categories through out the week.

3. The sum of all the different request for the sub categories in the categories are calculated daily

4. attached is a draft file to explain a bit how things look like.

Thanks for helping

[#2]

@mmslimunga 

I've looked at the sheet, read your post; it's not clear to me what you're asking; nor is it clear what the sheet is collecting or how it's working. Is there a macro behind what is visible? Are the answers to the drop-downs being collected somewhere?

[#3]

@mathetesthanks a lot for reaching out.

Actually the sheet is just to further explain the question. You see the categories and the subcategories below.

The numbers are nothing. I was actually trying to use developer combo to see if I could use offset, and index function to help me build the solution.

in my mind, I am thinking I can do sumifs with respect to index but I can't come out clear with something that works.

Actually i want the total number of request made per week for all the categories

e.g

On Monday:

Inquiry, 4 request were made Gen, 3 for How, 8 for Pol and 2 for sta 

Incident: 2 request for AD, 0 for DC, 10 for DI

Security: 6 for DS, 10 for Net, 8 for UAC

and all the other are 0, i should get the sum of all (53 request for Monday)

So i need to get the total number of all request for each day in the week. And at the end get the total for the whole week.

I hope this is helpful

[#4]

@mmslimunga 

It's the first mention you've made  of the Developer Combo Box--so in that sense it's helpful. I've never used that part of Excel, so I'll defer to somebody else here who has.

I have used INDEX and OFFSET, but not in this connection, so I'm afraid that you'll need to wait for somebody else there too, unless you can be more descriptive about where those even appear here absent the Combo Box, if at all.

[#5]

@mathetesAny solution will do. With or without the combo box.

Any formulat that solves this will work just fine.

thanks again

[#6]

@mmslimunga 

As I said before, though, I don't see what's happening here at all. Your descriptions, along with the spreadsheet, just don't make sense to me. If you want to take away the combo box and show what those requests are that are being processed, maybe it could start to make sense. I'm sure it's crystal clear in your mind, but it's not at this end at this point.

[#7]

@mmslimunga It seems you are trying to mimic data validation through combo boxes. Not sure how the data should get entered in the first place. Perhaps something like what's in the attached file (row 18 and down) is something to point you in the right direction. It's a quick-and-dirty example of what's possible. Have used the lists in K:M for the data validation source lists. To get the total number of requests use SUM. If you want sub-totals by day, you can use SUMIF. 

[#8]

@Riny_van_EekelenThank you very much.

This is good light you threw on it. I am reviewing.

will share with you the final road I took.

thank u