Automate 'Previous Week Update' information from one sheet to another....options.

Christopher Tirro

Trying to find an automated way to pull data from one cell to another so I can then reference it as 'Previous Week' back on the main sheet. For example: Sheet 1 will have a column named 'Key Activities Completed this Week'. I'd like that information weekly to be copied to another sheet. I'll then reference this cell in the main sheet as 'Previous Week Activities Completed'. Since we can't automate copying a cell value within the same sheet. If there's anyone who has a better way of doing this I'd appreciate the insight!

L_123

you can use index(match()) as your formula, then you just need to figure out what your reference is. You can have an autonumber and reference the max of the autonumber on the archive, or calculate the previous week from the date column.

Christopher Tirro

Each Row will be a project that teams are updating weekly - so I think my reference is the project title or the ID #. They receive update requests Friday so if someone makes an update that's when I'd like the 'previous week' information to be copied to the 'previous week' cells.

L_123

Can you share a screenshot of the 2 sheets so I can better understand? I think I might have not fully followed what you're asking for.

Christopher Tirro

Sure - and let maybe describe it better as well -

I manage about 14 projects that I ask Accountable parties to make weekly updates via an automated request. When they make these updates I've created an automation to create a summary PDF of the current updates and I'd like this to include the previous weeks updates as well.

The only way I can think to do this is to have the Current week updates copied over to a Previous Week column. During my research on how to do this I discovered that really can't be done in an automated way - so I was trying to create a helper sheet to reference the previous week's updates.

If there's an easier way I'm open to it of course.

Christopher Tirro

I had to hide some info. But this is ONE sheet. I was hoping to keep current and previous in the same sheet so I can generate the PDF.

L_123

I would use Index(Collect(),1).

So it would look something like:

=index(collect({Where do you need help},{Milestone},[Previous Week - Next Milestone Date - 1]@row),1)

Where you would need to build out your other sheet references to point to the correct columns

This can be used in conjunction with the copy workflow to the other sheet, then simply ignore the other sheet unless you need historical information

Christopher Tirro

I'm not sure I follow - Let me see if I can ask a few questions and get aligned:

• For my understanding I'm going to reference 'Sheet 1' as the sheet that contains the most recent updates from Project Leads.
• 'Sheet 2' would be the sheet I'm using to copy over the current week information and then use it as reference to 'Previous Week' on 'Sheet 1'
• So not sure where each formula should be and how they reference eachother

L_123

Yes, you would use your copy workflow to move the row to your archive. Then you would reference the archive to move the information back. I think the best way is to run the archive once manually, then look at it. Then you can know what column your desired information is in. Reference that into your target cell on the main sheet. The only formulas should be on the main sheet in this case.