Inserting Text into worksheet???

  • Thread starter Thread starter Michael Vaughan
  • Start date Start date
M

Michael Vaughan

Helly everyone,

I am at a lost here. I have a conditional statement that I need to place
which recalls lots of text. What is the best way to do this??? The
condition is:
=if(A5="N2AJ",text1,if(A5="N9421D",text2,"")

Now, I can recall information ok, but it is the text that I am having a
problem with. The text has multiple lines such as this:
N2AJ:
1. This is a long line of text that goes here.
2. This is a another long line of information.
3. And yet another long line.

N9421D:
1. This is a long line of text that goes here.
2. This is a another long line of information.
3. And yet another long line.

Now, the problem is that the cells format won't allow this. What can I do
to get it to look just like above and be able to recall it with a VLOOKUP??

Any Suggestions, or am I going about this all wrong??
 
One way, perhaps something along these lines ..

Supposing the data below is in col A, A1 down in Sheet1
N2AJ:
1. This is a long line of text that goes here.
2. This is a another long line of information.
3. And yet another long line.

N9421D:
1. This is a long line of text that goes here.
2. This is a another long line of information.
3. And yet another long line.
Click to expand...

In Sheet2
-----------
In A1 will be input the identifier e.g.: "N2AJ"

Select B1:B3 and paste into the formula bar:
=IF(A1="","",OFFSET(Sheet1!$A$1,MATCH(TRIM(A1)&":",Sheet1!A:A,0),,3))
Array-enter the formula, i.e. press CTRL+SHIFT+ENTER

B1:B3 will return the 3 lines of text corresponding to the identifier input
in A1
If A1 is cleared, nothing ("") will display
 
=IF(A1="","",OFFSET(Sheet1!$A$1,MATCH(TRIM(A1)&":",Sheet1!A:A,0),,3))

If there's a possibility that the input in A1 of Sheet2
might have no matching identifier in col A of Sheet1,
it might be better to include an error trap

Replace the formula in B1:B3 of Sheet2 with:
=IF(A1="","",IF(ISNA(MATCH(TRIM(A1)&":",Sheet1!A:A,0)),"No match for input
in A1",OFFSET(Sheet1!$A$1,MATCH(TRIM(A1)&":",Sheet1!A:A,0),,3)))
Array-enter as before

Any non-matches will now return the phrase "No match for input in A1"
 
Max said:
If there's a possibility that the input in A1 of Sheet2
might have no matching identifier in col A of Sheet1,
it might be better to include an error trap

Replace the formula in B1:B3 of Sheet2 with:
=IF(A1="","",IF(ISNA(MATCH(TRIM(A1)&":",Sheet1!A:A,0)),
"No match for input in A1",OFFSET(Sheet1!$A$1,
MATCH(TRIM(A1)&":",Sheet1!A:A,0),,3)))
Click to expand...
....

Are you sure you don't have an off-by-one error? If the first match were
found in cell A65535, then the B1 result cell would reference A65536, the
last cell in col A of Sheet1, and cells B2 and B3 would display #REF!. Even
if the indexing is correct, there's no need to check past row 65534.

Also, if you're concerned about stray spaces in A1 when there are other
characters too, perhaps you should also be concerned about them when they
may be the only characters in the cell.

=IF(TRIM(A1)="","",...
 
Harlan Grove said:
Are you sure you don't have an off-by-one error? If the first match were
found in cell A65535, then the B1 result cell would reference A65536, the
last cell in col A of Sheet1, and cells B2 and B3 would display #REF!. Even
if the indexing is correct, there's no need to check past row 65534.
Click to expand...

No, light testing returned results ok. The retrieval intentionally skips
returning
the first line per data-set, i.e. the identifier itself (e.g.: "N2AJ").
Admit I didn't test the 65Kth end. I was lazy in using the entire cols ref
(Sheet1!A:A). Didn't think the data would extend that far down said:
Also, if you're concerned about stray spaces in A1 when there are other
characters too, perhaps you should also be concerned about them when they
may be the only characters in the cell.

=IF(TRIM(A1)="","",...
Click to expand...

Yes, I missed this part out. Thanks for the refinement.

I missed mentioning a couple of assumptions, though:
a. Identifiers in col A of Sheet1 (e.g.: "N2AJ") are unique
b. Data structure is in groups of 3 lines below each identifier
 
Back
Top