Calendar and VLOOKUP (perhaps?)

[SamuelT]

Hi all,

I have set up a calendar (away from my main data in a named range 'dates') covering the time period a project is running. I have the week's numbers running down a column (Weeks 1 - 10) and the days (Mon - Sun) running across in rows.

In column A I have listed when a certain part of the project is taking place ('Week 1', 'Week 2', etc). In column S I have which day of this week a certain part is taking place (Mon, Tues, etc).

What I'm trying to achieve is for Excel to determine from the week number and day what the specific date will be. The days (but not the weeks) are constantly changing, so I need for the user simply to be able to type in, for instance, 'Fri' and Excel will, from my calendar, work out what the specific date is.

I vaguely think some kind of VLOOKUP or INDEX/MATCH formula might work, but can't work out the exact phrasing. Any ideas?

TIA,

SamuelT

[Franz Verga]

SamuelT wrote:
> Hi all,
>
> I have set up a calendar (away from my main data in a named range
> 'dates') covering the time period a project is running. I have the
> week's numbers running down a column (Weeks 1 - 10) and the days (Mon
> - Sun) running across in rows.
>
> In column A I have listed when a certain part of the project is taking
> place ('Week 1', 'Week 2', etc). In column S I have which day of this
> week a certain part is taking place (Mon, Tues, etc).
>
> What I'm trying to achieve is for Excel to determine from the week
> number and day what the specific date will be. The days (but not the
> weeks) are constantly changing, so I need for the user simply to be
> able to type in, for instance, 'Fri' and Excel will, from my calendar,
> work out what the specific date is.
>
> I vaguely think some kind of VLOOKUP or INDEX/MATCH formula might
> work, but can't work out the exact phrasing. Any ideas?
>
> TIA,
>
> SamuelT

Hi Samuel,

It's quite difficult for me what you mean...
Maybe if could upload a small example file to www.savefile.com or
www.rapidshare.de it would be easier to help you...


--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

[taylorm]

Try the following formula:

=VLOOKUP(WeekNum,Dates,MATCH(WeekDay,DaysOfWeek)+1,FALSE)

Where WeekNum refers to the cell in Column A
Dates is your calendar name range
WeekDay refers to the cell in Column S
DaysOfWeek is your Mon-Sun running across in rows of your Dates name range