Newbie needing help copying/keeping weekly data

mlamew66

New member
Joined
Apr 22, 2019
Messages
9
Reaction score
0
Points
0
Excel Version(s)
2003
Hi All, I am fairly new to excel. I have taken on a project and need a little help.

I have created 2 cover sheet that are filled in weekly with data, printed and then filled in according to the next employees weekly data. It contains drop-downs, shipment info and several formulas to do the calculations. I have figured out how to use the IF AND statement to move the data I need to other sheet(s) within the same workbook). When I go to enter the next weeks data the info from the previous week changes. Is there a way to keep the data on the 2[SUP]nd[/SUP] sheet when I input the next weeks data?

Here is a summary of what I have:
Sheet 1:
Weekending dates are in cells E19 and S19
Names are in D25 and R25 (these are drop-downs)
Totals are in L49 and Z49

Sheet 2
I have created 3 columns for each employee

  • Weekending
  • Total
  • Holiday pay

I use an IF AND statement to bring the data into Sheet 2 and it works fine, but when I go to input the next weeks data on the cover sheet the 2[SUP]nd[/SUP] sheet does not have the old data there.

How can I get the data to stay in Sheet 2 when reusing sheet one for each different employee and different Weekending dates? Or is this possible?

Sorry if this is a lot to understand.

Hope someone can help me.

Using excel 2016 saving as macro-enabled (needs to be opened on a 2003 version)/Windows
 
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.


Remember to desensitize the data.
 
Ok, while desentizing I had a brainstorm, to make things easier. In the long run I would like to be able to pull as much data from these cover-sheets to go into other sheets/workbooks. It may take me a day or so.
 
Brainstorm lead to a small disaster, but reconstructed enough to get my point across (I hope).

Legend:
Sheets:
Payroll = the actual sheet that I will reuse over and over again, changing the driver and weekending each week)

Week 2 is just to show if data needs to be entered into the “1 ticket” section (I will use either the 1 ticket or 2, never both in one week

Week 3 to show a 2[SUP]nd[/SUP] set of entries using a different “weekending”

Hol-Pay is where I am copying the data to


  • Column A is the weekending date, I automatically generated these dates and they will need to match either cell E19 or S19
  • Column B is weekly pay this number will be come from cells L49 or Z49
  • Column C is holiday pay I have highlighted the pays that will have Holiday pay on them and inserted a formula to calculate it (that was an easy one)

Here is the formula I used to get Hol-Pay column B data
=IF(AND(Payroll!$D$25="Driver 1",Payroll!$E$19=$A5),+Payroll!L49,"")

I have 2 questions:


  1. How do I alter this formula in case I am using the “1 Ticket section meaning the highlighted cells would change. I know how to change the driver one.
Most importantly how do I get this data to stay when I go to enter next weeks data? If possible.
 

Attachments

  • brainstorm.xlsm
    40.8 KB · Views: 7
Since I am trying to learn on my own and not bother all of you guys I tried something and I think it will solve my first issue. I changed the "" at the end to the S49 cell. It seems to work. So now I just need help keeping the data in the cells when the "Weekendng" dates change.

mlamew66
 
Disregard the last post - it did not work.
 
Back
Top