Apr 08 2021 07:07 AM
I need to insert the data from the latest 2 weeks (columns N-S) from the Index sheet into the Master sheet against the ID numbers.
The formula will need to include the heading names so when a new week column is inserted and renamed 'Notes for W/C 15th March 2021' and 'w/c 15/03' these will be dragged through to the Master sheet.
Only the latest 2 weeks data will be picked up at all times!
Please can someone help? I have attached the document.
Apr 08 2021 07:50 AM
See the attached version. It uses the INDIRECT function to prevent inserting/shifting columns from messing with the formulas.
Apr 08 2021 08:04 AM
Apr 08 2021 08:11 AM
Apr 08 2021 08:38 AM
My apologies, I see that I messed up. I had formulas for the column headers, but somehow removed them again. And the formulas were based on an incorrect assumption.
Here is a new version.
Apr 08 2021 09:18 AM
Apr 08 2021 11:35 AM
SolutionSee if this works better.
Apr 09 2021 01:46 AM
Apr 09 2021 04:58 AM
Hi Hans.
I have an issue in the example you provided.
When a new column is copied and pasted in the Index sheet for a new week, the rows will be cleared down for the user to edit again in the week.
The formulas in the Master sheet need to be static so reflect the last 2 columns on the Index sheet.
I have attached the example again where I have added the new column however the blank cells are not pulled through to the Master sheet.
Please can you help?
Apr 09 2021 05:20 AM
The formulas in the previous versions assumed that there wouldn't be blank cells, so they return incorrect results if there are blank cells.
The attached version takes a different approach. I created a defined name called LastCol that returns the number of the last used column. All the formulas use LastCol.
Apr 09 2021 08:04 AM
Apr 09 2021 10:53 AM
To see the definition of LastCol, click Name Manager on the Formulas tab of the ribbon.
Select LastCol, and you'll see its formula in the Refers to box.
Apr 12 2021 02:01 AM
Apr 08 2021 11:35 AM
Solution