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?

 

[skywriter]

Can you post some sample data and the formula you are using?

 

[Madcowz]

			15-Jan-15 (CELL D6)		26
Site and Forman Incharge if applicable	Fuel	Employees	Equipment	EQ.#	Start	Notes
Work location

<tbody>
</tbody>

above is Sheet # 1


Sheet # 2

Name

Working

2-Jan-15

3-Jan-15

4-Jan-15

5-Jan-15

6-Jan-15

7-Jan-15

8-Jan-15

9-Jan-15

10-Jan-15

11-Jan-15

12-Jan-15

13-Jan-15

14-Jan-15

15-Jan-15

16-Jan-15

Employee

1

0

0

0

0

0

0

0

0

W/E

W/E

0

0

0

1

0

0

W/E

W/E

0

W/E

W/E

0

W/E

W/E

<tbody>
</tbody>

This is the formula I used in Excel to link the two sheets, works good. sheet 1 is the daily schedule, sheet 2 is the days worked test sheet..

=IF(AND(EXACT('New daily schedule'!D6,'Days worked test draft'!Q1), EXACT('New daily schedule'!D6,'Days worked test draft'!Q1)),1,0)

 

[Madcowz]

so tomorrow (the 16th) it will show the #1 on sheet 2, but under jan 15th, it will go back to 0
, I need it to save and not change everyday

 

[skywriter]

So I pasted your data where I thought it might go, but you don't have any row or numbers and letters in your post, so pay attention to the formulas I give you and adjust them if needed. I gave you more than one formula so you could see what happens when you copy it. Again make the cell references appropriate for you data if I pasted them in the wrong cells. I am assuming what appear to be dates on your two sheets are actually dates. If they are text you will have problems, by having them as dates you can use the formulas I give you. The reason I state this is because you are using the exact function.

Excel 2010

	M	N	O	P	Q
1	12-Jan-15	13-Jan-15	14-Jan-15	15-Jan-15	16-Jan-15
2	1	1	1	1	0

<tbody>
</tbody>

Days worked test draft​

Worksheet Formulas Cell Formula M2 =IF('Days worked test draft'!M$1<='New daily schedule'!$D$6,1,0) N2 =IF('Days worked test draft'!N$1<='New daily schedule'!$D$6,1,0) O2 =IF('Days worked test draft'!O$1<='New daily schedule'!$D$6,1,0) P2 =IF('Days worked test draft'!P$1<='New daily schedule'!$D$6,1,0) Q2 =IF('Days worked test draft'!Q$1<='New daily schedule'!$D$6,1,0) <tbody> </tbody>

<tbody>
</tbody>

Excel 2010

	B	C	D	E	F
4
5
6			15-Jan-15		26
7	Fuel	Employees	Equipment	EQ.#	Start

<tbody>
</tbody>

New daily schedule
​

 

[Madcowz]

That is fantastic, thank you very much for that, I will give it a go and let you know how it turns out

 

[Madcowz]

I tried the formula and it does work, but does the same as my last formula, as soon as I switch the date, the previous day changes from 1 back to zero

 

[skywriter]

Can you share the workbook, sensitive information removed?
Hard to say what's going on without the ability to see the whole sheet and evaluate formulas.

https://www.dropbox.com

 

[Madcowz]

I have uploaded the file to dropbox, I think I need your email to send it to you, not really sure, haven't used drop box before.

 

[skywriter]

I have uploaded the file to dropbox, I think I need your email to send it to you, not really sure, haven't used drop box before.

Paste a link to it here.
There's a link tool in the toolbar when you are posting. I guess it looks like the planet earth with a couple of chain links below it.