Obtaining Value From Index/Match Cell

[JPARKHURST]

I'm sorry if this has been covered before, I have looked through and searched and was not able to find it [yet]. It seems to me this should be pretty obvious, but I must not be doing something correctly.

I have a cell which uses a job # to obtain the balance left on the job. This cell is the result of an index match which obtains the job qty from a dispatch sheet.

My problem comes when I try to work with the value fetched by the cell. In this particular case, my cell has pulled in "99". When I attempt to work with this value, I am not able. I have tried everything other than pasting the value into the cell, as I want this process to be more automated than that (hence the index and match). But if I, say, try to add 1 to the cell, it results in "0". If I try to divide this number, I get a "0". What I am particularly wanting to do is to compare this value to an equation and have it let me know what the results are.

A JOB

B DUE

C DESCRIPTION

D balance

E Test

F A Start

G A End

H Time

I Rate

J Weekly

K 25-Oct

L 26-Oct

M 27-Oct

N 28-Oct

O 29-Oct

66380

10/19

10 DWR 52IN CABINET BLACK

99

0 (e3+1)

10/24/16

10/29/16

6

660

3960

0

6

<colgroup><col><col><col><col span="2"><col span="2"><col span="8"></colgroup><tbody>
</tbody>

BIG PICTURE:
I am attempting to create a schedule which takes in our dispatch list and automatically (or semi) distributes new jobs across multiple shifts. This is my second or third trial of ideas, so I'm not set on anything, just trying to get a few of the basics down. This problem is particularly frustrating as it seems as if i'm missing something completely obvious.

TIA,

Jon

 

[FDibbins]

The data you are returning is probably text that just looks like a number. try adding -- in front of your formula...
=--your-formula

 

[JPARKHURST]

The data you are returning is probably text that just looks like a number. try adding -- in front of your formula...
=--your-formula

Hmmm, no go.

=IFERROR(INDEX(Job_Dispatch!$H$2:$H$500,MATCH($C3,Job_Dispatch!$A$2:$A$500,0)),"")

to

=--IFERROR(INDEX(Job_Dispatch!$H$2:$H$500,MATCH($C3,Job_Dispatch!$A$2:$A$500,0)),"")

Results in "99" disappearing to a 0.

I have also tried int(), text(,"numeral"), etc. without success.

However, your suggestion may be correct in that I am showing the 'value' on the right side of the cell, though that may have been from an alignment I did.

isnumber(), istext(), both also result in a "0".

Thanks for the help, though, much appreciated.

 

[JPARKHURST]

Wow. STUPID mistake. As I said, this was one of several version I was looking at - format, logic, etc. When I changed this one up, I had neglected to change my cell assignment. I can't believe it even kep the original value.

I think I have it working now. I'll check in the morning, though, after I've had some rest and coffee.

 

[FDibbins]

=--IFERROR(INDEX(Job_Dispatch!$H$2:$H$500,MATCH($C3,Job_Dispatch!$A$2:$A$500,0)),"")

Not quite...
=IFERROR(--INDEX(Job_Dispatch!$H$2:$H$500,MATCH($C3,Job_Dispatch!$A$2:$A$500,0)),"")

isnumber(), istext(), both also result in a "0".

If used correctly, isnumber() and istext() should return TRUE or FALSE, not any value...
=isnumber(A1)
where A1 would be the cell you are testing

 

[JPARKHURST]

Thank you for correcting my syntax on the --. what exactly does this do? I tried to search for it, but wasn't very successful.

Understood on the isnum/text. I'm not sure why it would return a "0". I often use this as a check as our part numbers are alpha numeric. It seems to me that it should have just returned a 'false', but instead I got a '0'. Then again, I'm not sure exactly how the cell was keeping its value, since all of the references were jacked up.

Thanks again for your help, I'm sure I'll be back here before long. This worksheet is proving to be more than a little bit challenging. (and yes, I'm probably borderline misusing excel, but it's what we have available).

 

[FDibbins]

-- (double unary/double negative) is a way to convert a text number to a real number.

Applying any math operator to a text number will convert it...-- or *1 or +0