|
12 dependent series
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 |
12 dependent series
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 "Elton Law" wrote: 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 |
12 dependent series
select A2:A13 then enter the formula below
=CHOOSE(A1,range1,range2) ctrl+shift+enter, not just enter "Elton Law" wrote: 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 |
12 dependent series
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 "Tom Hutchins" wrote: 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 "Elton Law" wrote: 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 |
12 dependent series
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 --------------- Jacob Skaria "Elton Law" wrote: 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 "Tom Hutchins" wrote: 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 "Elton Law" wrote: 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 |
12 dependent series
Hi Jacob,
Thanks indeed. It works welllllll. Thanks always. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Elton Law" wrote: 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 "Tom Hutchins" wrote: 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 "Elton Law" wrote: 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 |
| All times are GMT +1. The time now is 01:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com