| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a macro to do what this VLOOKUP does
Frank helped me out with the the following VLOOKUP
=IF(ISNA(VLOOKUP('Sheet2'!R33,'Sheet10'! F1:G59,2)),"",VLOOKUP('Sheet1'!R33,'Sheet10'!F1:G5 9,2)) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP('Sheet2'!R34,'Sheet10'! F1:G59,2)),"",VLOOKUP('Sheet1'!R34,'Sheet10'!F1:G5 9,2)) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP('Sheet2'!R35,'Sheet10'! F1:G59,2)),"",VLOOKUP('Sheet1'!R35,'Sheet10'!F1:G5 9,2)) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP('Sheet2'!R36,'Sheet10'! F1:G59,2)),"",VLOOKUP('Sheet1'!R36,'Sheet10'!F1:G5 9,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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need a macro to do what this VLOOKUP does
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 Tim wrote: Frank helped me out with the the following VLOOKUP =IF(ISNA(VLOOKUP('Sheet2'!R33,'Sheet10'! F1:G59,2)),"",VLOOKUP('Sheet1'!R33,'Sheet10'!F1:G5 9,2)) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP('Sheet2'!R34,'Sheet10'! F1:G59,2)),"",VLOOKUP('Sheet1'!R34,'Sheet10'!F1:G5 9,2)) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP('Sheet2'!R35,'Sheet10'! F1:G59,2)),"",VLOOKUP('Sheet1'!R35,'Sheet10'!F1:G5 9,2)) & CHAR(10) & CHAR(10) & IF(ISNA(VLOOKUP('Sheet2'!R36,'Sheet10'! F1:G59,2)),"",VLOOKUP('Sheet1'!R36,'Sheet10'!F1:G5 9,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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need a macro to do what this VLOOKUP does
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. -----Original Message----- You can "edit" the cell by using the INDIRECT function. Example. =IF(ISNUMBER(D5),VLOOKUP($A5,INDIRECT("Data! B1:X1000"),5,FALSE),"") -- Don Guillett SalesAid Software "Tim" wrote in message ... 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:G5 9,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. . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need a macro to do what this VLOOKUP does
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 wrote: 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. -----Original Message----- You can "edit" the cell by using the INDIRECT function. Example. =IF(ISNUMBER(D5),VLOOKUP($A5,INDIRECT("Data! B1:X1000"),5,FALSE),"") -- Don Guillett SalesAid Software "Tim" wrote in message ... 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:G5 9,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. . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need a macro to do what this VLOOKUP does
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... -----Original Message----- 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 wrote: 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. -----Original Message----- You can "edit" the cell by using the INDIRECT function. Example. =IF(ISNUMBER(D5),VLOOKUP($A5,INDIRECT("Data! B1:X1000"),5,FALSE),"") -- Don Guillett SalesAid Software "Tim" wrote in message ... 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. . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP Macro? | Excel Worksheet Functions | |||
| VLOOKUP Macro? | Excel Worksheet Functions | |||
| Macro - Vlookup | Excel Discussion (Misc queries) | |||
| Vlookup Macro | Excel Discussion (Misc queries) | |||
| Vlookup Macro? | Excel Discussion (Misc queries) | |||
Linear Mode
