Access and Excel

[Madcowz]

Hello all.
Hopefully I can explain and ask this question correctly... So I have finally figured out my Excel spread sheet problems, almost anyways. So right now I have my sheets sheet up to return a value under a specific date. For example, I have an employee booked to work on my daily schedule (Sheet 1)for Jan 15. On sheet2, I have the days of the month across the top (cells A1 to AG1), employee name from cell B1 to B52. So when I plug my worker in to a spot on Sheet1, it automatically input "worked" under the Jan 15th cell on sheet 2 and it works great . My only problem is, on Jan 16th, it will show that the employee has worked in the Jan16th cell, but the jan 15th cell now shows he was off, basically not saving the previous days info. So my question is, Is this where creating an Acess database would come in handy, so that my previous days info will be saved and not reset?

 

[Madcowz]

https://www.dropbox.com/s/03pvaaepfv0cyrq/Test%20Schedule%20Template.xlsx?dl=0

 

[skywriter]

https://www.dropbox.com/s/03pvaaepfv0cyrq/Test Schedule Template.xlsx?dl=0

This is the formula you have in your spreadsheet.
=IF('Days worked test draft'!Q$1='New daily schedule'!$D$6,1,0)

Look up at the formula I gave you in Post#5, it has <= which is key to making this formula work. Your formula above only has = fix this and you will be fine.

 

[Madcowz]

Wow, I can't believe I missed that, it works great. Thanks very much. Is there any way I can splice this formula in =COUNTIFS('New daily - Table 1 - Guelph'!C13:C83,"=John Doe "), this puts the # 1 in cell c2 on the day's worked sheet, if the employee worked, or a 0 if they are off. Ultimately I would like it to automatically show a 1 for worked or for not worked under the dates on the days worked sheet. so when I move any employee on the daily schedule into cell C13 to C83, it then matches the date like the formula you gave me, and inputs 1 or 0 under the correct date. The formula I listed does it, but on in Column C on the days worked sheet.

 

[Madcowz]

This formula works for me.....=IF(AND(C2:C2<=1,1),IF('Days worked test draft'!X$1<='New daily schedule'!$D$6,1,0))

Does that make sense, seems to do what I need it to

 

[Madcowz]

Never mind the above doesn't work correctly

 

[Madcowz]

This formula works for me.....=IF(AND(C2:C2<=1,1),IF('Days worked test draft'!X$1<='New daily schedule'!$D$6,1,0))

Does that make sense, ?

I am finding that the above formula works in some cells but not others, any ideas what causes that?. Thinking I may have to start all over again, seems to work one time, then it doesn't, very frustrating

 

[skywriter]

This formula works for me.....=IF(AND(C2:C2<=1,1),IF('Days worked test draft'!X$1<='New daily schedule'!$D$6,1,0))

Does that make sense, ?

I am finding that the above formula works in some cells but not others, any ideas what causes that?. Thinking I may have to start all over again, seems to work one time, then it doesn't, very frustrating

Make sure this formula is on the sheet you are sharing on drop box and make some notes on that sheet regarding what you want it to do and I'll look at it.

 

[Madcowz]

I added a note on the 2nd sheet, thanks for the help

https://www.dropbox.com/s/03pvaaepfv0cyrq/Test%20Schedule%20Template.xlsx?dl=0

 

[skywriter]

Here's you formula in D1

=IF(AND(C10:C10=1,1),IF('Days worked test draft'!X$1<='New daily schedule'!$D$6,1,0))

Let's break it down so you can better understand what's going on.

With the AND function all logical tests within the AND function must evaluate to TRUE for the AND function to evaluate to TRUE.

AND(C10:C10=1,1) doesn't make sense.
You are asking the AND function to evaluate two arguments.
#1 Does C10:C10 = 1, which by the way should just be written C10, no need for the other C10. In this case C10 does indeed have a 1 in it so that part of the AND function evaluates to TRUE.
#2 You are then asking the AND function to evaluate the number 1. In Excel any number besides 0 is considered a TRUE. So I don't know why you have the number 1 as the second logical test for AND, but since you are giving it a number other than 0 as the argument it evalutes to TRUE.

Both arguments in the AND function evaluate to TRUE so the AND function evaluates to TRUE.

Now lets look at the IF function.
The IF function works this way. I want to evaluate something and if it's true do this otherwise if it's false I want to do something else.

Example:
In A1 i have the word "APPLE"
In B1 i have the formula =IF(A1="APPLE","FRUIT","NOT APPLE")

In plain English I'm saying if cell A1 has the word "APPLE" in it then cell B1 where the formula is will have the word "FRUIT" in it. If cell A1 has the word "ORANGE" in it cell B1 will return "NOT APPLE". I know and orange is a fruit but the formula is looking for the word "APPLE", anything else is false and we get "NOT APPLE".

Your formula works like this. If the AND part is true which it is, then it goes to the next part of the formula which is another IF function.

If the next if function evaluates to true you are asking it to return the number 1, if it evaluates to false you are asking it to return the number 0.

If the AND part of the first IF function had evaluated to false you would have received the word "FALSE" as a result of your formula because you didn't specifically tell the first IF function what to return if it evaluates to false.

The problem with the second if function is that you are looking at cell X1 not D1, the value in X1 January 22, 2015, so that If function is evaluating correctly to false.

 

[skywriter]

The first line of the post above should read formula in D10, not D1.

Regarding Cell C6:
Instead of typing "John Doe" in the formula, if you are going to have John Doe entered in B6 then just reference cell B6 in your formula.
=COUNTIF('New daily schedule'!C8:C89,B6)

Regarding Cell D6:
If you want it to check whether cell C6 shows they were working then the formula would be.
=IF(AND($C6,'Days worked test draft'!D$1<='New daily schedule'!$D$6),1,0)
This can be copied up to the cells above and those below and then copied right into the adjacent columns.

Good Luck.

Any more help needed, don't hesitate to ask.
Just please carefully look at the formulas for references to other cells that might be wrong and please be very clear in your notes as to what you are doing. Make sure to give me some for instances. For instance in Cell whatever I was hoping it would look at this cell and if the value is this do this.