I was very helpfully assisted earlier by a few people on here. Here is the next challenge.
The attached spreadsheet searches for non-zeros in a lis and displays them in column E. In column D, I need the corresponding week number and in column F the corresponding week begining date. I've tried pulling apart the folumula in column E to try and index this but my efforts are now turning into dead time.
As before, any help much appreciated.
I'm guessing no-one has had any ideas on this so far. If anything springs to mind today please let me know.
Many thanks
Hi
I have trouble opening your example sheet.
Can you please re-post it?
Thx
Hi
If i get your point, then pls try this.
In D2>>=WEEKNUM(C2) Copy down
In F2>>=C2-WEEKDAY(C2)+2 Copy down.
Monday=First day of the week.
Hope to helps you.
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.
Datadog, please disregard my post, problem is at my place - Sorry
Sorry try this one. It's 97 - 2003 format.
May i ask if you saw my suggestion??
Yeah doesn't quite do the job. What columns D and F need to do is look at the cell next to it in column E and say 'Ah, now thats drawn from row 4/5/6/7' etc and pull the corresponding week number and week begining date from columns A and C. Hope that makes sense.
Can you give us an example?
Which is the result that you expect in D2 AND E2 and what in D3 AND E3??
In the example uploaded, Cell D2 would be week 4, D3 would return 5, D4 would return 14, D5 would return 17. Column E would be E2 = 22/06/2010, E3 = 29/06/2010, E4 = 31/08/2010.
Basically Column E looks at column B and skips between all the number greater than zero in order of their appearance, displaying them with the zeros in between gone. The corresponding week numbers and week begining dates need to appear next to them.
..Ok.. for column E, we need a simple INDEX&MATCH..
But for D,...sorry i can not follow you. I can not find the reason d2(for example) to return 4, etc....so to try to built a formula.
I think both of us have to wait for an answer....!
I'm meeting with my boss late to discuss this. If an answer comes along I'll post it for anyone that is intereted or as frustrated withnot knowing the answer as I am!
Isn't that just
D2: =IF(ROW(1:1)>COUNTIF(B$2:B$157,">0"),"",INDEX(A:A,LARGE(INDEX(ROW(B$2:B$157)*(B$2:B$157>0),0),COUNTIF(B$2:B$157,">0")-(ROW(1:1)-1))))
F2: =IF(ROW(1:1)>COUNTIF($B$2:$B$157,">0"),"",INDEX(C:C,LARGE(INDEX(ROW($B$2:$B$157)*($B$2:$B$157>0),0),COUNTIF($B$2:$B$157,">0")-(ROW(1:1)-1))))
and fill down?
Good luck.
This was so ridiculously simple in the end. It was just a case of adding an offset to the second half of the command in column E. It only took almost a whole day to figure that one out!
Her's a different approach to simplify......
As your week numbers are in ascending order then perhaps use this formula in D2 copied down
=IF(D1="","",IFERROR(INDEX(A$2:A$157,MATCH(1,INDEX((B$2:B$157>0)*(A$2:A$157>N(D1)),0),0)),""))
Then for the other data you can use VLOOKUP, i.e. in E2 copied to F2 and then down both columns
=IF($D2="","",VLOOKUP($D2,$A$2:$C$157,COLUMNS($E2:E2)+1,0))
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks