I have concatinated two cells to create a function and I can cut and past
special the values for that function but it is not until I select the cell
contents and press enter that the function works.

example:

A1 has an equal sign
A2 has C1 where C1 has the number 12
on A3 I code =A1&A2 and it shows =C1
I can copy and past special this onto A4 and it shows =C1 but it will not
show the 12 in C1?

thanks

That's because the result of the paste special operation is a TEXT string
that *looks like a formula* but is not a formula.

Try this:

=INDIRECT(A2)

--
Biff
Microsoft Excel MVP


"Don" <(E-Mail Removed)> wrote in message
news:1224C876-7E27-4056-84DE-(E-Mail Removed)...
>I have concatinated two cells to create a function and I can cut and past
> special the values for that function but it is not until I select the cell
> contents and press enter that the function works.
>
> example:
>
> A1 has an equal sign
> A2 has C1 where C1 has the number 12
> on A3 I code =A1&A2 and it shows =C1
> I can copy and past special this onto A4 and it shows =C1 but it will not
> show the 12 in C1?
>
> thanks

this helps, but my end state was to be able to concatinate a cell that would
have the Date so that I could select a different file each day. I think I
looked at the indirect command but could not get it to work for my issue.

Doing a VLookup in another file and have in A1 the MMDDYY that would add
onto the name of the file I was looking for.

Good help from my original unless I am missing something

"T. Valko" wrote:

> That's because the result of the paste special operation is a TEXT string
> that *looks like a formula* but is not a formula.
>
> Try this:
>
> =INDIRECT(A2)
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Don" <(E-Mail Removed)> wrote in message
> news:1224C876-7E27-4056-84DE-(E-Mail Removed)...
> >I have concatinated two cells to create a function and I can cut and past
> > special the values for that function but it is not until I select the cell
> > contents and press enter that the function works.
> >
> > example:
> >
> > A1 has an equal sign
> > A2 has C1 where C1 has the number 12
> > on A3 I code =A1&A2 and it shows =C1
> > I can copy and past special this onto A4 and it shows =C1 but it will not
> > show the 12 in C1?
> >
> > thanks
>
>
>

You could use INDIRECT for this *BUT* the referenced file MUST be open. This
is usually a "deal breaker"!

One alternative is to download the *free* add-in Morefunc.xll from:

http://xcell05.free.fr/english/

It has a function named INDIRECT.EXT that *might* do what you want without
needing the referenced file to be open.

--
Biff
Microsoft Excel MVP


"Don" <(E-Mail Removed)> wrote in message
news:4A366A02-428A-491B-BA4A-(E-Mail Removed)...
> this helps, but my end state was to be able to concatinate a cell that
> would
> have the Date so that I could select a different file each day. I think I
> looked at the indirect command but could not get it to work for my issue.
>
> Doing a VLookup in another file and have in A1 the MMDDYY that would add
> onto the name of the file I was looking for.
>
> Good help from my original unless I am missing something
>
> "T. Valko" wrote:
>
>> That's because the result of the paste special operation is a TEXT string
>> that *looks like a formula* but is not a formula.
>>
>> Try this:
>>
>> =INDIRECT(A2)
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Don" <(E-Mail Removed)> wrote in message
>> news:1224C876-7E27-4056-84DE-(E-Mail Removed)...
>> >I have concatinated two cells to create a function and I can cut and
>> >past
>> > special the values for that function but it is not until I select the
>> > cell
>> > contents and press enter that the function works.
>> >
>> > example:
>> >
>> > A1 has an equal sign
>> > A2 has C1 where C1 has the number 12
>> > on A3 I code =A1&A2 and it shows =C1
>> > I can copy and past special this onto A4 and it shows =C1 but it will
>> > not
>> > show the 12 in C1?
>> >
>> > thanks
>>
>>
>>

thanks, will look into this

"T. Valko" wrote:

> You could use INDIRECT for this *BUT* the referenced file MUST be open. This
> is usually a "deal breaker"!
>
> One alternative is to download the *free* add-in Morefunc.xll from:
>
> http://xcell05.free.fr/english/
>
> It has a function named INDIRECT.EXT that *might* do what you want without
> needing the referenced file to be open.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Don" <(E-Mail Removed)> wrote in message
> news:4A366A02-428A-491B-BA4A-(E-Mail Removed)...
> > this helps, but my end state was to be able to concatinate a cell that
> > would
> > have the Date so that I could select a different file each day. I think I
> > looked at the indirect command but could not get it to work for my issue.
> >
> > Doing a VLookup in another file and have in A1 the MMDDYY that would add
> > onto the name of the file I was looking for.
> >
> > Good help from my original unless I am missing something
> >
> > "T. Valko" wrote:
> >
> >> That's because the result of the paste special operation is a TEXT string
> >> that *looks like a formula* but is not a formula.
> >>
> >> Try this:
> >>
> >> =INDIRECT(A2)
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Don" <(E-Mail Removed)> wrote in message
> >> news:1224C876-7E27-4056-84DE-(E-Mail Removed)...
> >> >I have concatinated two cells to create a function and I can cut and
> >> >past
> >> > special the values for that function but it is not until I select the
> >> > cell
> >> > contents and press enter that the function works.
> >> >
> >> > example:
> >> >
> >> > A1 has an equal sign
> >> > A2 has C1 where C1 has the number 12
> >> > on A3 I code =A1&A2 and it shows =C1
> >> > I can copy and past special this onto A4 and it shows =C1 but it will
> >> > not
> >> > show the 12 in C1?
> >> >
> >> > thanks
> >>
> >>
> >>
>
>
>

On Dec 11, 9:45 pm, Don <D...@discussions.microsoft.com> wrote:
> I have concatinated two cells to create a function and I can cut and past
> special the values for that function but it is not until I select the cell
> contents and press enter that the function works.
>
> example:
>
> A1 has an equal sign
> A2 has C1 where C1 has the number 12
> on A3 I code =A1&A2 and it shows =C1
> I can copy and past special this onto A4 and it shows =C1 but it will not
> show the 12 in C1?
>
> thanks

It doesn't work until you click the cell, then press ENTER? Sounds
like you need to go to TOOLS > OPTIONS > Calculation Tab, and click
AUTOMATIC radio button