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

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.

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

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

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

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

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

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

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