Hope someone can help with this, I have a workbook allocating men to machines. The details are in a range 3Cx9R for each rota and there are 9 variations of the rota in named ranges rota a, rota b, rota c, etc
What I need to be able to do is read a letter from a cell and replace the current rota with one corresponding to the new letter for example rota a this week, rota b next week.

Hope this makes sense, let me know if more information would be helpful

Thanks in anticipation of a solution

Martin

Think you could use a simple multi-cell array formula to do this easily

Here's a sample to illustrate:
http://www.freefilehosting.net/download/3a1c7
Rota selection.xls

Assuming you have 3 defined ranges, viz:
rota_a =Sheet1!$A$1:$C$9
rota_b =Sheet1!$A$11:$C$19
rota_c =Sheet1!$A$21:$C$29

In another sheet,

With A1 containing a data validation* list to allow
the easy selection of the 3 defined ranges: rota_a, rota_b, etc

Select a 3C x 9R range, say C2:E10
Paste into the formula bar:
=IF(A1="","",INDIRECT(A1))
Press CTRL+SHIFT+ENTER to confirm the formula (ie "array-enter")
C2:E10 will return the contents of the defined range selected in A1

*one way to create the dv:
Select A1, click Data Validation
Allow: List
Source: rota_a, rota_b, rota_c
Click OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Martin B" wrote in message
...
Hope someone can help with this, I have a workbook allocating men to
machines. The details are in a range 3Cx9R for each rota and there are 9
variations of the rota in named ranges rota a, rota b, rota c, etc
What I need to be able to do is read a letter from a cell and replace the
current rota with one corresponding to the new letter for example rota a
this week, rota b next week.

Hope this makes sense, let me know if more information would be helpful

Thanks in anticipation of a solution

Martin

Thanks Max, played around with your formula a bit but it's done the trick
nicely

Have a great 2008

Martin

"Max" wrote in message
...
Think you could use a simple multi-cell array formula to do this easily

Here's a sample to illustrate:
http://www.freefilehosting.net/download/3a1c7
Rota selection.xls

Assuming you have 3 defined ranges, viz:
rota_a =Sheet1!$A$1:$C$9
rota_b =Sheet1!$A$11:$C$19
rota_c =Sheet1!$A$21:$C$29

In another sheet,

With A1 containing a data validation* list to allow
the easy selection of the 3 defined ranges: rota_a, rota_b, etc

Select a 3C x 9R range, say C2:E10
Paste into the formula bar:
=IF(A1="","",INDIRECT(A1))
Press CTRL+SHIFT+ENTER to confirm the formula (ie "array-enter")
C2:E10 will return the contents of the defined range selected in A1

*one way to create the dv:
Select A1, click Data Validation
Allow: List
Source: rota_a, rota_b, rota_c
Click OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Martin B" wrote in message
...
Hope someone can help with this, I have a workbook allocating men to
machines. The details are in a range 3Cx9R for each rota and there are 9
variations of the rota in named ranges rota a, rota b, rota c, etc
What I need to be able to do is read a letter from a cell and replace the
current rota with one corresponding to the new letter for example rota a
this week, rota b next week.

Hope this makes sense, let me know if more information would be helpful

Thanks in anticipation of a solution

Martin

Welcome, Martin
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Martin B" wrote in message
...
Thanks Max, played around with your formula a bit but it's done the trick
nicely

Have a great 2008

Martin