My brain won't work!

[adambc]

I have 2 worksheets, 1 with a list of names, the other with a table of those names (4 to a row) ...

I want to ALWAYS have the first 4 names on the list (WS1) on the first row (WS2), names 5-8 on the second row, etc ...

But I also want to be able to CUT/paste the list of names, but ='LIST OF NAMES'!B19 doesn't let me do that ...

Have tried adding $s, but just can't get it right!!!

Thanks for your help (and patience) ...

 

[jimrward]

assuming your list of names starts in 'ListOfNames'!A1 try the following in your table sheet!A1

=OFFSET(ListOfNames!$A$1,(((ROW()-1)*4)-1+COLUMN()),0)
drag across to D1, then drag down do it for a few lines only then try playing with the list of names, sort, cut paste etc to see if you get any reference errors

the ROW and COLUMN functions are used to convert into a sequential number
1,2,3,4
5,6,7,8 etc

 

[adambc]

@jimward ... apologies for the delayed response - I've had to divert my attention to something else for the past 6 weeks!!!

Many thanks for your OFFSET formula which I have got working ... sort of!

My list of names starts in ListOfNames!$B$19 down to ListOfNames!$B$70 - so I've changed your string to =OFFSET(ListOfNames!$B$19,(((ROW()-1)*4)-1+COLUMN()),0) ...

If I then use that string in TableOfNames!A1 (drag A1 across to D1 and drag A1:D1 down) I get the TableOfNames I want (haven't tried doing any sort/cut/copy/paste my ListOfNames yet) ...

But ... I want the TableOfNames to start in TableOfNames!B16 (to TableOfNames!E28) - and that's where things go awry ...

Do I need another "OFFSET" in there to do that?

Many thanks ...

 

[adambc]

@jimward ... IGNORE MY PREVIOUS POST

Have changed the string to =OFFSET(INPUTHERE!$B$19,(((ROW()-16)*4)-1+COLUMN()-1),0) and I'm back on track ...

Will now test the sort/cut/copy/paste of ListOfNames ...

Thanks again ...

 

[adambc]

@jimward ... hhhmmm whilst sort is OK, cut/paste throws up a #REF! - and it's cut/paste I need ...

I think (?) it's because when I cut/paste, the OFFSET formula still doesn't keep the $B$19 reference ...

Any other ideas?

Thanks ...