formula help needed

[djackson]

I am working in to seperate work books(call them wrk1 and wrk2), I a
wanting to reference back to wrk1, say cell J2. If the value is 0 o
larger I want to add 1 to it number from the wrk1 to give a runnin
total in wrk2.

Can someone please give me a little advise on this

 

[CLR]

If both workbooks are open, the following formula in wrk2 will deliver the
value in wrk1 sheet1 cell J2

=[wrk1.xls]Sheet1!$J$2

then you can do anything you want to with it.........

this formula will retrieve it, and add one to the number if it's 0 or
more........

=IF([wrk1.xls]Sheet1!$J$2>=0,[wrk1.xls]Sheet1!$J$2+1,"value is <0")

hth
Vaya con Dios,
Chuck, CABGx3

 

[Max]

Maybe something like this:

Put in a cell, say
in A2 in Sheet1 in wrk2.xls:

=IF(ISBLANK([wrk1.xls]Sheet1!J2),"",IF(ISTEXT([wrk1.xls]
Sheet1!J2),"Text??",IF([wrk1.xls]Sheet1!J2>=0,[wrk1.xls]
Sheet1!J2+1,"")))

The above will read cell J2 in Sheet1 in wrk1.xls
and return either:

a. "Blank", i.e. "", if J2 is blank
b. An error phrase "Text??", if J2 contains text
c. The value J2 + 1, if J2 has a number greater than or
equal to zero

 

[Max]

SLight amendment to the line:

a. "Blank", i.e. "", if J2 is blank

Should read as

a. "Blank", i.e. "", if J2 is blank or J2 contains a

number less than zero

Rgds
Max
xl 97

 

[djackson]

how would i incorporate the lookup function into this to ensure I a
matching a value in a2 in both wrk1, and wrk2 before adding this value
I have attempted a few times but could not get the result I am lookin
for.

Thank

 

[Max]

Perhaps you could post the formula that's not working for you ?
Might be easier to zoom in that way ..

 

[djackson]

to get the initial sum from wrk1 im using "VLOOKUP(A2
'[wrk1.xls]Sheet1'!$A$2:'[wrk1.xls]Sheet'!$H$59, 10, FALSE)

then simply adding to that value in a seperate box, I know its probabl
something stupid I'm doing.

Any advic

 

[Max]

Assuming your table_array is in range A2:H59 in Sheet1 of wrk1.xls
(inferred from your formula)

and the lookup values are in col A in Sheet1 of wrk2.xls, row2 down

With wrk1.xls open,

you could try putting in say B2 in Sheet1 of wrk2.xls :

=VLOOKUP(TRIM(A2),[wrk1.xls]Sheet1!$A$2:$H$59,8,FALSE)

and then copy B2 down as many rows as there are lookup values listed in col
A

I've corrected the column index number to say, "8", instead of the original
"10"
as the maximum cols in your table_array [i.e. range A2:H59 in Sheet1 of
wrk1.xls]
is only 8 (cols A to H). The col index can only be a number between 1 - 8.

Adjust the amended vlookup above [syntax is now ok] accordingly to suit
(e.g. change the sheet reference if it's not Sheet1, and also change the col
index number )

TRIM() is added for increased robustness in matching (especially for
matching of text)
just in case there are any inadvertent "invisible" leading, in-between or
trailing spaces
in the lookup text values in col A

--

If you need error trapping to cater for the possibility of unmatched lookups
in col A,

Put instead in B2 in Sheet1 of wrk2.xls :

=IF(ISNA(MATCH(TRIM(A2),[wrk1.xls]Sheet1!$A:$A,FALSE)),"No
match",VLOOKUP(TRIM(A2),[wrk1.xls]Sheet1!$A$2:$H$59,8,FALSE))

which will return the phrase "No match" for unmatched cases
[instead of #NA's]

Copy B2 down

 

[Max]

Oops, line

.. any inadvertent "invisible" leading, in-between or

should read as

 

[djackson]

Thanks Max worked pretty good for my needs, I caught the col number a
soon as I typed it in.

Thank

 

[Max]

Pleased to hear that !
Thanks for the feedback