Formula With Multiple Conditions

[js10053]

Hey,

I have a new excel question... every time I think I am making progress, something comes along that I just can't quite figure out. So, I'm working on a new file, I need the file to pull a number based on a date and a time. I have done both of these formulas, but I have never tried to combine them. So, basically, I am trying to get a number to automatically generate based on the date, but the number varies depending on the time of the day. So far, what I have done is create a drop down list for the times that I need, I decided that would work better than having it use the current time for various reasons based on the information I need. I have made the formula work that will allow me to pull the data I need based on the time, but I can't figure out how to get it to do it based on the date.

I'm working with two separate tabs, the main tab has the drop down list with the time. The second tab has the information I am pulling from, its a chart that has each day of the week, with a breakdown of every hour of the day, i.e.


......Sunday..........Monday..........Tuesday
12 ...XYZ. ..............ABC ...............123
1 ......ABC .............DEF ................123
2 .....GHI ..............123 ................QRS

So, I just need to be able to select 12, for example, from my drop down menu and then have the excel file automatically put the coordinating data, for example if it were Monday, ABC, in the cell.

I think I pretty much have everything else in the file working, I have some conditional formatting going, I have the date that I can use if needed, etc. this is just what I'm stuck on. I currently have it where I can pull all the data for Sunday, it's just getting the file to know what day it is and pull based on that.

I hope this all makes sense, please let me know if I need to clarify anything.

Thank you!

 

[Marcílio_Lobão]

Re: Help With A Formula With Multiple Conditions

js10053, Good morning.

I have a ready model that might give you some ideas for your case.
It's in Portuguese.
Unfortunately I have no time available at this time to adapt it to English.

https://www.sendspace.com/file/pv219p

Any questions just ask.

Hope this helps.

 

[wideboydixon]

Re: Help With A Formula With Multiple Conditions

Example lookup table:

Book1
	A	B	C	D	E	F	G	H
1		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
2	0	$B$2	$C$2	$D$2	$E$2	$F$2	$G$2	$H$2
3	1	$B$3	$C$3	$D$3	$E$3	$F$3	$G$3	$H$3
4	2	$B$4	$C$4	$D$4	$E$4	$F$4	$G$4	$H$4
5	3	$B$5	$C$5	$D$5	$E$5	$F$5	$G$5	$H$5
6	4	$B$6	$C$6	$D$6	$E$6	$F$6	$G$6	$H$6
7	5	$B$7	$C$7	$D$7	$E$7	$F$7	$G$7	$H$7
8	6	$B$8	$C$8	$D$8	$E$8	$F$8	$G$8	$H$8
9	7	$B$9	$C$9	$D$9	$E$9	$F$9	$G$9	$H$9
10	8	$B$10	$C$10	$D$10	$E$10	$F$10	$G$10	$H$10
11	9	$B$11	$C$11	$D$11	$E$11	$F$11	$G$11	$H$11
12	10	$B$12	$C$12	$D$12	$E$12	$F$12	$G$12	$H$12
13	11	$B$13	$C$13	$D$13	$E$13	$F$13	$G$13	$H$13
14	12	$B$14	$C$14	$D$14	$E$14	$F$14	$G$14	$H$14
15	13	$B$15	$C$15	$D$15	$E$15	$F$15	$G$15	$H$15
16	14	$B$16	$C$16	$D$16	$E$16	$F$16	$G$16	$H$16
17	15	$B$17	$C$17	$D$17	$E$17	$F$17	$G$17	$H$17
18	16	$B$18	$C$18	$D$18	$E$18	$F$18	$G$18	$H$18
19	17	$B$19	$C$19	$D$19	$E$19	$F$19	$G$19	$H$19
20	18	$B$20	$C$20	$D$20	$E$20	$F$20	$G$20	$H$20
21	19	$B$21	$C$21	$D$21	$E$21	$F$21	$G$21	$H$21
22	20	$B$22	$C$22	$D$22	$E$22	$F$22	$G$22	$H$22
23	21	$B$23	$C$23	$D$23	$E$23	$F$23	$G$23	$H$23
24	22	$B$24	$C$24	$D$24	$E$24	$F$24	$G$24	$H$24
25	23	$B$25	$C$25	$D$25	$E$25	$F$25	$G$25	$H$25
26
Sheet2

Then the lookup is like this:

Book1
	A	B
1	Today:	Thursday
2	Hour:	13
3	Result:	$F$15
Sheet1

Cell Formulas
Range	Formula
B3	=INDEX(Sheet2!$B$2:$H$25,MATCH($B$2,Sheet2!$A$2:$A$25,0),MATCH($B$1,Sheet2!$B$1:$H$1,0))

WBD

 

[js10053]

Re: Help With A Formula With Multiple Conditions

Thank you both for your replies, I really appreciate it. Unfortunately I wasn't able to get the sendspace file to work for me, so I tried with the Index option and was able to get it to work. However, I'm not sure if this can be done or not, but I thought I would check. Is there any way this option to work when the date is pulled using the =TODAY() formula and for the hour using the drop down list that I created? I have it working if I put the day of the week and time in myself, but anytime I use a number that is automatically pulled it results in an N/A. It won't be the end of the world if it isn't possible, but I figured it would be worth asking to try and make the excel sheet as easy to use as possible.

Thank you again for your help!

 

[wideboydixon]

Re: Help With A Formula With Multiple Conditions

I didn't show it but my formula in B1 was:

=TEXT(TODAY(),"dddd")

WBD

 

[js10053]

Re: Help With A Formula With Multiple Conditions

Thank you again, it seems like that is going to work. I don't have my full version of excel at the moment, I'm just using the extension in chrome so it is acting up. But it appears that it will work.

Is there any way to do this with the time, but not doing =NOW() and using the drop down list, or will I just need to enter the time I need?

Thanks again

 

[wideboydixon]

Re: Help With A Formula With Multiple Conditions

You could trigger setting the time through a macro by setting

Range("B2").Value = DatePart("h", Now)

Or you could use a formula in B2:

=HOUR(NOW())

WBD

 

[js10053]

So I finally go back to my file so I could try everything. The date formula is working, but the hour formula is not. However, whenever I try to get the information to auto generate the index formula comes back with a N/A? I’m not sure if the formula cannot read the results or what the issue is. If I just type the date and time it’s fine. It wouldn’t be the end of the world to do that, but I was just trying to make this as user friendly as possible since I won’t be the only one using it.

 

[js10053]

I was able to work on the formula more and I was able to get the date to work, but I’m still having issues with the time formula pulling just the hour and when I do pull the time I get an error in the index formula. I know I’m probably doing something wrong. But I’m not sure what.

 

[wideboydixon]

What formula are you using for the day of week and the hour? What does your lookup sheet look like? What is your INDEX formula. It's very hard to diagnose a problem without any information ...

WBD