Hello I am trying to return a value from a database based upon today's date, a simplified example of the sheet is attached.
Also my method of populating successive months is rather crude and I'm sure could be better stated! Any assistance much appreciated
Book 04_12_13.xlsx
d2 1/8/2013
e2 filled across
=eomonth(D2,0)+1
to get decembers value
=LOOKUP(B5,D4:N4)
Last edited by martindwilson; 12-04-2013 at 08:21 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Thanks Martin, works perfectly on my simplified example but not in the real thing. I think I can see that your formula effectively takes the last populated number. In other words Jan 14 cant be populated and so today's date will always reflect the current months data, and that would work but..... the data is populated by the formula =SUMPRODUCT(--(!ISNUMBER(H4:H16)),--(H4:H16>0),$C10:$C20) for whatever reason this formula applied in Column H (December) and successively thereafter I (January) etc returns a zero in the future months and so your formula picks up on the last of these and thus returns zero. Perhaps its as simple as reformatting the cells to remain blank instead of returning zero?
oops
decembers value would be
=LOOKUP(B5,D2:N2,D4:N4)
oddly this fix returns 564.80 which is the Nov-13 figure, instead of the December figure?
not for me,did you fix the date ranges? if you leave them as they are h2 =15/dec lookup today() cant be found there so it returns the next lowest which is 14 november
Last edited by martindwilson; 12-04-2013 at 10:32 AM.
No I didn't!.... apologies...and thanks for that clean up as well. Outstanding!
Does this do what you want?
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Hi Ron, your method also works, thank you
You're welcome. There is usually more than one way to solve a problem. Choose what works best for you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks