Can anyone help with a formula to take data from a column entry to a row style? I've attached a sample spreadsheet.
Thanks for any ideas.
Can anyone help with a formula to take data from a column entry to a row style? I've attached a sample spreadsheet.
Thanks for any ideas.
Last edited by BRISBANEBOB; 04-03-2009 at 01:50 AM.
Try:
=IF(AND(C$5>=INDEX(Record!$B$8:$E$14,MATCH($B6,Record!$B$8:$B$14,0),2),C$5<=INDEX(Record!$B$8:$E$14,MATCH($B6,Record!$B$8:$B$14,0),3)),INDEX(Record!$B$8:$E$14,MATCH($B6,Record!$B$8:$B$14,0),4),"")
in C6 of log sheet.
copy down and drag across
Works beautifully! Thanks - logic had me beaten.
BRISBANEBOB,
You've marked as SOLVED so I might be missing something but as I see it the prior formula does not work in so far as it will only pull the first record for each person (eg 2nd instance of James is ignored)
Based on my understanding I think the below will work for you:
Log!C6:
=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",LOOKUP(2,1/((Record!$B$8:$B$14=$B6)*(Record!$C$8:$C$14<=C$5)*(Record!$D$8:$D$14>=C$5)),Record!$E$8:$E$14)))
copied across matrix
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Please can you explain how this formula works - there are things in it I didn't know existed...
It's not quick to explain I'm afraid and I've tried to explain this approach a few times - once here: http://www.excelforum.com/2053930-post10.html ... read through and see if it makes more sense ?
The 2nd LOOKUP with CHOOSE is used to handle the fact that in some cases you may get an error result from the embedded LOOKUP... and in those cases you want to return a Null rather than the Error value to the cell in which the formula exists.
These formulae are not quick performance wise but I would say less expensive than a Sumproduct / CSE approach... I don't have the requisite knowledge to advise definitively.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks