• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Formula Help! Look up dates in a Row, then return last date that has value

W

WinExcel

New Member
Hello Hui! Help!!!
I've been trying to figure this one out on my own for a while and no luck... it's time to ask the Ninjas!

In Row 2 - I have a list of dates Column G to Column AI (weekly budget end dates for 3 years)​

In Column F, I want to create a formula to:
- look at Row 2's set of dates
- Return the last date that that the cell has a value >$1

I think/ know it's probably a VBA formula but just can't figure it out.
Your help will save me... tremendously! THANK YOU!!!​
 
Hi, WinExcel!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, VBA and formula don't go together in the same container. While a formula is something you enter (type) in Excel cells, VBA (acronym for Visual Basic for Applications, which is the programming language included in MS Office products) is a piece of code that does something, and it's written in Excel VBE (Visual Basic Environment, reachable pressing Alt-F11 from Excel worksheets).

Clarified that (I hope, if not please ask any doubts), give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/VBA Formula Help! Look up dates in a Row, then return last date that has value (for WinExcel at chandoo.org).xlsx
​
As I didn't fully understand what did you mean by:​
WinExcel said:
- Return the last date that that the cell has a value >$1
Click to expand...
I set up 2 searches: 1 for searching upwards (into row 1) and 1 for downwards (into row 3).​
​
F6 (up):=INDICE(G1:AI1;1;COINCIDIR(F$5+6;G2:AI2;1)) -----> in english: =INDEX(G1:AI1,1,MATCH(F$5+6,G2:AI2,1))

F7 (down): =BUSCARH(F$5+6;G2:AI3;2;VERDADERO) -----> in english: =HLOOKUP(F$5+6,G2:AI3,2,TRUE)

Just advise if any issue.

Regards!
 
Thank you so much for the education! I've always considered myself pretty 'advanced' in Excel until I met the Chandoo site and the many Ninjas out there. I'm aspiring to be one!

Sorry for not being clear -- here's what I'm trying to accomplish:
-- see file attached
-- In Row 3, I have a set of dates (Friday dates for 3 years out)
-- In Row 4, I enter the budget numbers by week

-- In column F, I am trying to look at Row 3 and Row 4 and ask Excel to see what is the last date (in Row 3) that there's any budget numbers in Row 4, and return that date back to me.
-- Right now, I'm doing this manually. Going out to each row and manually entering the dates back in Column F.

I know this is hard to describe in words, hope the attached file helps!
 

Attachments

Hi, WinExcel!

Ok, gotcha. Try this in F4:
=INDICE(G$3:FP$3;1;SUMAPRODUCTO(MAX((G4:FP4<>"")*(COLUMNA(G4:FP4))))-6) -----> in english: =INDEX(G$3:FP$3,1,SUMPRODUCT(MAX((G4:FP4<>"")*(COLUMN(G4:FP4))))-6)
and copy down as required.

Regards!
 
WOW! I am truly amazed and forever humbled. You have saved me... I should have asked at least a year earlier!!!

THANK YOU SO MUCH! A sincere thank you!
 
Hi, WinExcel!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
Hello SirJB7!
Sorry - one more question. (because no good deed goes unpunished!!) :P

-- Can you help me with the attached file? (thought I might as well add in the "Budget Start Date" too)

-- Question: When I popped in your formula in my file - the dates gets messed up. I can't figure out why.

Could you help take a look at Columns C and D for me please?
 

Attachments

Hi, WinExcel!

The expression COLUMN(G4:FP4) returns an array with values from 7 (column G) thru 172 (column FP), so the -6 was there to transform them into 1 thru 166, i.e. the relative order of the searched date.

Now your formula contains COLUMN(AR12:GK12) returning 44 to 193, so change the -6 to -43, et voilà!

Regards!
 
Hi SirJB7 -

Sorry to bug you again... can you help me figure out how to insert the "START DATE" too?
-- Looking at ChandooFile2, I need to now add in the "BUDGET START DATE".

Thank you!!!
`Win
 
Hi, WinExcel!
Try this at cell C12:
=INDICE(AR$10:GK$10;1;COINCIDIR(VERDADERO;INDICE(AR12:GK12<>0;0);0)) -----> in english: =INDEX(AR$10:GK$10,1,MATCH(TRUE,INDEX(AR12:GK12<>0,0),0))
Regards!
 
You must log in or register to reply here.
Back
Top