Error in #VALUE

**Larbec** · 03-31-2013, 08:38 PM

Hey guys and gals! Newbie here and learning rapidly or at least I hope so

I am going behind another person on a book and it is giving me an Error in Value in some of the cells. I have looked over the code and it matches the cells without any issues at all. Any clues where I can go look to solve? When I check things out it says I have a value used in the formula is of the wrong data type BUT like I said, when I look in the other cells they look the same besides the line number (B252,C252)

If this is not in the correct place please forgive me

**Dunc3142** · 03-31-2013, 08:57 PM

Make sure you don't have a space in front or behind the number. Excel treats that as text. Also make sure the cell is formatted as a number.

**Larbec** · 03-31-2013, 09:23 PM

The formula is set up for TEXT and I have examined the ones with error codes with the ones without codes and they are identical

here they are

=IF(ISBLUE('Numbers - PF'!H249),"B","") GIVING AN ERROR
=IF(ISBLUE('Numbers - PF'!H251),"B","") NOT GIVING AN ERROR

I am clueless

**martindwilson** · 03-31-2013, 09:34 PM

crystal ball time?

**Larbec** · 03-31-2013, 10:22 PM

Originally Posted by martindwilson

crystal ball time?

My crystal ball has a crack LOL

okay, is there a way I can copy GOOD cells and drag them over the BAD ones to copy the GOOD cells to the BAD. Like you would drag a few for the date sequence or numbers 1 2 3 4 5 6

#VALUE! ( this is the error code)

**protonLeah** · 03-31-2013, 11:12 PM

crystal ball time? (translation: Post a workbook = no guessing,

)
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**Larbec** · 03-31-2013, 11:38 PM

This is a small portion of the sheet. It links to another numbers sheet (not sure if that's important)

**martindwilson** · 04-01-2013, 07:53 AM

=IF(ISEVEN('C:\Users\Larry\Desktop\POWERBALL\PB books\[(Numbers Even - Odd)-Good Book.xlsm]Numbers - EO'!B245),"E","")of course its important, how can we see whats in the other workbook!

**martindwilson** · 04-01-2013, 09:47 AM

(Numbers Even - Odd)-Good Book.xlsm]Numbers - EO'!B244) what's actually in B244?

**Larbec** · 04-01-2013, 10:13 AM

Each line will place an E for even or O for odd. The numbers book is where I place a number say 25 on a specific line on that book. That book is linked to this book I'm working on. I have several lines that work great say from B100-B238. B238 all the way to B400 for example are giving me this error code. Below B238 no issues I hope this makes sense

**Larbec** · 04-01-2013, 10:22 AM

I understand but as you can see n the few lines I put up. They are all the same but one has no errors. Could the issue be on another book like the numbers book for example?

**martindwilson** · 04-01-2013, 10:18 AM

value errors are given when the value is of the wrong type, in your case probably text instead of numbers,numbers stored as text should work but numbers with spaces in the same cell wont

**martindwilson** · 04-01-2013, 10:48 AM

the error will be in
(Numbers Even - Odd)-Good Book.xlsm]Numbers - EO'!B244)

**Larbec** · 04-01-2013, 11:38 AM

Originally Posted by martindwilson

the error will be in
(Numbers Even - Odd)-Good Book.xlsm]Numbers - EO'!B244)

I hope this makes better sense by these pics whats going on

Numbers EO Good book.jpg Numbers EO Numbers 1-5.jpg Numbers Sheet.jpg Numbers Even Odd Good book paint.jpg

**martindwilson** · 04-01-2013, 12:02 PM

have you just tried checking the numbers in the same sheet? with a straight =iseven(b1) and fill down

**Larbec** · 04-01-2013, 01:30 PM

Originally Posted by martindwilson

have you just tried checking the numbers in the same sheet? with a straight =iseven(b1) and fill down

I am not sure I know what you mean. How do I do this?

**Larbec** · 04-01-2013, 06:16 PM

Originally Posted by martindwilson

have you just tried checking the numbers in the same sheet? with a straight =iseven(b1) and fill down

I have not. The reason is that the pattern works diagonal. If you look at the pic cell 51B has an E and 51A and 53A and will not. 52A will. The right 2 cells are Odd and the left Even They basically have code in every other one like a checker or chess board. Unless, there is a way to do that with a click of the mouse?

Numbers EO.jpg

**Larbec** · 04-01-2013, 01:53 PM

Martin

You have convinced me to quadruple check my numbers and I have found 6 errors so far on the NUMBERS sheet. I corrected them and although it corrected several other sheets it did not correct the one I am asking about. I will continue to search

THANK YOU!

**martindwilson** · 04-01-2013, 02:04 PM

first way of trouble shooting
break the formula/formulas down to its simplist and test each part, this is quite a simple formula

=IF(ISODD('C:\Users\Larry\Desktop\POWERBALL\PB books\[(Numbers Even - Odd)-Good Book.xlsm]Numbers - EO'!B244),"O","")
but you dont test it across work books just go to workbook
[(Numbers Even - Odd)-Good Book.xlsm open it on sheet Numbers - EO'
dont worry about the IF() just test the =isodd(b2444) in a spare cell see what result you get ie true/false or #value! if its #value! then something is wrong with the contents of b244

**Larbec** · 04-01-2013, 02:17 PM

Originally Posted by martindwilson

first way of trouble shooting
break the formula/formulas down to its simplist and test each part, this is quite a simple formula

=IF(ISODD('C:\Users\Larry\Desktop\POWERBALL\PB books\[(Numbers Even - Odd)-Good Book.xlsm]Numbers - EO'!B244),"O","")
but you dont test it across work books just go to workbook
[(Numbers Even - Odd)-Good Book.xlsm open it on sheet Numbers - EO'
dont worry about the IF() just test the =isodd(b2444) in a spare cell see what result you get ie true/false or #value! if its #value! then something is wrong with the contents of b244

Thanks! I will give it a try

**Larbec** · 04-01-2013, 02:30 PM

Originally Posted by martindwilson

first way of trouble shooting
break the formula/formulas down to its simplist and test each part, this is quite a simple formula

=IF(ISODD('C:\Users\Larry\Desktop\POWERBALL\PB books\[(Numbers Even - Odd)-Good Book.xlsm]Numbers - EO'!B244),"O","")
but you dont test it across work books just go to workbook
[(Numbers Even - Odd)-Good Book.xlsm open it on sheet Numbers - EO'
dont worry about the IF() just test the =isodd(b2444) in a spare cell see what result you get ie true/false or #value! if its #value! then something is wrong with the contents of b244

It works in the spare cell. Now what?

Spoke too soon. LOL, The even works but the odd does not. I copied a cell from the error and placed in a clean cell and the "E" was present. I copied the cell with an "O" and nothing happened. Same error?

**Larbec** · 04-01-2013, 02:53 PM

Originally Posted by Larbec

It works in the spare cell. Now what?

Spoke too soon. LOL, The even works but the odd does not. I copied a cell from the error and placed in a clean cell and the "E" was present. I copied the cell with an "O" and nothing happened. Same error?

Not sure if this matters. I have "no" errors on the Numbers EO sheet. In the book the errors are on the page called "starter"

**Larbec** · 04-01-2013, 02:57 PM

Originally Posted by Larbec

It works in the spare cell. Now what?

Spoke too soon. LOL, The even works but the odd does not. I copied a cell from the error and placed in a clean cell and the "E" was present. I copied the cell with an "O" and nothing happened. Same error?

Okay, I did exactly what you asked and it comes back TRUE
=iseven(J248)

=ISODD(J248) FALSE

=ISEVEN(J249) #VALUE!

**martindwilson** · 04-01-2013, 03:03 PM

=ISEVEN(J249) so what's in that cell?

**Larbec** · 04-01-2013, 03:08 PM

Originally Posted by martindwilson

=ISEVEN(J249) so what's in that cell?

#VALUE! is in the cell off to the side
=IF(ISEVEN('Numbers - EO'!J249),"E","") is in the actual cell with the error #VALUE!

**martindwilson** · 04-01-2013, 03:33 PM

what value is in the cell j249? if it is a number what does =len(j249)return? is it the same length as the number? eg 12 should return 2

**martindwilson** · 04-01-2013, 06:39 PM

until you check just one that gives a value error there is no point in carrying on,pics do not help

**Larbec** · 04-01-2013, 07:44 PM

Originally Posted by martindwilson

until you check just one that gives a value error there is no point in carrying on,pics do not help

I am so sorry Martin, what do you mean until I check just one that gives a value error? The boxes say #VALUE error, nothing more even though the code is in the line

**martindwilson** · 04-01-2013, 08:58 PM

now you have to check the cell you are testing,as i said before what is in the cell you are referencing you haven't said,the formula isnt wrong,its whats actually in the cell it refers to that is wrong

**Larbec** · 04-01-2013, 10:41 PM

Originally Posted by martindwilson

now you have to check the cell you are testing,as i said before what is in the cell you are referencing you haven't said,the formula isnt wrong,its whats actually in the cell it refers to that is wrong

I copied the cell, placed in on an empty cell and this is what I got E

**Larbec** · 04-01-2013, 10:53 PM

Originally Posted by Larbec

I copied the cell, placed in on an empty cell and this is what I got E

I just noticed something. The cells that are listed as B250. C250, D250 are really in columns D,J and P. Does this mean it is linked to another pages getting information from B250,C250,D250?

**Larbec** · 04-02-2013, 12:04 AM

Here is the book for you to look at it if you do not mind

**FDibbins** · 04-02-2013, 12:24 AM

Im not sure what you mean by

The cells that are listed as B250. C250, D250 are really in columns D,J and P.

the cells in B, D, F, H, J are all referencing another file on (your?) hard drive...
='C:\Users\Larry\Desktop\POWERBALL\PB books\[(Numbers).xlsx]Sheet1'!F390 (each column is referencing different columns)

Also note that both this and your other thread were in the VBA forum, I would suggest that unless tou know that it is definitely a VBA question, you post in the GENERAL or EXCEL FORMULAS AND FUNCTIONS forums

**Larbec** · 04-02-2013, 12:27 AM

Originally Posted by FDibbins

Im not sure what you mean by

the cells in B, D, F, H, J are all referencing another file on (your?) hard drive...
='C:\Users\Larry\Desktop\POWERBALL\PB books\[(Numbers).xlsx]Sheet1'!F390 (each column is referencing different columns)

Also note that both this and your other thread were in the VBA forum, I would suggest that unless tou know that it is definitely a VBA question, you post in the GENERAL or EXCEL FORMULAS AND FUNCTIONS forums

So sorry, I can move it and yes I have several other books it links to. That means the error is from another book and page?

**Larbec** · 04-02-2013, 12:38 AM

Originally Posted by FDibbins

Im not sure what you mean by

the cells in B, D, F, H, J are all referencing another file on (your?) hard drive...

Also note that both this and your other thread were in the VBA forum, I would suggest that unless tou know that it is definitely a VBA question, you post in the GENERAL or EXCEL FORMULAS AND FUNCTIONS forums

How can I delete this thread and start another one in the correct place?

**Larbec** · 04-02-2013, 07:12 AM

Originally Posted by FDibbins

Im not sure what you mean by

the cells in B, D, F, H, J are all referencing another file on (your?) hard drive...
='C:\Users\Larry\Desktop\POWERBALL\PB books\[(Numbers).xlsx]Sheet1'!F390 (each column is referencing different columns)

Also note that both this and your other thread were in the VBA forum, I would suggest that unless tou know that it is definitely a VBA question, you post in the GENERAL or EXCEL FORMULAS AND FUNCTIONS forums

Does this mean the error could be coming from the Numbers Book? If so, would I look at the cell that has the #VALUE! Error, see what cell it refers to on the Numbers book?

**Larbec** · 04-02-2013, 12:36 AM

Thank for your help

**FDibbins** · 04-02-2013, 12:44 AM

I have moved this thread for you, continue on this 1

**martindwilson** · 04-02-2013, 06:43 AM

now april 1st has come and gone,can you seriously not know how to use a formula?, you keep asking the same questions and do not do the tests asked, do you actually understand anything i posted as you have not tried the tests in the individual work book or checked as requested what is in the cells you are referring to. i give up!

**Larbec** · 04-02-2013, 05:55 PM

Originally Posted by martindwilson

now april 1st has come and gone,can you seriously not know how to use a formula?, you keep asking the same questions and do not do the tests asked, do you actually understand anything i posted as you have not tried the tests in the individual work book or checked as requested what is in the cells you are referring to. i give up!

I am SO sorry Martin and no I do not know how to use excel outside of the basic spread sheets that I have used. I am trying to learn though so I may not understand what your asking out of ignorance of the software. It's NOT that I don't want to, its understanding what you are asking. That is why I am here.

**Larbec** · 04-02-2013, 07:48 AM

Here are some of the codes from the numbers book

=SUMPRODUCT(--MID(CC389,ROW(INDIRECT("1:"&LEN(CC389))),1)) No error
=SUMPRODUCT(--MID(CA389,ROW(INDIRECT("1:"&LEN(CA389))),1)) #VALUE! error
=ABS(LEFT(N389,1)-IF(RIGHT(N389,1)="0",10,RIGHT(N389,1))) No error
=SUMPRODUCT(--MID(CE389,ROW(INDIRECT("1:"&LEN(CE389))),1)) No error
=SUMPRODUCT(--MID(CG389,ROW(INDIRECT("1:"&LEN(CG389))),1)) #VALUE! error

=ABS(LEFT(F389,1)-IF(RIGHT(F389,1)="0",10,RIGHT(F389,1))) #VALUE! error

**JosephP** · 04-02-2013, 07:52 AM

what is in cell CA389?

**Larbec** · 04-02-2013, 05:57 PM

Originally Posted by josephp

what is in cell ca389?

=sumproduct(--mid(ca389,row(indirect("1:"&len(ca389))),1))

**martindwilson** · 04-02-2013, 10:17 AM

ok now we're getting somewhere all these produce value errors
=SUMPRODUCT(--MID(CG389,ROW(INDIRECT("1:"&LEN(CG389))),1))
=SUMPRODUCT(--MID(CA389,ROW(INDIRECT("1:"&LEN(CA389))),1))
=ABS(LEFT(F389,1)-IF(RIGHT(F389,1)="0",10,RIGHT(F389,1)))
so whats in
CG389
CA389
F389
the formula sums the digits in the cell
=SUMPRODUCT(--MID(CG389,ROW(INDIRECT("1:"&LEN(CG389))),1))
so 123 = 6
1230=6
456 =15
if there are any text eg 123abc or if the cell is formatted text and includes spaces or such eg 123"space" you will get a value error same applies to
=ABS(LEFT(F389,1)-IF(RIGHT(F389,1)="0",10,RIGHT(F389,1)))

**Larbec** · 04-02-2013, 11:59 AM

Originally Posted by martindwilson

ok now we're getting somewhere all these produce value errors
=SUMPRODUCT(--MID(CG389,ROW(INDIRECT("1:"&LEN(CG389))),1))
=SUMPRODUCT(--MID(CA389,ROW(INDIRECT("1:"&LEN(CA389))),1))
=ABS(LEFT(F389,1)-IF(RIGHT(F389,1)="0",10,RIGHT(F389,1)))
so whats in
CG389
CA389
F389
the formula sums the digits in the cell
=SUMPRODUCT(--MID(CG389,ROW(INDIRECT("1:"&LEN(CG389))),1))
so 123 = 6
1230=6
456 =15
if there are any text eg 123abc or if the cell is formatted text and includes spaces or such eg 123"space" you will get a value error same applies to
=ABS(LEFT(F389,1)-IF(RIGHT(F389,1)="0",10,RIGHT(F389,1)))

I'm glad were getting somewhere. I'm so sorry but I am a newbie but learning rapidly or at least I hope I am. Lol

I am away from my desktop until this afternoon so I will let you know what's in those cells when I get home.

By looking at what I posted you indicated that they would indeed create an error. Do you know what is incorrect about the code or do you need the additional information? I did notice on the main numbers book, same lines have the same error #VALUE!

Thank you "so" much for your patience and helping me!

**martindwilson** · 04-02-2013, 06:15 PM

still need to know what is in ca389 you cant have =sumproduct(--mid(ca389,row(indirect("1:"&len(ca389))),1)) in that cell

**Larbec** · 04-02-2013, 06:29 PM

Originally Posted by martindwilson

still need to know what is in ca389 you cant have =sumproduct(--mid(ca389,row(indirect("1:"&len(ca389))),1)) in that cell

If I copy and paste onto a blank cell it is a # sign. It also states the same error code #VALUE!

I hope this answers your question. If not PLEASE give me a step by step what I need to do to give you the answer you are looking for. But this is what is in the cell to my knowledge

**martindwilson** · 04-02-2013, 06:41 PM

just tell us what is in cell ca389

**Larbec** · 04-02-2013, 07:27 PM

Originally Posted by martindwilson

just tell us what is in cell ca389

I can't give you any additional information. I've already posted whats in the cell when I click on it. I don't know what else I could possibly give you?

**martindwilson** · 04-02-2013, 07:39 PM

well i didn't see the memo
so you click on ca389 and you see =sumproduct(--mid(ca389,row(indirect("1:"&len(ca389))),1)) ? or what? what is shown in the formula bar at the top?

**Larbec** · 04-02-2013, 08:15 PM

Originally Posted by martindwilson

well i didn't see the memo
so you click on ca389 and you see =sumproduct(--mid(ca389,row(indirect("1:"&len(ca389))),1)) ? or what? what is shown in the formula bar at the top?

=SUMPRODUCT(--MID(CA389,ROW(INDIRECT("1:"&LEN(CA389))),1))

I will reload the sheet

**Larbec** · 04-02-2013, 08:45 PM

What I just noticed is once a number is placed in the numbers book the error code goes away. Why?

**dredwolf** · 04-02-2013, 09:14 PM

On your 'Numbers EO' sheet, row 2- 149 are all = '?', thus they are not numbers, and you get the error message instead of values,so whatever is in 'C:\Users\Larry\Desktop\POWERBALL\PB books\[(Numbers).xlsx]Sheet1'!F142 (for cell B2, as an example) is being returned to as ? (I used this in M2 =B2 , copied it down to the end of the list, to show what the cell was returning)
So the problem is in this workbook (C:\Users\Larry\Desktop\POWERBALL\PB books\[(Numbers).xlsx]), not the one you uploaded

Hope this helps

**Larbec** · 04-02-2013, 09:59 PM

Originally Posted by dredwolf

On your 'Numbers EO' sheet, row 2- 149 are all = '?', thus they are not numbers, and you get the error message instead of values,so whatever is in 'C:\Users\Larry\Desktop\POWERBALL\PB books\[(Numbers).xlsx]Sheet1'!F142 (for cell B2, as an example) is being returned to as ? (I used this in M2 =B2 , copied it down to the end of the list, to show what the cell was returning)
So the problem is in this workbook (C:\Users\Larry\Desktop\POWERBALL\PB books\[(Numbers).xlsx]), not the one you uploaded

Hope this helps

I sent up a dummy file using the same code just changing the numbers around in the columns. The way it works is, I write the number down in the numbers book. That number is linked to the other book placing a "E" or "O" for even or odd. Once I place a number in the numbers book the ? goes away in the E or O book and is replaced by the E or O. For some reason I am getting a # symbol in some cells in the E O book. Maybe its suppose to work like this

Error in #VALUE

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Re: Error in #VALUE

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions