On Mar 25, 10:03*pm, Chechu wrote:
On Mar 25, 9:54*am, pbart wrote:





Chechu

It looks like you now have alternative lines of investigation to follow up:
the first using inbuilt functionality of the spreadsheet and the second
introducing VBA macros.

I do agree with Joel that there are dangers of hiding errors in complicated
formulae. *If you adopt the formula approach, I would suggest you break the
process into small steps using additional columns to hold intermediate
results.

For example, the first MATCH would tell you how many rows of the second
sheet should be ignored because they postdate the activity. *That number can
be checked for correctness.

The CONCATENATE in the next column uses this value of 'number of rows to
omit' in order to calculate the part of the range on sheet 2 that remains of
interest (the starting cell will be further down the table than the original)
and displays the result as a string. *Again this is something you can check.

Finally a VLOOKUP in the next column can be applied to the range (the
INDIRECT(cell) references the string from the previous column) and will find
all information relating to the first instance of the ResID. *

The remaining step before your spreadsheet hits an unsuspecting public is to
'hide your workings' by first hiding the extra columns and, possibly,
deselecting headings from the view menu.

I hope you are not offended by my suggesting how you might best arrange such
a calculation but, like Joel, I do rather shy away from complex formulae.

Good luck

PBart

"Chechu" wrote:
pbart,

Thanks a lot for your help on this. I understand your suggestion,
except when you say "will return a value from MATCH that provides a
starting row/date for the subsequent Resource search". At some point I
think that the formula I started to work uses some similar approach.
This is how it looks like so far:

INDEX("Sheet 2 COST",MATCH(CONCATENATE(SUMPRODUCT(--("EFFECTIVE
DATE"<="ACTVITY DATE"),--("NEXT EFFECTIVE DATE for the
resource""ACTVITY DATE"),--("EFFECTIVE DATE"),--("Sheet 2 RES
ID"="Sheet 1 RES ID")),"Sheet 1 RES ID"),"dummy: Concatenate(Effective
Date&ResID)",0))

The "NEXT EFFECTIVE DATE for the resource" is a dummy column in sheet
2, sorted by Res ID, Eff Date, then with a serie of If I can determine
the end date for the effective rate.

I know, I know, this is confusing... but looks reasonable??? Still
need to work on the N/A error handling, but that's not complex.

THANKS!
Cecilia

On Mar 24, 7:10 pm, pbart wrote:
Can you sort the second table starting with =TODAY() and running backwards?

If so searching for the Activity Date within the Effective Date range will
return a value from MATCH that provides a starting row/date for the
subsequent Resource search. You could use CONCATENATE to produce a string
representing the range to be searched, eg

=CONCATENATE("B"&(16+F3)&":C21")

(where F3 is the result of the first MATCH or the function itself). *Finally
VLOOKUP, referencing this string using INDIRECT() and set to return the
contents one column to the right of the ResID, would give the cost.

"Chechu" wrote:
I am needing some help on this situation. I have the data structured
in this way:

* *A * * * B * * * C
* *Res ID *Cost * *Activity Date
1 *1234 * * $20 * *1/1/2010
2 *1234 * * $25 * *1/20/2010
3 *7432 * * $15 * *2/2/2010
4 *2574 * * $45 * *10/1/2009
5 *7432 * * $65 * *1/2/2010

It shows by resource (Col A), Hourly Cost (Col B), and Activity date
(Col C).

Then I have a second sheet, with this format:

* *A * * * B * * * C
* *Res ID *Cost * *Effective Date
1 *1234 * * $15 * *12/30/2009
2 *1234 * * $18 * *1/18/2010
3 *7432 * * $12 * *1/31/2010
4 *2574 * * $43 * *9/29/2009
5 *7432 * * $67 * *12/31/2009

Same format, but it shows the standard cost. Effective Date represents
the day when this cost becomes available.

What I am trying to do is in the first sheet, add a column with the
current Cost (Sheet 2) at the moment of the transaction. Example:
Resource 1234, shows $20 cost on an activity on 1/1/2010, but the real
cost coming from sheet 2 is $15 (because it is since 30/12/2009 to
1/18/2010, when a new cost becomes effective).
Sheet 2 can contain two, three, four... x numbers of times the same
resource (with different effective date, of course).

Does somebody know how to calculate this??? In the meantime I am
trying with Index, Match, Sumproduct, Lookup.......

Thanks!
Cecilia
.- Hide quoted text -

- Show quoted text -

.- Hide quoted text -

- Show quoted text -

Joel and PBart,
I just can say 1.000.000 of THANKS for your help and guidance on this.
I will try first with the Macro, since my Excel is crashing with all
these formulas. Demo version is OK, but real data is too large for my
humble Excel 2003...
PBart, great suggestions, and I really appreciate it. I will follow
your logic if I can’t make it with the Macro.
Joel, your Macro is the kind of code that we love, short and clear!! I
think that I can customize it based on the real file, I will start
with it.
THANKS!!!
Cecilia- Hide quoted text -

- Show quoted text -

Joel and PBart,
I customized the Macro based on some data quality issues I have, and
it works just PERFECT. Also I learned a lot which is fantastic. THANKS
a lot for your help on this!!!!!
Cecilia