| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alt+Enter Formula/VBA help
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?? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alt+Enter Formula/VBA help
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" wrote in message ... 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?? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alt+Enter Formula/VBA help
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" wrote in message ... 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?? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alt+Enter Formula/VBA help
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" wrote in message ... 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" wrote in message ... 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?? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alt+Enter Formula/VBA help
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?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| When I enter a formula, Excel shows the formula not the results | Excel Worksheet Functions | |||
| is it possible to enter in text over a formula without deleting the formula? | Excel Discussion (Misc queries) | |||
| can you wrap formula results via a formula eg. Alt Enter | Excel Discussion (Misc queries) | |||
| What does hitting Ctrl + Shift + Enter to enter a formula do??? Help a n00b out. | Excel Worksheet Functions | |||
| Cannot enter formula in a cell after removing a circular formula | Excel Worksheet Functions | |||
Linear Mode
