How to autopopulate columns on one sheet from a data range on another.

Morning all.

What I'm looking at doing is taking a data range that consists of rows of names and columns of week numbers with each week of the year holding a value for if that name has achieved a target, say a 1 or 0. so along these lines..

	WK1	WK2	WK3	WK4
Andy	1	0	0	1
Bob	0	0	0	1
Charlie	0	1	1	0

What I'm then looking to do is then generate a report from this that just pulls the data from that range to populate columns that show the results of the last 4 weeks, but have those columns automatically update as the week number increments so that only the data range needs updating and noone needs to touch anything else. ie. in a week 20 it'll show the weeks 15-19 etc.

Bad news is work only has an archaic version of Office 97 on hand which it would need to be compatible with. In principal this seems like it ought to be a fairly simple thing to do, I'm thinking something VLOOKUP related, but maybe I'm being overly hopeful..

TIA

Hi,

Welcome to the Forum.

I have created a template based on the description provided by you. Please see if this suits your requirement (file attached). I have used INDEX & MATCH functions to achieve the desired result.


Regards,
Chandra


Please click on ‘* Add Reputation’ if this was helpful

cbatrody,

Thanks, that does the job just fine and I can expand on that from there. I thought there'd be a relatively simple way to do it but I'm not familiar enough with Excels command list at present. Rep added!


sktneer, 97 wouldn't load your file up unfortunately, something broke when it tried to convert it but thankyou anyway for the attempt to help.


Regards