extract data from multiple columns and rows to make a single list

I've set this up for you in the attached file.

I've used the grey columns in your Ward List sheet as helper columns, with this formula in A4:

=IF(G4<>"",MAX(A$3:A3)+1,"")

This can be copied across into O4, AC4 and AQ4, and then copied down to row 38, but delete the formulae in A20:A22 and O20:O22 to avoid the repeated headings. This formula generates a unique sequential number where there is a dietary requirement specified, but we want this number to increase for each block of data. Consequently, I have also used this formula in O3:

=MAX(A:A)

which will give the starting point for column O as the largest number that had been reached in column A. I have coloured this white for clarity, and then this can be copied into AC3 and AQ3 (I've also put zero in A3, for completeness).

I've used column K in the Meal Register sheet as another helper, with this (long) formula in K4:

=IF(COUNTIF('WARD LIST'!A:A,ROWS($1:1)),"1_"&MATCH(ROWS($1:1),'WARD LIST'!A:A,0),IF(COUNTIF('WARD LIST'!O:O,ROWS($1:1)),"2_"&MATCH(ROWS($1:1),'WARD LIST'!O:O,0),IF(COUNTIF('WARD LIST'!AC:AC,ROWS($1:1)),"3_"&MATCH(ROWS($1:1),'WARD LIST'!AC:AC,0),IF(COUNTIF('WARD LIST'!AQ:AQ,ROWS($1:1)),"4_"&MATCH(ROWS($1:1),'WARD LIST'!AQ:AQ,0),"-"))))

It will return the block number and the row in that block where those sequential numbers occur, separated by an underscore. Copy this down to the bottom of your data.

The Ward data can be retrieved using this formula in A4:

=IF(OR($K4="",$K4="-"),"",INDEX('WARD LIST'!$B$1:$BD$38,RIGHT($K4,LEN($K4)-FIND("_",$K4)),14*(LEFT($K4)-1)+COLUMNS($A:A)))

This can be copied into B4, and both formulae copied down as required.

The Dietary data uses a slightly simpler formula in D4:

=IF(OR($K4="",$K4="-"),"",INDEX('WARD LIST'!$B$1:$BD$38,RIGHT($K4,LEN($K4)-FIND("_",$K4)),14*(LEFT($K4)-1)+6))

as this does not follow the same sequence of columns.

Hope this helps.

Pete

Hi, I interpreted the ask slightly differently to Bo_Ry i.e. presumed active where no discharge date.

To address current layout I used a key [RC co-ordinate] for subsequent index calls - but also AGGREGATE dependent.

Hi biex, I don't seem to be able to download the file.

Thank you everyone, for your much-appreciated assistance. Before this post I naively thought I knew a bit about excel - I would have classified myself as an intermediate user. After looking at your responses, I realise now that I know very little and need to do a lot of work on my excel skills. I am trying to understand the formulas you have used and might need to seek your assistance if I can't fathom it out

Thank you once again for all your help.

You're welcome - thanks for the rep.

We are all here to help, so feel free to post back with any further queries that you might have about the solutions offered.

Pete