Need a macro to do what this VLOOKUP does

  • Thread starter Thread starter Tim
  • Start date Start date
T

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.
 
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
 
You can "edit" the cell by using the INDIRECT function. Example.
=IF(ISNUMBER(D5),VLOOKUP($A5,INDIRECT("Data!B1:X1000"),5,FALSE),"")
 
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.
 
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
 
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...
 
Back
Top