populating a dynamic array based on cell criteria.

[gordsky]

Hi All,

im struggling with arrays and admit I dont fully understand the syntax so any help would be appreciated
We have a spreadsheet with staff name on in columnA and dates accross B2:H2. staff basically put an x in the relevant row/column showing availability for overtime.
The sheet cells are the coloured green for those selected (manually not through a formatting.)

So management then want all people selected for each day pasted onto a different sheet which is then sent to the duty manager for the day.

So I know it can be done (and how to) by filtering and then copy/paste or by looping though each cell in column and pasting to the relevant to new sheet one by one.

Instead of those I would like to loop through the range and create an array (if thats the correct term) to then paste in one go to the new sheet.
The data collected would need to be the name from columnA&relevantrow as well as columnK&relevantrow.

Ive figured out how to create an array for cells meeting the criteria in columnA for a fixed array size (1 to 6) for example but what I figure out is how to do that and take the info from both columns and also for a non fixed size. Can anyone help

 

[shift-del]

Hi

If you would opt out of a crosstabular layout and go go for a list of records then it is just simple filtering - either by name or by date.