Alt+Enter Formula/VBA help

[Guest]

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??

 

[Tom Ogilvy]

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.

 

[Guest]

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.

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

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??

 

[Tom Ogilvy]

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

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.

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

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??

 

[Guest]

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...