Are there any Excel geeks around??

[Drewboy22]

So here is my dilemma...

I have a spreadsheet for payroll. It consists of 3 contracts - 1 spreadsheet for each contract.
Each tap is labeled

- 7198
- 7202
- 18224

I am working on tap 4 now and making a "Tally" sheet. The tally sheet looks like this

Name | 7198 | 7202 | 18224 | Total Hours | Overtime Hours


What I am trying to do is this - I want the tally sheet to auto populate when I fill in the other spreadsheets. The easy part I was able to accomplish.

Ex.
Name______ | 7198 | 7202 | 18224 | Total Hours | Overtime Hours
Drewboy22__|__0__|_40__|______|____40____|_____0__ ____

To come up with the value for my contract (40 hours) I used "=7202!I6" and an easy "if, then" statement for the OT hours "=IF(E3>40,(E3-40)," ")"

Now lets say I worked some overtime on contract 7198. I would go to the contract tab 7198 and fill in the hours I worked by filling in the next available slot with my info.

How do I get that info to auto populate into the cell above for contract 7198. I tried this formula and it didnt work

=if((7198!A15=Harris, Andrew),=7198!I15," ")

A15 being the cell with the employees name in it and I15 being the cell with the total weekly hours for that employee on that contract.

Anyone knwo what I am doing wrong?

Thanks

[Razorback01]

Copy the cell you want to carry to the other tab, and when you paste to the other tab use paste and link- chain looking paste.

[Razorback01]

Or create a pivot table, just don't ask me how to build a pivot.

[Tmag]

If I am not missing something here, go to the cell that you want to populate on the tally page and hit = then move to the 7198 sheet cell and hit enter. What ever you put in the cell on the 7198 will also go to the tally sheet.
I use this method weekly doing my IFTA mileage reports.

Had to correct, had it backwards.

[Outlaw_6]

=if((7198!A15=Harris, Andrew),=7198!I15," ")

A15 being the cell with the employees name in it and I15 being the cell with the total weekly hours for that employee on that contract.

Anyone knwo what I am doing wrong?

Thanks

Put "Harris, Andrew" inside quotes in the formula above and see if that doesn't fix your problem.

[Felix40]

Try putting quotes around the employee name in your "if" statement. Im having a hard time following but Ill see if I can figure it out.

Edit:too slow

[SB09]

I just checked our excel sheets that are linked the same way you're wanting yours to. The above posts are exactly how ours are setup. It's got the name of the tab it's pulling in ' ' from followed by an !. So, for instance it looks like.... 'ESTIMATE SHEET 27'!N2137

[Drewboy22]

Put "Harris, Andrew" inside quotes in the formula above and see if that doesn't fix your problem.

BOOM! Winner Winner Chicken Dinner!! Thanks!


Thanks to all for trying ! The Quotes is what was missing!!

[Drewboy22]

Now that we got that figured out I was able to nest them together so that if the employees name showed up on any of the 3 open slots for contract 7198 it would auto populate the hours worked for that employee on that contract. See below!!

=IF('7198'!A28="Harris, Andrew",'7198'!I30,IF('7198'!A31="Harris, Andrew",'7198'!I33,IF('7198'!A34="Harris, Andrew",'7198'!I34," ")))

YEAH!!!