Need a macro to do what this VLOOKUP does

[Tim]

Frank helped me out with the the following VLOOKUP

=IF(ISNA(VLOOKUP('Sheet2'!R33,'Sheet10'!
F1:G59,2)),"",VLOOKUP('Sheet1'!R33,'Sheet10'!F1:G59,2)) &
CHAR(10) & CHAR(10) &
IF(ISNA(VLOOKUP('Sheet2'!R34,'Sheet10'!
F1:G59,2)),"",VLOOKUP('Sheet1'!R34,'Sheet10'!F1:G59,2)) &
CHAR(10) & CHAR(10) &
IF(ISNA(VLOOKUP('Sheet2'!R35,'Sheet10'!
F1:G59,2)),"",VLOOKUP('Sheet1'!R35,'Sheet10'!F1:G59,2)) &
CHAR(10) & CHAR(10) &
IF(ISNA(VLOOKUP('Sheet2'!R36,'Sheet10'!
F1:G59,2)),"",VLOOKUP('Sheet1'!R36,'Sheet10'!F1:G59,2))

The problem is after I finished it, I realized that I
would need to edit the data once it was in the cell, but
with the VLOOKUP I was unable to edit the data.
Is there a way to do what I'm trying to do with a macro.

Basically I have cells in 'Sheet1'(R33-R36) that may or
maynot have data in them(they are also VLOOKUP's). If
there is data in any of these cells, I want them to look
it up in 'Sheet10' and grab the info in row 2 and place
in cell A16 of 'Sheet6'. But each entry from(R33 to R36)
would be a new paragraph.
The other part I would like to add is (AND FURTHER THAT)
inbetween each new paragraph(if there is data in cells
R34, R35 ,R36). If not I don't need (AND FURTHER THAT) to
appear.

Any help would be greatly appreciated.

 

[Frank Kabel]

Hi Tim
this can be done with formulas (though they are getting long). You may
define a name (goto 'Insert - Name - Define name') that stores your
text. Lets say you define the name:
sep_text = CHAR(10) & "(AND FURTHER THAT)" & CHR(10) ' change this
to your needs
try the following formula
=IF(ISNA(VLOOKUP('Sheet2'!R33,'Sheet10'!F1:G59,2)),"",VLOOKUP('Sheet1'!
R33,'Sheet10'!F1:G59,2)) &
IF(ISNA(VLOOKUP('Sheet2'!R34,'Sheet10'!F1:G59,2)),"",sep_text &
VLOOKUP('Sheet1'!R34,'Sheet10'!F1:G59,2)) &
IF(ISNA(VLOOKUP('Sheet2'!R35,'Sheet10'!F1:G59,2)),"",sep_text &
VLOOKUP('Sheet1'!R35,'Sheet10'!F1:G59,2)) &
IF(ISNA(VLOOKUP('Sheet2'!R36,'Sheet10'! F1:G59,2)),"",sep_text &
VLOOKUP('Sheet1'!R36,'Sheet10'!F1:G59,2))

HTH
Frank

 

[Don Guillett]

You can "edit" the cell by using the INDIRECT function. Example.
=IF(ISNUMBER(D5),VLOOKUP($A5,INDIRECT("Data!B1:X1000"),5,FALSE),"")

 

[Tim]

I having a hard time understanding this(Newbie). But what
I need to do is edit all the text after it is in cell
that this formula is in.

 

[Frank Kabel]

Hi Tim
if you want to edit the formula result (your text) you can't do this in
the same cell. You either have a formula (with its result) or a static
value (which can be altered).

Frank

 

[Tim]

I kind of figured that I would'nt be able to edit the
resulting text, using the formula. I didn't think about
that when I came up with the idea, was excited that I
thought of a time saving measure, before thinking what
the end result was.
More training needed, but having fun learning...