Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONCATENATE problem
I am trying to constuct a table of DDE links to be referenceby offset amd
match funtions on another sheet. There are hundreds of these links, so tomake entering them a bit easier I'm trying to use CONCATENATE to help reduce the time. The liks look like this =the962|IQVALUE!'L14O011S1(V)' =the962|IQVALUE!'L14O011 this bit stays the same But S1 will change and so will the text inside the brackets. if I place =the962|IQVALUE!'L14O011 in one cell, S1 in another and (V)' in another and use =CONCATENATE(Cell1, Cell2, Cell3) the = in cell 1 makes it return "Cell1" in the CONCATENATE result. Is there a way around this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONCATENATE problem
try
=B3&B4&B5 or without typing the ( ) =B3&B4&"("&B5&")" -- Don Guillett SalesAid Software "Mark Dullingham" wrote in message ... I am trying to constuct a table of DDE links to be referenceby offset amd match funtions on another sheet. There are hundreds of these links, so tomake entering them a bit easier I'm trying to use CONCATENATE to help reduce the time. The liks look like this =the962|IQVALUE!'L14O011S1(V)' =the962|IQVALUE!'L14O011 this bit stays the same But S1 will change and so will the text inside the brackets. if I place =the962|IQVALUE!'L14O011 in one cell, S1 in another and (V)' in another and use =CONCATENATE(Cell1, Cell2, Cell3) the = in cell 1 makes it return "Cell1" in the CONCATENATE result. Is there a way around this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONCATENATE problem
Didn't know about that, so try
=right(B3,len(b3)-1)&B4&"("&B5&")" -- Don Guillett SalesAid Software "Mark Dullingham" wrote in message ... Don Thanks for your reply, unfortunately it didn't solve the promblem. The '=' at the begining of the DDE link is still causing the value to be returned as #NAME? Mark "Don Guillett" wrote: try =B3&B4&B5 or without typing the ( ) =B3&B4&"("&B5&")" -- Don Guillett SalesAid Software "Mark Dullingham" wrote in message ... I am trying to constuct a table of DDE links to be referenceby offset amd match funtions on another sheet. There are hundreds of these links, so tomake entering them a bit easier I'm trying to use CONCATENATE to help reduce the time. The liks look like this =the962|IQVALUE!'L14O011S1(V)' =the962|IQVALUE!'L14O011 this bit stays the same But S1 will change and so will the text inside the brackets. if I place =the962|IQVALUE!'L14O011 in one cell, S1 in another and (V)' in another and use =CONCATENATE(Cell1, Cell2, Cell3) the = in cell 1 makes it return "Cell1" in the CONCATENATE result. Is there a way around this? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONCATENATE problem
Don
Looks like I've hit a dead end, that didn't work either, I'm going to try a VB solution. Thanks for your help Mark "Don Guillett" wrote: Didn't know about that, so try =right(B3,len(b3)-1)&B4&"("&B5&")" -- Don Guillett SalesAid Software "Mark Dullingham" wrote in message ... Don Thanks for your reply, unfortunately it didn't solve the promblem. The '=' at the begining of the DDE link is still causing the value to be returned as #NAME? Mark "Don Guillett" wrote: try =B3&B4&B5 or without typing the ( ) =B3&B4&"("&B5&")" -- Don Guillett SalesAid Software "Mark Dullingham" wrote in message ... I am trying to constuct a table of DDE links to be referenceby offset amd match funtions on another sheet. There are hundreds of these links, so tomake entering them a bit easier I'm trying to use CONCATENATE to help reduce the time. The liks look like this =the962|IQVALUE!'L14O011S1(V)' =the962|IQVALUE!'L14O011 this bit stays the same But S1 will change and so will the text inside the brackets. if I place =the962|IQVALUE!'L14O011 in one cell, S1 in another and (V)' in another and use =CONCATENATE(Cell1, Cell2, Cell3) the = in cell 1 makes it return "Cell1" in the CONCATENATE result. Is there a way around this? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONCATENATE problem
Mark Dullingham wrote...
.... The liks look like this =the962|IQVALUE!'L14O011S1(V)' =the962|IQVALUE!'L14O011 this bit stays the same But S1 will change and so will the text inside the brackets. if I place =the962|IQVALUE!'L14O011 in one cell, S1 in another and (V)' in another and use =CONCATENATE(Cell1, Cell2, Cell3) the = in cell 1 makes it return "Cell1" in the CONCATENATE result. Is there a way around this? DDE links can't be dynamic. If they wouldn't change (at least not frequently) once created, an easy way to generate them as a batch involves using formulas to create text strings that look like the final DDE links, converting those formulas to values (as text strings), then converting those text strings to DDE link formulas. If you put either ="=the962|IQVALUE!'L14O011" or '=the962|IQVALUE!'L14O011 in cell A1 (note that the 1st ' in the second possibility is a label prefix character that will force Excel to treat the rest of the cell contents as a text string), S1 in cell A2 and (V)' in cell B1, enter the formula =$A$1&$A2&B$1 in cell B2. This should result in the text string =the962|IQVALUE!'L14O011S1(V)' in B2. Run the menu commands Edit Copy followed by Edit Paste Special, as value. That should convert the formula to a text constant. Then run the menu command Edit Replace, finding = and replacing it with = (yes, replace the equal sign with itself). That'll effectively enter the text constant as a formula, so the cell should then return the DDE link's value. If you had several other values like S1, enter them in column A below cell A2, and if you had several other values like (V)', enter them in row 1 to the right of cell B1. Fill the cell B2 formula down and right to match up with the column A and row 1 entries. Then run the Edit Copy, Edit Paste Special and Edit Replace menu commands on the entire range of such formulas. Finally, don't use CONCATENATE. Use the & operator. There may be no difference in results, but there's a lot less typing using &, it's not subject to any limitations on the number of arguments, and it doesn't incur a nested function call level. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONCATENATE problem
Harlan,
Thanks for you reply, I've tried you suggestion, in a slightly different format, and it works a treat. I've used '=the962|IQVALUE!'L14O011 in B1 S in B2 (Letters change down this column) 1 in C1 (numbers increase along this row upto 240) (V)' in A2 (Change down this colomn to) Then using =$B$1&$B$2&C$1&$A$2 I cna just drag across and the jobs done. A smal alteration for the next letter down Col B and I'm off again. You've unwittingly solved another problem I knew was coming, but hadn't solved yet which was having a very large 2D array with 'Live' DDE links as the 3rd party sofware struggles with more than about 200 active links and by the time I've assembled all the possiblities, it will be over a 1000! so by stopping before the paste special part of you suggestion, problem solved. I'm planning on a VB solution to reference this table from another sheet using the 'S1' value, split into 2 cells, then using OFFSET and 2 MATCH functions find the correct string and copy and paste special (as your suggestion) into the active sheet and finally run a macro for replacing the = with = to make them live. So thanks once again youv'e helped more than you realised, Ive just got to figure out the VB bit now *!? "Harlan Grove" wrote: Mark Dullingham wrote... .... The liks look like this =the962|IQVALUE!'L14O011S1(V)' =the962|IQVALUE!'L14O011 this bit stays the same But S1 will change and so will the text inside the brackets. if I place =the962|IQVALUE!'L14O011 in one cell, S1 in another and (V)' in another and use =CONCATENATE(Cell1, Cell2, Cell3) the = in cell 1 makes it return "Cell1" in the CONCATENATE result. Is there a way around this? DDE links can't be dynamic. If they wouldn't change (at least not frequently) once created, an easy way to generate them as a batch involves using formulas to create text strings that look like the final DDE links, converting those formulas to values (as text strings), then converting those text strings to DDE link formulas. If you put either ="=the962|IQVALUE!'L14O011" or '=the962|IQVALUE!'L14O011 in cell A1 (note that the 1st ' in the second possibility is a label prefix character that will force Excel to treat the rest of the cell contents as a text string), S1 in cell A2 and (V)' in cell B1, enter the formula =$A$1&$A2&B$1 in cell B2. This should result in the text string =the962|IQVALUE!'L14O011S1(V)' in B2. Run the menu commands Edit Copy followed by Edit Paste Special, as value. That should convert the formula to a text constant. Then run the menu command Edit Replace, finding = and replacing it with = (yes, replace the equal sign with itself). That'll effectively enter the text constant as a formula, so the cell should then return the DDE link's value. If you had several other values like S1, enter them in column A below cell A2, and if you had several other values like (V)', enter them in row 1 to the right of cell B1. Fill the cell B2 formula down and right to match up with the column A and row 1 entries. Then run the Edit Copy, Edit Paste Special and Edit Replace menu commands on the entire range of such formulas. Finally, don't use CONCATENATE. Use the & operator. There may be no difference in results, but there's a lot less typing using &, it's not subject to any limitations on the number of arguments, and it doesn't incur a nested function call level. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate Problem | Excel Discussion (Misc queries) | |||
Concatenate Problem | New Users to Excel | |||
Concatenate Problem | Excel Worksheet Functions | |||
Tough Concatenate Problem | Excel Worksheet Functions | |||
concatenate problem | Excel Worksheet Functions |