Selecting cells based on Month and Day of the Week

**ResidentKen** · 08-19-2012, 10:48 PM

Alright, there's gotta be an easier way to do this and I'm going to try and explain it the best I can. I have a table full of data, Column A are the months throughout the year, Row 1 are the days of the week. Each coordinate has a number; January and Monday has a number, August and Wednesday has a different number, etc.

I want the data to populate based on two drop-down menus, one month and one for day of the week, when selected it will give me the intersecting cells that has the required data. IF statements are the only way I can think of to do it but that's going to be a HUGE formula and there's got to be an easier way. Can someone help me out?

(I really hope I explained this clearly so I don't get flamed)

**benishiryo** · 08-20-2012, 12:22 AM

hi ResidentKen, welcome to the forum. I think INDEX & MATCH should be able to solve your problem, but could you upload an excel sample for us to work on? press the "Go Advanced" button beside "Post Quick Reply", then go to the paperclip icon.

if i visualise it correctly, B1:H1 is showing 1 to 7 (i.e. days of the week). A2:A13 is showing Jan to Dec? so if your dropdown is at J1 & J2 for Day & Month, your formula would be:
=INDEX(B2:G13,MATCH(J1,A2:A13,0),MATCH(J2,B1:H1,0))

but if it's wrong, then do upload a sample

**ResidentKen** · 08-20-2012, 07:47 AM

Actually, that's perfect! One more thing, I also want it to return the values for the next 3 days as well. So Day 1 will be the day selected, day 2 will be the next day, day 3 is the next, and day 4 is the last. With the days being Monday thru Saturday, how can I do that?

Thank you so much for helping me with Day 1! I think I may be able to figure it out at work today but in case I don't would you shed some light on where I can begin? As requested, I'll post the sheet below.

Sample.xlsx

**ResidentKen** · 08-20-2012, 06:35 PM

Okay so someone informed me of an issue that we're going to run into so it may be easier to explain what I want it to do if I tell you what I'm using it for.

I work at a bank. When we order cash from Loomis, we have to subtract the amount of cash estimated that will we go through over the next 4 days from what we already have in the branch. There is a table that we use to find out what these estimates are. It shows all the months in column 1, and the days of the week in row A. Each "coordinate" is the estimated amount of cash we will go through on any given day in that particular month. So on an average Monday in August, we're going to go through X amount of cash, so we need to subtract Monday's, Tuesday's, Wednesday's and Thursday's estimated use from what we already have in the bank.

Now, the problem I'm going to run into is the fact that if Monday is the 31st, and Tuesday is the 1st (for example, I know the 31st of August is not a Monday), it's going to take August's estimate of the cash used on a Tuesday, Wednesday and Thursday instead of September's estimate of the cash used for Tuesday, Wednesday and Thursday. Therefore, I'm wondering whether this would be easier if we used dates rather than selecting months and days of the week from drop-down menus.

If I put in a date, is there a way for it to figure out the month and the day of the week the date falls on? Also, will it be able to figure out the month and day of the week the next 3 days fall on?

If this is entirely too complicated, I'm just going to have to put in the estimates manually, but I figured I'd ask if there was a more automatic way.