How to formulate OFFSET function for every 6th column in different worksheets?
I've been struggling from last few days and this is my first post here.
Actually I am working on one excel report where in one tab of that report I have got the historical data of all the weekdays where all metrics are in column A and the heading has got the dates only (Starting from column B5 onwards). And I want to create all MONDAYS, TUESDAYS etc report from this historical report in the same workbook but in separate tabs .
So for: B5, C5, D5, E5, F5 (G5 column is used to separate the weeks)
H5, I5, J5, K5, L5 (M5 column is again used to separate the week) and so on in History tab
I just need B5, H5, N5, T5 etc columns to create Monday report in tab name Mon
Similarly for Tue (C5, I5 etc), Wed (D5, J5 etc) as well.
Is there a formula that I can drag across all columns? Because basically I need every 6th column to create Mondays, Tuesdays etc reports. I tried offset, columns and indirect function but struggling a lot.
MANY THANKS,
GK
Hi G Kaur, Can you use the
Hi G Kaur,
Can you use the index function for this?
so in sheet Monday you'd have in B5
=index(Sheet1!$A:$XFD,5,2)
in C5 you'd then use the following:
=index(Sheet1!$A:$XFD,row(),Column()+5)
Then copy this second formula along and it should bring you only Mondays
Adjust for different tabs and days of weeks.
Regards
Mark