Help: Multiple criteria involving strings

1. The first worksheet contains a database.

2. The column headers can be seen in attached file.

3. Budget-Spent-Forecast attribute can be
1. Committed
2. Forecast

4.CW attribute is year followed by week number
example:
2014-35
2015-06
2015-08

5. Year attribute lists the year


6.Here is my problem. I want to figure out a way (function or a macro), that will run through the database and find the latest entry for the attribute ' committed'
ie.
committed - latest year

Then it proceeds to extract the week number. Then it exports the week number as 'CWweeknumber' to a cell in a different worksheet in the same file.

Any help would be grateful! Thank you!

Provide a small database to work upon...

Vikas Gautam

here is a small example of data


CWexcelmacro.xlsx

Doesn't help since, new entries would be made to the database and the formula contains fixed cell references. I also made a mistake in the database. the CW colum is not filled as week number but as 2014-01 or 2015-06 or 2015-43. Some clarification, small explanation on why MMULT function works here would be great!

Originally Posted by Vikas_Gautam

Got the solution...
Try this..

Formula:

Please Login or Register  to view this content.

check the attachment..

Don't forget to click *

---

http://www.Excel-Buzz.blogspot.in

I used Mmult() to avoid pressing Ctrl + shift + enter..

Vikas Gautam

I have given two formulas for latest entry..
and those two were working for me..
as you didn't explain the measure of latest entry...

May be there can be problem with your excel version..
try replacing "," with ";"

Vikas Gautam

That formula works on in the test file. in datvbase file file it doesn't, it gives 'value error, I made the , > , change already. even i am wondering why it works in one file and doesnt in another. it is xlsm file.

Originally Posted by Vikas_Gautam

I have given two formulas for latest entry..
and those two were working for me..
as you didn't explain the measure of latest entry...

May be there can be problem with your excel version..
try replacing "," with ";"

Vikas Gautam

=RIGHT(INDEX(Basis!$U$4:$U$90000;MAX(IF(LEFT(Basis!$U$4:$U$90000;4)*1=MAX(LEFT(Basis!$U$4:$U$90000)*1);IF(Basis!$I$4:$I$90000="committed";ROW(Basis!$U$4:$U$90000)-ROW(Basis!$U$4)+1))));2)

I edited the formula to this to fit it to my database, but now I get #value error

I get an error message when I control shift enter. I usuallg get an error from this sheet alread whenever I press enter. so i dont think I can enter it way you want me to. one user above mentioned mmult function for doing this. what is that about?

Provide your database file...
the big one..
if you can...

Don't forget to click *

Unfortunately confidential.

Okay then..
So try Mr. Sktneer's formula..
Hopefully that will..do..

Vikas Gautam

I have an error in the file already called 'ambiguous name detected' it always appears when I change some cell. Does that help?

#value!

Originally Posted by sktneer

Try this formula.....

Please Login or Register  to view this content.

Don't forget to enter it with Ctrl+Shift+Enter.

Does this help?

{=RIGHT(INDEX(Basis!$G$78:$G$90000;MAX(IF(IF(Basis!$G$78:$G$90000<>"";LEFT(Basis!$G$78:$G$90000;78)*1)=MAX(IF(Basis!$G$78:$G$90000<>"";LEFT(Basis!$G$78:$G$90000;78)*1));IF(Basis!$I$78:$I$90000="committed";ROW(Basis!$G$78:$G$90000)-ROW(Basis!$G$78)+1))));2)}


This is what I have in the cell

some of the data in the table is pulled up from other worksheets. but is think that is not relevant here

exactfile.xlsx

yeah corrected that, still the same error

Originally Posted by sktneer

I just noticed that in the LEFT formula you are using 78. Why? I was using 4 to extract the year as in your sample data the first 4 characters of the strings in the cells are year.