Aug 21 2017
02:00 PM
- last edited on
Jul 25 2018
09:56 AM
by
TechCommunityAP
Aug 21 2017
02:00 PM
- last edited on
Jul 25 2018
09:56 AM
by
TechCommunityAP
Hi Not sure what I've done but the only thing I've changed is the number of cells/ lines in the range from: =MAX(IF('Draw Results'!C1:C2248={3,4},'Draw Results'!D1:D2248))
to =MAX(IF('Draw Results'!C1:C2283={3,4},'Draw Results'!D1:D2283))
What it does is to show the last draw number entered in in column A where either 3 or 4 are entered in column D. The answer should be 2257 but it comes out as "0"
The second formula was changed
from: =MAX(IF('Draw Results'!C1:C2248={3,4},'Draw Results'!A1:A2248))
to =MAX(IF('Draw Results'!C1:C2283={3,4},'Draw Results'!A1:A2283))
What it should show is the date of the last draw entered in column A, where 3 or 4 is entered in column D. the answer should be 08/07/17 but comes out as 00/01/00.
Each formula is entered with ctrl+shift+ebter
Tried other excell help sites but got no help so please don't let me down
Sep 04 2017 02:23 AM
Hi Steven,
I can't reproduce that. Whatever i enter in cells from E1 to E247 i have no error in E317.
Is file you attached with previous post with error? And please, what's your Excel verson?
Sep 10 2017 07:15 AM
Hello again. sorry it took so long to reply. My last message i said about it not working ok. SORRY it seems its my fault. i had not put the formula in right. It seems ok but ill let you know. thank you for your help.
Sep 10 2017 01:56 PM
Hi Again. seems i've not got it figured out. I have attached the sheet. As you can see ive put the draw results in row 249. and in row 317 under the draw results then #VALUE appears in the that cell. not sure whay but could you please let me know what ive done wrong. Thank you
Sep 12 2017 08:59 AM
Hi Steven,
Sorry, missed this your reply. I tried to play with the file you send a week or so ago and was not able to reproduce the error. But that was no data in row 249.
Could you please send me the file where you see the error?
Sep 21 2017 01:48 PM - edited Sep 21 2017 01:49 PM
Hello Again. Ive attached the sheet. wot ive done is and your formula to the cells in row 317. Ive added the a new set of draw results in to row 249. If you look at cells K317, R317 etc you'll see #VALUE
Sep 22 2017 04:52 AM
Hi Steven,
Unfortunately in the file you attached i don't see the formulas (only #REF) since 'General Lotto Info' tab is removed. Could you please send full file to check?
If you care about the confidentiality send the link where i can download it with in-private message.
Sep 27 2017 01:55 PM
Hi Sorry about that. i had to delete some of the none essential page and deleted the wrong one. Ill try again. Please try this one.
Sep 28 2017 03:54 AM
Hi Steven,
That's since in B249 you enter the text "12.7.17" instead of the date in your locale format. If change on date (actually number) formulas work. Please see attached.
Oct 01 2017 01:48 PM
Hi Sergei. Ive done as you have suggested but still getting the same probelm. Ive attached a PDF of a screen shot of what i get when i try to open the file to work on it. I have circled the cells that have #Value in them incase the image is not great.
Oct 02 2017 06:43 AM
Hi Steven,
As the date you have to enter the date in your locale format (12/07/2017 by default for UK) or simply the number 42928 (which is equivalent of that date). Or by refrence on previous date
=B248+4
And only after that apply format if necessary.
Please see the file in my previous post.
Oct 13 2017 01:13 PM
Hi Sorry it took so long to reply but my laptop has been away for repair. Will try your suggestion as sson as i can. thank you
Oct 29 2017 02:33 PM
Nov 05 2017 01:23 AM
Steven,
That could be something else, but i see no errors in my copy of the file. How your date is entered you may check in formula bar. If as text
you have an error, if as date
all works