Is this possible

[iansmith]

In colunm A, row 2 I have the W/C dates that populate down throghout that colunm.
In colonm B, I have Duty Numbers which also populates down.(It is set up so that I can just enter the first date & duty number and it populates down)
In colunms C,D,E,F and so on, I have a vlookup formula that looks at the duty number colunm and inserts the relavent duty time for each day ( the days are across the top row 1) what I want to happen is: that if christmas day falls on say Tuesday, I want the cell with the vlookup formula in to say "Christmas day" is this possible. ( it is not possible to just write Chistmas day in the cell because it changes position in the table as I change the date and duty number at the top of colonms A & B).. Hope iv'e explained this clearly, can any one help, any suggestions are welcome.

 

[Aladin Akyurek]

On 2002-10-27 02:59, iansmith wrote:
In colunm A, row 2 I have the W/C dates that populate down throghout that colunm.
In colonm B, I have Duty Numbers which also populates down.(It is set up so that I can just enter the first date & duty number and it populates down)
In colunms C,D,E,F and so on, I have a vlookup formula that looks at the duty number colunm and inserts the relavent duty time for each day ( the days are across the top row 1) what I want to happen is: that if christmas day falls on say Tuesday, I want the cell with the vlookup formula in to say "Christmas day" is this possible. ( it is not possible to just write Chistmas day in the cell because it changes position in the table as I change the date and duty number at the top of colonms A & B).. Hope iv'e explained this clearly, can any one help, any suggestions are welcome.

That should be possible. Is the "Christmas day" the only concern?

And, care to post the VLOOKUP formula that you're using?

 

[iansmith]

Thanks for your reply..I would like to include all the holiday throughout the year if possible.
The fomula is =VLOOKUP(B2,$B$42:$H$47,2)
This looks at the duty number in colunm B and finds the relevant start time in in the template cells B42 to H47. I would like a formula that reflects holidays in the appropriate cell. hope this is understandable.
Colunm A = the W/c date
Colunm B = the duty number
Colunm C = Monday
Colunm D + Tuesday and so on
the sheet is set up so that whatever the date and duty number are inserted in row 2 they populate down the colunms

 

[Aladin Akyurek]

On 2002-10-27 04:44, iansmith wrote:
Thanks for your reply..I would like to include all the holiday throughout the year if possible.
The fomula is =VLOOKUP(B2,$B$42:$H$47,2)
This looks at the duty number in colunm B and finds the relevant start time in in the template cells B42 to H47. I would like a formula that reflects holidays in the appropriate cell. hope this is understandable.
Colunm A = the W/c date
Colunm B = the duty number
Colunm C = Monday
Colunm D + Tuesday and so on
the sheet is set up so that whatever the date and duty number are inserted in row 2 they populate down the colunms

Can you use the morefunc.xll add-in, downloadable from

http://longre.free.fr/english/index.html ?

 

[iansmith]

Iv'e never heard of it , but can try if you could resolve this problem ( as youv'e probably gathered i'm new to all this)

 

[Aladin Akyurek]

On 2002-10-27 04:55, iansmith wrote:
Iv'e never heard of it , but can try if you could resolve this problem ( as youv'e probably gathered i'm new to all this)

One more question: Are the days across true dates or just day names?

 

[iansmith]

Just day names...Thanks

 

[iansmith]

My problem is still not resolved does anyone have any ideas how or ideed if this can be done

 

[Aladin Akyurek]

On 2002-10-27 06:01, iansmith wrote:
My problem is still not resolved does anyone have any ideas how or ideed if this can be done

You're new to all this, as you say, but also impatient...

This is what I suggest...

=IF(TEXT(TODAY(),"dddd")=C1,IF(ISNA(SETV(VLOOKUP(TODAY(),Holidays,2,0))),VLOOKUP(B2,$B$42:$H$47,2),GETV()),VLOOKUP(B2,$B$42:$H$47,2))

Holidays refers to a range where you set up a 2-column list of holiday dates of interest along with a desription...

SETV/GETV are functions from the morefunc add-in.

 

[iansmith]

Thank you Aladin
sorry for being impatient i was just getting deperate