If I understand, one way to do it is:
Worksheet B, create another column (maybe D) after then the end week date
with the number of week, than you could use a formula like
=(52-vlookup(start date,worksheetb!$b$2:$d$53,3,1))*rate
Change start date and rate to the cells reference as your convenience.
hope it helps
Regards
Marcelo
"Mary-Lou" escreveu:
Thanks for the tip - but I searched through a lot of examples and haven't
found what I'm looking for yet.
Here's what I'm trying to do:
Worksheet A would have the following:
Resource Start Date Rate Expected Expenses
Joe 03/16/06 $100
Mary 04/05/06 $75
1) In the Expected Expenses column, I would calculate the expected cost for
a resource from the time they start until the end of company fiscal year (not
calendar year).
2) I would manually look up the Start Date in Worksheet B (see below) to
see what week# the start date falls under.
3) Then I would manually add the week # into the Expected Expenses
calculation.
I'm looking for a way to automate taking Start Date from Worksheet A and
looking it up in Worksheet B in order to return the week #.
Worksheet B - Company fiscal calendar by week
Week # Start of Week End of Week
1 10/22/05 10/28/05
2 10/29/05 11/04/05
€¦.
52 10/14/06 10/20/06
"Marcelo" wrote:
Mary-lou,
try the tips on this web,
http://www.bettersolutions.com/excel...M012916331.htm
regards from Brazil
Marcelo
"Mary-Lou" escreveu:
I have one worksheet with 52 rows for each week of the year with the
following headings: week #, start date of week and end date of week. I have
another worksheet where I need to forecast expected expenses for new
employees based on the date they start ie. Joe starts in Feb 6 (week 6) and
then calculate expected spend based on remaining weeks in the year. I do
this manually right now. How can I automate this?