LinkBack URL
About LinkBacks
Hi,
Please help with creating a formula that finds a first filled row in date column and retrieves this date to column C. See attachment for more details.
best regards,
Risto
Hi,
Please help with creating a formula that finds a first filled row in date column and retrieves this date to column C. See attachment for more details.
best regards,
Risto
Im not entirely sure I understand what you want, byt try this in C3, copied down...
=SMALL($E$1:$I$1,ROW(A1))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Please try this file.
Please click 'Add reputation', if my answer helped you.
Sorry non of these solutions are working. Basically what I want to see in column C is the first planned date of each item.
Perhaps using this ARRAY formula?
=SMALL(IF($D$2:$I$2="Planned",$D$1:$I$1),ROW(A1))
-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
For some reasons this array formula is not working and I would like to avoid array as much as possible since it makes table rather slow with great amount of data.
2 month I asked for formula to get last planned date click to see thread and there I got working solution from martindwilson. Does anyone know how to modifiy it, so instead of getting last planned date it would return first.
It's completely different thing to say that formula does not works than to say that:Ok. Formula works but i prefer a non Array formula as solution.
And here is a non Array formula for your first date.
=LARGE(INDEX(($D$2:$I$2="Planned")*$D$1:$I$1,0),COUNTIF($D$2:$I$2,"Planned"))
Fotis' formula wont give results if the first 'planned' column has no dateOriginally Posted by Fotis1991
Try this adaptation
=LARGE(INDEX(($D$2:$I$2="Planned")*$D$1:$I$1,0),SUMPRODUCT(($D$2:$I$2="Planned")*ISNUMBER($D$1:$I$1)))
Life's a spreadsheet, Excel!
Say thanks, Click *
obviously not. As also the Array SMALL(IF...will not gives correct results. But what kind of "Planned" column it is if has no date?
Acel_XL you are getting close, but for some reason it doesn't show correct result when I have only 1 planned date filled or if I have only last and first filled. Please see attachment. Item A has correct date but B and C are wrong. B should have 27.08.2013 and C should have 26.08.2013
Can anyone help with this?
I tried to do this but you just ignored me(My posts#7 & #8....).
Sorry Fotis1991, I tried your solution but they didn't quite work. I'll attach new sample file, I think I didn't explain it well.
I am not also fan of ARRAY formulas but in some cases MAYBE we have to accept this...
So ARRAY formula.
=SMALL(IF((D3:I3>0)*($D$2:$I$2="Planned");$D$1:$I$1);1)
-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.
Thank you Fotis1991, this one works very good!
You are welcome and thanks for the feed back & rep*.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks