12 dependent series

[Elton Law]

Dear expert,
Would like to prepare the shift table.
Say I put 1 in cell A1
A2 to A13 will show up like this ...

Timmy
James
Patrick
Terence
Dion
Jasmine
Kammi
Steve
Ricky
Kelly
Fiona
Peggy

Say I put 2 in cell A1.

A2 to A13 will come up like this.

Terence
Dion
Jasmine
Timmy
James
Patrick
Kammi
Fiona
Peggy
Kelly
Steve
Ricky

It is hard to use "IF", becasue there are more than 7 choices ....
VB can definitely do, but I would like to have a try on worksheet function
first.

Thanks, Elton

 

[Tom Hutchins]

Here is one way...

On another sheet (Sheet3 in my example), put the shift 1 names in A2:A13.
Put the shift 2 names in B2:B13. On the main sheet (where you will specify 1
or 2 in A1), enter this in A2 and copy down through A13:

=INDIRECT(ADDRESS(ROW(),$A$1,,,"Sheet3"))

Enter 1 or 2 in A1. Hope this helps,

Hutch

 

[Teethless mama]

select A2:A13 then enter the formula below

=CHOOSE(A1,range1,range2)

ctrl+shift+enter, not just enter

 

[Elton Law]

Hi Tom,

Actaully, I have 12 shifts and and now I show up 6 ...
I have put in sheet called "Raw"
Raw sheet cell A1 to L1 is 1 to 12
Raw sheet cell A2 to L13 are the names ...

Previous sheet will have the criteria ... address is DJ25

Then I replace to

=INDIRECT(ADDRESS(ROW(),$DJ$25,,,"Raw"))

It does not work .... can you advise?
DJ25 can be number 1 to 12 and relates to data series under sheet "raw"

Thanks

1 2 3 4 5
6
Timmy Timmy Terence Dion Steve Steve
James James Dion Jasmine Ricky Ricky
Patrick Patrick Jasmine Timmy Kelly Kelly
Terence Timmy Timmy James Fiona James
Dion James James Patrick Peggy Patrick
Jasmine Patrick Patrick Kammi Timmy Terence
Kammi Kammi Kammi Fiona James Dion
Steve Steve Fiona Peggy Patrick Jasmine
Ricky Ricky Peggy Kelly Terence Kammi
Kelly Kelly Kelly Steve Dion Fiona
Fiona Fiona Steve Ricky Jasmine Peggy
Peggy Peggy Ricky Terence Kammi Timmy

 

[Jacob Skaria]

With data in sheet Raw; and number in DJ25 try the below formula in a cell
and copy down as required

=OFFSET(Raw!$A$1,ROW(A1),$DJ$25-1)

If this post helps click Yes

 

[Elton Law]

Hi Jacob,
Thanks indeed. It works welllllll. Thanks always.