I have a spreadsheet that has invoice numbers in column F which i want to
use to link my data into another worksheet.

0001173631

and in another worksheet I have a list of accts but the field that contains
the invoice number is formatted with locationnumber/invoicenumber/amount all
concatenated together like this:

0001700011736310000000331

I wanted to link so that i can return the value in column H of the first
shreadsheet by using the invoice number but wasnt sure how to accomplish - I
have it like this:

=VLOOKUP(F2,'[East 12 months of Past Dues.xls]Sheet1'!$F:$G,1,0)
and it doesnt work as F2 is the long number

Try this:

=VLOOKUP(MID(F2,6,10),'[East 12 months of Past Dues.xls]Sheet1'!$F:$G,
1,0)

Hope this helps.

Pete

On Jul 11, 4:17*pm, Belinda7237
wrote:
I have a spreadsheet that has invoice numbers in column F which i want to
use to link my data into another worksheet.

0001173631

and in another worksheet I have a list of accts but the field that contains
the invoice number is formatted with locationnumber/invoicenumber/amount all
concatenated together like this:

0001700011736310000000331

I wanted to link so that i can return the value in column H of the first
shreadsheet by using the invoice number but wasnt sure how to accomplish - I
have it like this:

=VLOOKUP(F2,'[East 12 months of Past Dues.xls]Sheet1'!$F:$G,1,0)
and it doesnt work as F2 is the long number

Give this a go:
=SUMPRODUCT(ISNUMBER(SEARCH("*"&A1,A1:A5))*((H1:H5 )))


Regards,
Ryan---

--
RyGuy


"Pete_UK" wrote:

Try this:

=VLOOKUP(MID(F2,6,10),'[East 12 months of Past Dues.xls]Sheet1'!$F:$G,
1,0)

Hope this helps.

Pete

On Jul 11, 4:17 pm, Belinda7237
wrote:
I have a spreadsheet that has invoice numbers in column F which i want to
use to link my data into another worksheet.

0001173631

and in another worksheet I have a list of accts but the field that contains
the invoice number is formatted with locationnumber/invoicenumber/amount all
concatenated together like this:

0001700011736310000000331

I wanted to link so that i can return the value in column H of the first
shreadsheet by using the invoice number but wasnt sure how to accomplish - I
have it like this:

=VLOOKUP(F2,'[East 12 months of Past Dues.xls]Sheet1'!$F:$G,1,0)
and it doesnt work as F2 is the long number

your the best, works perfectly - i knew i should have asked the community two
days ago!

"Pete_UK" wrote:

Try this:

=VLOOKUP(MID(F2,6,10),'[East 12 months of Past Dues.xls]Sheet1'!$F:$G,
1,0)

Hope this helps.

Pete

On Jul 11, 4:17 pm, Belinda7237
wrote:
I have a spreadsheet that has invoice numbers in column F which i want to
use to link my data into another worksheet.

0001173631

and in another worksheet I have a list of accts but the field that contains
the invoice number is formatted with locationnumber/invoicenumber/amount all
concatenated together like this:

0001700011736310000000331

I wanted to link so that i can return the value in column H of the first
shreadsheet by using the invoice number but wasnt sure how to accomplish - I
have it like this:

=VLOOKUP(F2,'[East 12 months of Past Dues.xls]Sheet1'!$F:$G,1,0)
and it doesnt work as F2 is the long number

Thanks for the feedback, Belinda - you'll know what to do next time !!

Pete

On Jul 11, 6:27*pm, Belinda7237
wrote:
your the best, works perfectly - i knew i should have asked the community two
days ago!



"Pete_UK" wrote:
Try this:

=VLOOKUP(MID(F2,6,10),'[East 12 months of Past Dues.xls]Sheet1'!$F:$G,
1,0)

Hope this helps.

Pete