Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate Strings And Values To Create Formula
I am using Excel 2013.
Cell AL7 has the value 7 in it. I have created the following formula in P7: =concatenate("=","A",AL7) The hope was that this would produce in P7 a formula (i.e. =A7). Instead it creates a string, i.e. "=A7". Is it possible to create an Excel formula by concatenating strings and values? Thanks. -- tb |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate Strings And Values To Create Formula
tb wrote:
I am using Excel 2013. Cell AL7 has the value 7 in it. I have created the following formula in P7: =concatenate("=","A",AL7) The hope was that this would produce in P7 a formula (i.e. =A7). Instead it creates a string, i.e. "=A7". Is it possible to create an Excel formula by concatenating strings and values? What are you trying to do, exactly? -- At least she didn't swing a torch at me while screaming, "Back, monster, back!" I've gotten pretty fed up with that. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate Strings And Values To Create Formula
I am using Excel 2013.
Cell AL7 has the value 7 in it. I have created the following formula in P7: =concatenate("=","A",AL7) The hope was that this would produce in P7 a formula (i.e. =A7). Instead it creates a string, i.e. "=A7". Is it possible to create an Excel formula by concatenating strings and values? Thanks. Concatenate is a string function and so you can't do it this way. You can, however, use a macro to read values from cells and assign the result to be a formula in a target cell because VBA passes the formula as a string that gets converted to a formula... Example: Range("B1").Formula = "=A" & Range("AL7").Value Result in B1: =A7 -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate Strings And Values To Create Formula
hi,
Am Wed, 6 Jan 2016 00:27:42 +0000 (UTC) schrieb tb: The hope was that this would produce in P7 a formula (i.e. =A7). Instead it creates a string, i.e. "=A7". you could do it with: =INDIRECT("A"&AL7) But I would prefer Garry's VBA solution. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate Strings And Values To Create Formula
On 1/5/2016 at 9:40:41 PM Auric__ wrote:
tb wrote: I am using Excel 2013. Cell AL7 has the value 7 in it. I have created the following formula in P7: =concatenate("=","A",AL7) The hope was that this would produce in P7 a formula (i.e. =A7). Instead it creates a string, i.e. "=A7". Is it possible to create an Excel formula by concatenating strings and values? What are you trying to do, exactly? I have two tables in the same worksheet. One of the tables is single rows; the other one is double rows. For instance, in my single-row table I have records in rows 1, 2, 3, 4, etc. and I want to copy them in rows 1, 3, 5, 7 of the second table. I can't just do a copy/paste from the single-row table into the double-row one, so I was trying to come up with a formula to do the job. Hope this makes sense... -- tb |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate Strings And Values To Create Formula
On 1/6/2016 at 12:44:46 AM Claus Busch wrote:
hi, Am Wed, 6 Jan 2016 00:27:42 +0000 (UTC) schrieb tb: The hope was that this would produce in P7 a formula (i.e. =A7). Instead it creates a string, i.e. "=A7". you could do it with: =INDIRECT("A"&AL7) But I would prefer Garry's VBA solution. Regards Claus B. This works, thanks! -- tb |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate Strings And Values To Create Formula
you could do it with:
=INDIRECT("A"&AL7) But of course! I've been using that function this past week to populate a report sheet with data stored in named ranges on another sheet, based on user criteria in a helper column with a row-relative name... =INDIRECT(Data!Key,ThisNdx) 'for single column data =INDIRECT(Data!Key,ThisNdx,ColNdx) 'for multi column data ...where the target cells are in one column on the report sheet, and the data is in single rows of named ranges that may be multi column depending on how many 'sections' there are for reporting a specific category of data. This, IMO, is the simpler solution that you suggest which works better where VBA isn't desired! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate Strings And Values To Create Formula
Hi Garry,
Am Wed, 06 Jan 2016 09:33:13 -0500 schrieb GS: =INDIRECT(Data!Key,ThisNdx) 'for single column data =INDIRECT(Data!Key,ThisNdx,ColNdx) 'for multi column data ..where the target cells are in one column on the report sheet, and the data is in single rows of named ranges that may be multi column depending on how many 'sections' there are for reporting a specific category of data. This, IMO, is the simpler solution that you suggest which works better where VBA isn't desired! yes, it works. But I don't like INDIRECT because it does not work with closed workbooks and it is a volatile function. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate Strings And Values To Create Formula
Hi Garry,
Am Wed, 06 Jan 2016 09:33:13 -0500 schrieb GS: =INDIRECT(Data!Key,ThisNdx) 'for single column data =INDIRECT(Data!Key,ThisNdx,ColNdx) 'for multi column data ..where the target cells are in one column on the report sheet, and the data is in single rows of named ranges that may be multi column depending on how many 'sections' there are for reporting a specific category of data. This, IMO, is the simpler solution that you suggest which works better where VBA isn't desired! yes, it works. But I don't like INDIRECT because it does not work with closed workbooks and it is a volatile function. Regards Claus B. This is why I went with the VBA suggestion! I was thinking to store my data in an external file (or files) as opposed to a sheet in the project file. VBA works for both! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate strings? | Excel Discussion (Misc queries) | |||
CONCATENATE text to create a formula to be evaluated | Excel Worksheet Functions | |||
how can i use concatenate to create a linking formula? | Excel Worksheet Functions | |||
concatenate strings | Excel Worksheet Functions | |||
Concatenate Text to create Formula | Excel Programming |