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

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

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

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

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