Hi all -
I have two spreadsheets that index data from one spreadsheet into another.
Works fine, no problem... HOWEVER now it has become necessary that the data
in the source file has multiple lines entered in the one cell (using
Alt+Enter)being indexed. This means my formula in the destination file needs
to "see" each line in the source cell, along with all cells in the column
being indexed.

Example:
forumla in other spreadsheet: =INDEX('[spreadsheet
A.xls]Sheet1'!$A:$B,MATCH(N18,'[spreadsheet A.xls]Sheet1'!$A:$A,0),2)

A B
1 957 20
2 254 30
256
321
3 500 19

If N18 in spreadsheet B equals 256, I should get a result of 30.

Any ideas how I can get this formula to work with the Alt+Enter??

Try:

=if(iserror(INDEX('[spreadsheet
A.xls]Sheet1'!$A:$B,MATCH("*"&N18&"*",'[spreadsheet
A.xls]Sheet1'!$A:$A,0),2)),INDEX('[spreadsheet
A.xls]Sheet1'!$A:$B,MATCH(N18,'[spreadsheet
A.xls]Sheet1'!$A:$A,0),2),INDEX('[spreadsheet
A.xls]Sheet1'!$A:$B,MATCH("*"&N18&"*",'[spreadsheet
A.xls]Sheet1'!$A:$A,0),2))

Although, since you looking up on the left column, you could try vlookup
instead.

--
Regards,
Tom Ogilvy




"NewBike" <(E-Mail Removed)> wrote in message
news:1BCD34E8-5A6E-4D09-9B0F-(E-Mail Removed)...
> Hi all -
> I have two spreadsheets that index data from one spreadsheet into another.
> Works fine, no problem... HOWEVER now it has become necessary that the
> data
> in the source file has multiple lines entered in the one cell (using
> Alt+Enter)being indexed. This means my formula in the destination file
> needs
> to "see" each line in the source cell, along with all cells in the column
> being indexed.
>
> Example:
> forumla in other spreadsheet: =INDEX('[spreadsheet
> A.xls]Sheet1'!$A:$B,MATCH(N18,'[spreadsheet A.xls]Sheet1'!$A:$A,0),2)
>
> A B
> 1 957 20
> 2 254 30
> 256
> 321
> 3 500 19
>
> If N18 in spreadsheet B equals 256, I should get a result of 30.
>
> Any ideas how I can get this formula to work with the Alt+Enter??

Hi Tom -
Sorry for the two posts, the first one gave me an error that it did not
post...

I just tried the formula and it worked for cells with more than one line,
but now does not pick up cells with single lines.


"Tom Ogilvy" wrote:

> Try:
>
> =if(iserror(INDEX('[spreadsheet
> A.xls]Sheet1'!$A:$B,MATCH("*"&N18&"*",'[spreadsheet
> A.xls]Sheet1'!$A:$A,0),2)),INDEX('[spreadsheet
> A.xls]Sheet1'!$A:$B,MATCH(N18,'[spreadsheet
> A.xls]Sheet1'!$A:$A,0),2),INDEX('[spreadsheet
> A.xls]Sheet1'!$A:$B,MATCH("*"&N18&"*",'[spreadsheet
> A.xls]Sheet1'!$A:$A,0),2))
>
> Although, since you looking up on the left column, you could try vlookup
> instead.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "NewBike" <(E-Mail Removed)> wrote in message
> news:1BCD34E8-5A6E-4D09-9B0F-(E-Mail Removed)...
> > Hi all -
> > I have two spreadsheets that index data from one spreadsheet into another.
> > Works fine, no problem... HOWEVER now it has become necessary that the
> > data
> > in the source file has multiple lines entered in the one cell (using
> > Alt+Enter)being indexed. This means my formula in the destination file
> > needs
> > to "see" each line in the source cell, along with all cells in the column
> > being indexed.
> >
> > Example:
> > forumla in other spreadsheet: =INDEX('[spreadsheet
> > A.xls]Sheet1'!$A:$B,MATCH(N18,'[spreadsheet A.xls]Sheet1'!$A:$A,0),2)
> >
> > A B
> > 1 957 20
> > 2 254 30
> > 256
> > 321
> > 3 500 19
> >
> > If N18 in spreadsheet B equals 256, I should get a result of 30.
> >
> > Any ideas how I can get this formula to work with the Alt+Enter??
>
>
>

If it worked before on the same data set, it should continue to work. Your
original formula is included in the formula I posted.

--
Regards,
Tom Ogilvy


"NewBike" <(E-Mail Removed)> wrote in message
news:5F5FE325-19BE-4E17-A816-(E-Mail Removed)...
> Hi Tom -
> Sorry for the two posts, the first one gave me an error that it did not
> post...
>
> I just tried the formula and it worked for cells with more than one line,
> but now does not pick up cells with single lines.
>
>
> "Tom Ogilvy" wrote:
>
>> Try:
>>
>> =if(iserror(INDEX('[spreadsheet
>> A.xls]Sheet1'!$A:$B,MATCH("*"&N18&"*",'[spreadsheet
>> A.xls]Sheet1'!$A:$A,0),2)),INDEX('[spreadsheet
>> A.xls]Sheet1'!$A:$B,MATCH(N18,'[spreadsheet
>> A.xls]Sheet1'!$A:$A,0),2),INDEX('[spreadsheet
>> A.xls]Sheet1'!$A:$B,MATCH("*"&N18&"*",'[spreadsheet
>> A.xls]Sheet1'!$A:$A,0),2))
>>
>> Although, since you looking up on the left column, you could try vlookup
>> instead.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>>
>>
>> "NewBike" <(E-Mail Removed)> wrote in message
>> news:1BCD34E8-5A6E-4D09-9B0F-(E-Mail Removed)...
>> > Hi all -
>> > I have two spreadsheets that index data from one spreadsheet into
>> > another.
>> > Works fine, no problem... HOWEVER now it has become necessary that the
>> > data
>> > in the source file has multiple lines entered in the one cell (using
>> > Alt+Enter)being indexed. This means my formula in the destination file
>> > needs
>> > to "see" each line in the source cell, along with all cells in the
>> > column
>> > being indexed.
>> >
>> > Example:
>> > forumla in other spreadsheet: =INDEX('[spreadsheet
>> > A.xls]Sheet1'!$A:$B,MATCH(N18,'[spreadsheet A.xls]Sheet1'!$A:$A,0),2)
>> >
>> > A B
>> > 1 957 20
>> > 2 254 30
>> > 256
>> > 321
>> > 3 500 19
>> >
>> > If N18 in spreadsheet B equals 256, I should get a result of 30.
>> >
>> > Any ideas how I can get this formula to work with the Alt+Enter??
>>
>>
>>

Not easy to do, because of possible substring matches.
Here is an ugly formula that should work:
=INDEX( '[spreadsheetA.xls]Sheet1'!$A$1:$B$10, SUMPRODUCT( ( ( '[spreadsheet
A.xls]Sheet1'!$A$1:$A$10 = N18 ) + ( ( COUNTIF( '[spreadsheet
A.xls]Sheet1'!$A$1:$A$10, N18 ) = 0 ) * ( ( LEFT( '[spreadsheet
A.xls]Sheet1'!$A$1:$A$10, LEN( N18 ) + 1 ) = N18 & CHAR( 10 ) ) + ( RIGHT(
'[spreadsheet A.xls]Sheet1'!$A$1:$A$10, LEN( N18 ) + 1 ) = CHAR( 10 ) & N18 )
+ ( LEN( '[spreadsheet A.xls]Sheet1'!$A$1:$A$10 ) - LEN( SUBSTITUTE(
'[spreadsheet A.xls]Sheet1'!$A$1:$A$10, CHAR( 10 ) & N18 & CHAR( 10 ), "" ) )
- 2 = LEN( N18 ) ) ) ) ) * ROW( INDIRECT( "$1:$" & ROWS( '[spreadsheet
A.xls]Sheet1'!$A$1:$A$10 ) ) ) ), 2 )

Adjust to your ranges (Don't use full rows !).
It has limitations, it doesn't check for errors and duplicates are adding
up...
--
Festina Lente


"NewBike" wrote:

> Hi all -
> I have two spreadsheets that index data from one spreadsheet into another.
> Works fine, no problem... HOWEVER now it has become necessary that the data
> in the source file has multiple lines entered in the one cell (using
> Alt+Enter)being indexed. This means my formula in the destination file needs
> to "see" each line in the source cell, along with all cells in the column
> being indexed.
>
> Example:
> forumla in other spreadsheet: =INDEX('[spreadsheet
> A.xls]Sheet1'!$A:$B,MATCH(N18,'[spreadsheet A.xls]Sheet1'!$A:$A,0),2)
>
> A B
> 1 957 20
> 2 254 30
> 256
> 321
> 3 500 19
>
> If N18 in spreadsheet B equals 256, I should get a result of 30.
>
> Any ideas how I can get this formula to work with the Alt+Enter??