Retrieve next value(s)

[53teeth]

Hello-
I have a list of projects as follows:

Project Number Start Date End Date
1 Jan 11 Dec 11
2 Feb 11 Dec 13
3 Jan 11 Dec 12
4 Dec 10 Dec 11


On a summary page, I'd like to list only those projects that start in Jan 11. I have tried a few options, but, it's only returning the first value. I've got a "COUNTIF" table going, but, need the actual project number(s) for those that fit the condition of starting Jan 11.

Any help is appreciated!

[NBVC]

Are the dates text values "Jan 11" or are they actual dates formatted as "mmm yy"?

[53teeth]

They are real dates formatted as mmm yy

[NBVC]

Assuming data begins in A2 of Sheet1, then in an empty cell, say F2 enter formual:

Please Login or Register  to view this content.

this counts matches. Adjust range to suit.

And to get the projects enter formula in a new column:

Please Login or Register  to view this content.

adjust ranges again and confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down as far as you want... you can copy across too to get other info....

[53teeth]

Thanks NVBC! Here's what I've got but with my updates:

=IF(ROWS('Data - Roadmap'!$A$5:$A6)>J5,"",INDEX('Data - Roadmap'!A$6:A$26,SMALL(IF(TEXT('Data - Roadmap'!F6:F26,"mmmm yyyy")="May 2011",ROW('Data - Roadmap'!$F$6:$F$26)-ROW($F$5)+1),ROWS('Data - Roadmap'!$A$5:$A6))))

However, now I'm getting a NUM error? Note: J5 is on the summary sheet and formatted the same (mmmm yyyy). It's current value is 5/1/2011 (but in the other custom format). Also, Column A on Data - Roadmap worksheet is the project number, Column F is the Start Date.

[NBVC]

you need to be consistent with range sizes, and also you have some absolute/relative references incorrect...

Try:

Please Login or Register  to view this content.

also, make sure after entering the formula and making cell active for editing, hold down CTRL and SHIFT keys and hit ENTER to confirm it, then copy down.

[53teeth]

Cool! Getting closer...now it's pulling back a date of 1/1/2011, but, not the project number in column A?

[NBVC]

Change the indexed range, then:

Please Login or Register  to view this content.

[53teeth]

Thank you so much NBVC!!!

Hmm...now, nothing works and I get a REF error. Also, I notice on your formula that some of the F4:F140 do not have "data roadmap" as references? I am very appreciative of the help. Again....here's how it is formatted:

Sheet 1:
Cell J5 = mmmm yyyy

Sheet 2 (aka Data Roadmap)

Header: Project Number (A1) Start Date (A2)
1 Jan 11
2 Mar 11
3 Jan 11
4 Feb 11

If I enter "1/1/2011" into Cell J5, I'd like to have the projects on Sheet 2 show up.

[NBVC]

I am not sure how they disappeared....but you are correct, the sheet references need to be in there...

Please Login or Register  to view this content.

[53teeth]

Great! Looks like it's working. I get the value "2", though....and when I CSE the formula, it goes to a #NUM error. My header row is 5, should I bump the "5s" up to a 6?

[NBVC]

The formula should, after CSE confirmation, give you the first "project" in column A that has a May 2011 date....

Is there a project before "2" that should correspond?

Is J7 the cell that contains the count formula that should be:

=SUMPRODUCT(--(TEXT('Data - Roadmap'!$F$5:$F$140,"mmmm yyyy")="May 2011"))

and is J7 in the active sheet or in the Data - Roadmap sheet?

[53teeth]

Is there a project before "2" that should correspond?

The first project in the list with the start month of may is actually project 5. So, techincally 5 should be the first number displayed, then 16, then 104

Is J7 the cell that contains the count formula that should be:

J7 doesn't have anything in it and is on the active sheet. I don't have anything else on the sheet really, but, the date in J7. Did I miss something with not putting the SUMPRODUCT in there?

[NBVC]

Yes, the sumproduct formula gives a count of matches.. and where ever you put that formula should be the reference where your J7 currently is in the final formula...

Maybe you can post the workbook, if it is not confidential or you can adjust it to be non-confidential...

[53teeth]

I've attached basic data here...Thanks again!

EDIT:
I just realized that the project start dates aren't May 2011, but feel free to change one or two of them if it helps. The summary page has 5/1/12011 on it...

[NBVC]

Since you seem to be using XL2007 (adjust your profile to reflect please), then it is a bit easier. You don't need a counter formula, and you can use IFERROR

So try:

Please Login or Register  to view this content.

CSE confirmed.

See attached.

Note: I now also pointed to cell D3 for comparison date...

[53teeth]

Hmm...it works great on your sheet, but, I'm trying to copy it over into mine. No luck! It's only pulling back the number "2", which has the start date of Januaryy 2011.


Does it matter if the array on Data - Roadmap is a reference to another page? the data on the sheet is populated from an external workbook...They are all formatted the same though.

[NBVC]

Are the actual references exactly the same locations?

Are you confirming the formula with CTRL+SHIFT+ENTER (do you see { } brackets around the formula)?

Other than that, hard to say without seeing actual workbook.. you saw it working in the sample you supplied....

[53teeth]

There was something formatted in the original workbook that the formula didn't like, so, I just built the summary page again, this time around the formula itself. It works like a charm. Thanks so much NBVC!!!!!! I even added a projects completing this month column, and, bumped the columns from F to G.

Thanks a bnunch!

[NBVC]

You are welcome,

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save