Insert the latest 6 columns into a different sheet using a vlookup on the ID

[milo1234]

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.

[#2]

@milo1234 

See the attached version. It uses the INDIRECT function to prevent inserting/shifting columns from messing with the formulas.

[#3]

Thanks Hans Vogelaar.
Is there a way to pull through the column headings so these are formulas and not actual text?

[#4]

Also when I insert new columns this is not pulled through to the Master sheet

[#5]

@milo1234 

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.

[#6]

Thank you so much Hans - I appreciate this!

The only issue I have is that the formula is not referencing the ID column (column B in Master).
The order will be different to the Index IDs so they will need to do a lookup to the ID and then populate the data into the cells.

Thank you again for your help!

[#7 · Accepted solution]

@milo1234 

See if this works better.

[#8]

Thank you so much Hans! You are a life saver.
Would you be able to look at my other issue on the discussion page?
It's regarding a concatenate formula.

Thank you again, I appreciate this!

[#9]

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?

@Hans Vogelaar 

[#10]

@milo1234 

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.

[#11]

Thanks Hans
Where have you created a define named called LastCol?
Do I need to rename any columns?

[#12]

@milo1234 

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.

[#13]

Thank you so much!!