Concatinate a function and make it work

[Don]

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

 

[T. Valko]

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)

 

[Don]

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]

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.

 

[Don]

thanks, will look into this

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.

 

[dksaluki]

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