Forum Discussion
Help in creating table with offset and index and other formulas
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?
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
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.
mathetesAny solution will do. With or without the combo box.
Any formulat that solves this will work just fine.
thanks again
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.
