Moving people around

[Get the cans out]

What im trying to do is on one page put a list of people with 1 of 3 letters L,S or N.

Then on the next page put then in the relevant lines.

So for arguments sake in the first title Liquid then in the next amount of lines the people with the l next to them.
then the people with s next to them and finally the ones with n next to them.
but page 2 has to be dynamic as if you change the letter in the first page it has to move up or down the list in the second eg.

page 1

john smith n

page 2

John Smith is in the n list

[FDibbins]

Hi and welcome to the forum

Try this approach. Add a helper column on patient sheet (I used E) and copy this down...
=D7&COUNTIF($D$7:D7,D7)
Then use this in Menu B6, copied down...
=INDEX('Patient List'!$B:$B,MATCH("L"&ROW(A1),'Patient List'!$E:$E,0))
Copy the same thing to B16 abd B26, and then copy down

[Get the cans out]

Thanks.

Tried that but it did do what I wanted it to do so not sure if I did it right?

[Get the cans out]

I only want the liquid to be in the liquid and so on. And if one changes from so N to L it moves to the relevant section. And as I have x amount of entries can I make the section automatically add extra lines?
Also when I change say a N to a L it doesn't move to the liquids.

[FDibbins]

You were almost there You didnt change the L to S in B16 and the L to N in B26, before copying them down.

Will be pulling in additional info in the adjacent columns?

[Get the cans out]

Done that it works now, the only problem is when I say change patient 1 from L to S, patient 22 disappears and B16s answer is #N/A.
B6 to B16 has Persons 1 to 11, now lets say person 20 is not a S anymore but a L, how can I also increase the Liquid Diets to B17 and move the Soft Diets from B17 to B18?

[FDibbins]

If you can add some extra rows between each "section", then you could use this variation, copied down - it will show blank after it finds the last match...
=IFERROR(INDEX('Patient List'!$B:$B,MATCH("L"&ROW(A1),'Patient List'!$E:$E,0)),"")

Again, dont forget to change the "L" to "S" and "N" at the start of each section befor you copy down

[Get the cans out]

Hi,
Did that, it worked. the problem now is if I change patient 1 from l to s or n it puts 2 blank spaces in the first section!

[Get the cans out]

It moves patient 31 up 1 but if more are added the top ones disappear. try changing all the liquid patients to normal and you will see what I mean.

And thanks for your help

[martindwilson]

where do you want them to go?

[Get the cans out]

Hi Martin,
what im trying to do is.

have a list of names lets say 30 people with either a diet of L for liquid, S for soft and N for normal.
Lets say 10 are L.
10 are S.
and 10 are normal.
I have 2 pages 1 gives you a column of the patients with the l, s and n in the next column and the next page depending on there state they are put in the relevant block on the menu sheets the first ten lines are for the liquid the next are for the soft and the last are for normal each block has also a title line.
lets say the first 10 are liquid, the next 10 soft and the last ten normal. that's been done, the problem is if say patient 1 goes from liquid to normal an extra blank line appears in the liquid section from 1 to 2 blank lines. then the normal section moves up a line, if for instances there is 11 lines in normal and you have 10 entries you will have a blank line, yet if a new entry goes in there the blank space disappears and if you happen to put say 3 new entries in that section then the bottom few disappear and not move down.

Does that make sense?

Cheers,

Ian

[martindwilson]

i meant where do you want the overflow to go
you have 10 rows for normal diet in col b of menu sheet
so do you want the rest to go in col c

[Get the cans out]

Sorry see what you mean.
Lets say normal has 10 rows and 9 are filled, If I want a liquid to move back to normal or visa versa the normal column adds a row so it goes from 10 with 9 filled to 11 with 10 filled and that section increases by 1. but you see all the names.

[FDibbins]

self-Deleted post

[Get the cans out]

?
Sorry don't understand, self deleted post

[FDibbins]

Sorry, I dont get that. See below, I have pulled the info into Menu, so you can see all the data together (and I deleted a few rows so that it fits better). Patient 1 is L, and is the 1st name under Liquid...

	B	C	D	E	F
5	Liquid Diet
6	Person 1		Person 1		L
7	Person 2		Person 2		L
8	Person 3		Person 3		L
9	Person 4		Person 4		L
10	Person 5
11	Person 6
12	Soft Diet
13	Person 12
14	Person 13
15	Person 14
16	Person 15
17	Person 16
18	Normal Diet
19	Person 23
20	Person 24
21	Person 25
22	Person 26
23	Person 27

Next, I changed Patent 1 from L to S, patent 1 is now the 1st name under S, and patent 2 is the 1st name under L

	B	C	D	E	F
5	Liquid Diet
6	Person 2		Person 1		S
7	Person 3		Person 2		L
8	Person 4		Person 3		L
9	Person 5		Person 4		L
10	Person 6
11	Person 7
12	Soft Diet
13	Person 1
14	Person 12
15	Person 13
16	Person 14
17	Person 15
18	Normal Diet
19	Person 23
20	Person 24
21	Person 25
22	Person 26
23	Person 27

[FDibbins]

?
Sorry don't understand, self deleted post

I meant, I deleted my own post. I posted something, then decided to change it

[martindwilson]

that wont happen automatically,you cant add rows with a formula

[Get the cans out]

The menu page has to stay the same as mine cos they will be printed out. the patients will be getting a menu choice and I have to write there choice next to there name.

[martindwilson]

you can use a pivot table see attached when you change the menu status refresh the pivot