Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
Posts: 84
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 538
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
Posts: 84
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
Posts: 84
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate strings? Alain Dekker Excel Discussion (Misc queries) 4 February 21st 10 09:51 PM
CONCATENATE text to create a formula to be evaluated Brian Excel Worksheet Functions 2 January 2nd 10 03:25 AM
how can i use concatenate to create a linking formula? DRandolph Excel Worksheet Functions 10 January 25th 06 08:27 PM
concatenate strings Gary''s Student Excel Worksheet Functions 3 September 15th 05 05:25 PM
Concatenate Text to create Formula Brendon[_2_] Excel Programming 1 July 9th 03 01:15 PM


All times are GMT +1. The time now is 07:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"