Is this possible

iansmith

New Member
Joined
Oct 25, 2002
Messages
9
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Oct 25, 2002
Messages
9
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Oct 25, 2002
Messages
9

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Oct 25, 2002
Messages
9

ADVERTISEMENT

Just day names...Thanks
 

iansmith

New Member
Joined
Oct 25, 2002
Messages
9
My problem is still not resolved does anyone have any ideas how or ideed if this can be done
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,144,765
Messages
5,726,175
Members
422,660
Latest member
mrsteele

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top