Hi

I have a spreadsheet which shows a name in Column A.

The other columns are headed up with different dates in July.

Across the row, under the relevant date, appears the letter A or C or R.
This may appear several times across the row.

This shows which date that person will be able to perform a certain task.

On a separate sheet, I want to show the date of the night before, as that
person may need over-night accommodaiton in order to perform the task.

So, if Fred Smith is performing task R on July 4, 5 & 6, he may need
overnight accommodation on the nights of July 3, 4 & 5.

So Sheet 2 will need to look across that person's row on Sheet 1 for all
occurences of A, R or C; look up the date at the top of the column, then
return that date minus 1 however mnay times the A, C or R has appeared on
Sheet 1.

I hope this explains.

Can anyone offer a solution, please?

On May 2, 12:16 pm, bollard wrote:
Hi

I have a spreadsheet which shows a name in Column A.

The other columns are headed up with different dates in July.

Across the row, under the relevant date, appears the letter A or C or R.
This may appear several times across the row.

This shows which date that person will be able to perform a certain task.

On a separate sheet, I want to show the date of the night before, as that
person may need over-night accommodaiton in order to perform the task.

So, if Fred Smith is performing task R on July 4, 5 & 6, he may need
overnight accommodation on the nights of July 3, 4 & 5.

So Sheet 2 will need to look across that person's row on Sheet 1 for all
occurences of A, R or C; look up the date at the top of the column, then
return that date minus 1 however mnay times the A, C or R has appeared on
Sheet 1.

I hope this explains.

Can anyone offer a solution, please?

Assumptions:
In Sheet1, your table occupies cells:A1:AF10. Date headers in B1:AF1.
Names in A1:A10. A/C/R in B2:AF10.
In Sheet2, you enter a name in A1 and the labels A, C and R in B1:D1.

In Sheet2!B2:
=INDEX(Sheet1!$B$1:$AF$1,MATCH(B$1,INDEX(Sheet1!$B $1:$AF$10,MATCH($A
$1,Sheet1!$A$1:$A$10,0),0),0))-1
copy across until D2.

In Sheet2!B3 (this is an *array* formula, hence commit with Ctrl+Shift
+Enter):
=INDEX(Sheet1!$B$1:$AF$1,MATCH(1,(INDEX(Sheet1!$B$ 1:$AF$10,MATCH($A
$1,Sheet1!$A$1:$A$10,0),0)=B$1)*(COUNTIF(B$2:B2,Sh eet1!$B$1:$AF
$1-1)=0),0))-1
copy across and down.

This will produce three columns with the dates for each task as you
want them. After the list for each column is exhausted you will be
getting error values. You can perform conditional formatting in Sheet2!
B2:D10:

Select Sheet2!B2. Format | Conditional Formatting.
Choose Formula Is: =ISERROR(B2) (remove $$ that Excel might put in the
reference). Choose a white font.

HTH
Kostis Vezerides

Hello

Thank you for what looks like a large piece of work!

One of your assumptions is not quite as I'd intended, though:

On Sheet 2, I wanted to enter just the name in A1 (same order as on Sheet 1)
and have the sheet fill in the various dates across B1,C1,D1 etc... Whether
the person is an A, C or R is not important, just the dates they need
accommodation. This could be anything from 0 days through to a maximum of 35
day in theory. I'm not sure how this will affect the formulas you've given me.

Thanks again.

"vezerid" wrote:

On May 2, 12:16 pm, bollard wrote:
Hi

I have a spreadsheet which shows a name in Column A.

The other columns are headed up with different dates in July.

Across the row, under the relevant date, appears the letter A or C or R.
This may appear several times across the row.

This shows which date that person will be able to perform a certain task.

On a separate sheet, I want to show the date of the night before, as that
person may need over-night accommodaiton in order to perform the task.

So, if Fred Smith is performing task R on July 4, 5 & 6, he may need
overnight accommodation on the nights of July 3, 4 & 5.

So Sheet 2 will need to look across that person's row on Sheet 1 for all
occurences of A, R or C; look up the date at the top of the column, then
return that date minus 1 however mnay times the A, C or R has appeared on
Sheet 1.

I hope this explains.

Can anyone offer a solution, please?

Assumptions:
In Sheet1, your table occupies cells:A1:AF10. Date headers in B1:AF1.
Names in A1:A10. A/C/R in B2:AF10.
In Sheet2, you enter a name in A1 and the labels A, C and R in B1:D1.

In Sheet2!B2:
=INDEX(Sheet1!$B$1:$AF$1,MATCH(B$1,INDEX(Sheet1!$B $1:$AF$10,MATCH($A
$1,Sheet1!$A$1:$A$10,0),0),0))-1
copy across until D2.

In Sheet2!B3 (this is an *array* formula, hence commit with Ctrl+Shift
+Enter):
=INDEX(Sheet1!$B$1:$AF$1,MATCH(1,(INDEX(Sheet1!$B$ 1:$AF$10,MATCH($A
$1,Sheet1!$A$1:$A$10,0),0)=B$1)*(COUNTIF(B$2:B2,Sh eet1!$B$1:$AF
$1-1)=0),0))-1
copy across and down.

This will produce three columns with the dates for each task as you
want them. After the list for each column is exhausted you will be
getting error values. You can perform conditional formatting in Sheet2!
B2:D10:

Select Sheet2!B2. Format | Conditional Formatting.
Choose Formula Is: =ISERROR(B2) (remove $$ that Excel might put in the
reference). Choose a white font.

HTH
Kostis Vezerides

Hi again

I've also tried copying and pasting the formula which needs to go into
Sheet2!B2 and it throws up and error message. That's after I've altered the
bottom right co-ordinate from AF10 to AF52, which is the actual size of the
table in Sheet 1.

"vezerid" wrote:

On May 2, 12:16 pm, bollard wrote:
Hi

I have a spreadsheet which shows a name in Column A.

The other columns are headed up with different dates in July.

Across the row, under the relevant date, appears the letter A or C or R.
This may appear several times across the row.

This shows which date that person will be able to perform a certain task.

On a separate sheet, I want to show the date of the night before, as that
person may need over-night accommodaiton in order to perform the task.

So, if Fred Smith is performing task R on July 4, 5 & 6, he may need
overnight accommodation on the nights of July 3, 4 & 5.

So Sheet 2 will need to look across that person's row on Sheet 1 for all
occurences of A, R or C; look up the date at the top of the column, then
return that date minus 1 however mnay times the A, C or R has appeared on
Sheet 1.

I hope this explains.

Can anyone offer a solution, please?

Assumptions:
In Sheet1, your table occupies cells:A1:AF10. Date headers in B1:AF1.
Names in A1:A10. A/C/R in B2:AF10.
In Sheet2, you enter a name in A1 and the labels A, C and R in B1:D1.

In Sheet2!B2:
=INDEX(Sheet1!$B$1:$AF$1,MATCH(B$1,INDEX(Sheet1!$B $1:$AF$10,MATCH($A
$1,Sheet1!$A$1:$A$10,0),0),0))-1
copy across until D2.

In Sheet2!B3 (this is an *array* formula, hence commit with Ctrl+Shift
+Enter):
=INDEX(Sheet1!$B$1:$AF$1,MATCH(1,(INDEX(Sheet1!$B$ 1:$AF$10,MATCH($A
$1,Sheet1!$A$1:$A$10,0),0)=B$1)*(COUNTIF(B$2:B2,Sh eet1!$B$1:$AF
$1-1)=0),0))-1
copy across and down.

This will produce three columns with the dates for each task as you
want them. After the list for each column is exhausted you will be
getting error values. You can perform conditional formatting in Sheet2!
B2:D10:

Select Sheet2!B2. Format | Conditional Formatting.
Choose Formula Is: =ISERROR(B2) (remove $$ that Excel might put in the
reference). Choose a white font.

HTH
Kostis Vezerides

On May 3, 5:41 pm, bollard wrote:
Hi again

I've also tried copying and pasting the formula which needs to go into
Sheet2!B2 and it throws up and error message. That's after I've altered the
bottom right co-ordinate from AF10 to AF52, which is the actual size of the
table in Sheet 1.

"vezerid" wrote:
On May 2, 12:16 pm, bollard wrote:
Hi

I have a spreadsheet which shows a name in Column A.

The other columns are headed up with different dates in July.

Across the row, under the relevant date, appears the letter A or C or R.
This may appear several times across the row.

This shows which date that person will be able to perform a certain task.

On a separate sheet, I want to show the date of the night before, as that
person may need over-night accommodaiton in order to perform the task.

So, if Fred Smith is performing task R on July 4, 5 & 6, he may need
overnight accommodation on the nights of July 3, 4 & 5.

So Sheet 2 will need to look across that person's row on Sheet 1 for all
occurences of A, R or C; look up the date at the top of the column, then
return that date minus 1 however mnay times the A, C or R has appeared on
Sheet 1.

I hope this explains.

Can anyone offer a solution, please?

Assumptions:
In Sheet1, your table occupies cells:A1:AF10. Date headers in B1:AF1.
Names in A1:A10. A/C/R in B2:AF10.
In Sheet2, you enter a name in A1 and the labels A, C and R in B1:D1.

In Sheet2!B2:
=INDEX(Sheet1!$B$1:$AF$1,MATCH(B$1,INDEX(Sheet1!$B $1:$AF$10,MATCH($A
$1,Sheet1!$A$1:$A$10,0),0),0))-1
copy across until D2.

In Sheet2!B3 (this is an *array* formula, hence commit with Ctrl+Shift
+Enter):
=INDEX(Sheet1!$B$1:$AF$1,MATCH(1,(INDEX(Sheet1!$B$ 1:$AF$10,MATCH($A
$1,Sheet1!$A$1:$A$10,0),0)=B$1)*(COUNTIF(B$2:B2,Sh eet1!$B$1:$AF
$1-1)=0),0))-1
copy across and down.

This will produce three columns with the dates for each task as you
want them. After the list for each column is exhausted you will be
getting error values. You can perform conditional formatting in Sheet2!
B2:D10:

Select Sheet2!B2. Format | Conditional Formatting.
Choose Formula Is: =ISERROR(B2) (remove $$ that Excel might put in the
reference). Choose a white font.

HTH
Kostis Vezerides

Hi, I just saw your message. I will reply tomorrow. Pr3esently I don't
even have the time to alter the formulas.

Regards,
Kostis