Hi All,
Came by this forum and would like to post a query here. Have just started using Excel on my work related databases and am learning vba and formulas now from scratch.
I have this worksheet in the attachment. I need to put into E3:E7 the latest updated value with cells from G2:L2. Using range from G3 to L7. Column L being the first Column to be used then followed by K, J, I and so on. Is there a formula that can do this?
Regards,
danNAD
Last edited by danNAD; 04-01-2010 at 02:37 AM.
If I've understood...
D3: =COUNT(G3:L3)
copied down to D7
E3: =IF(D3,LOOKUP(9.99E+307,G3:L3,G$2:L$2),"")
copied down to E7
(if you don't want the header dates returned remove that in red from the above)
E3:E7 format as Date
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Erm.. Omg! Thanks Donkey Ote! That was exactly what I was looking for in regards to the E3:E7!
Will try to figure the logic of the formula out^^ Thanks mate!!!!!!! A millions..
Hi Donkey Ote,
I have a query in regards to the formula, (D3,LOOKUP(9.99E+307,G3:L3,G$2:L$2),"")
Can I not have D3 to exist in the formula and still it works the same?
Thanks,
danNAD
I assumed D3 was being used to determine the count of dates in the range - if that's not the case then I would suggest using
=IF(COUNT(G3:L3),LOOKUP(9.99E+307,G3:L3,G$2:L$2),"")
the idea being that should no dates exist a Null ("") should be returned
If you remove the IF the LOOKUP will otherwise return an error should no dates (numbers) exist within the range.
Hi Donkey Ote,
That formula worked wonders =) Instead of D you replace it with a count formula. Then the rest remains the same. Thanks!! I believe I understand a little bit more of excel from this =)
Regards,
danNAD
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks