Calling fellow Excel power users

remybfg10k

Expert Member
Joined
May 24, 2007
Messages
3,798
I have a bit of a problem i can't get my head around.

Yet its a very basic problem, i think :)

I have tried google but i cannot think of the right question to ask!

Anyway,

If i do a concatenation between two cells and i arrive with a formula, when i paste that cell into another cell, the formula doesn't "trigger" until i double click the cell and press enter.

Does that make sense?

I.e. if i concatenate the following two cells:

'=EMP001 and !A2

I get the following results in the third cell '=EMP001!A2

I then copy and paste Value over itself

I then do a replace all to remove the ' symbol

I then copy the result to a new cell
it then shows the following:

=EMP008!A2

i.e. it doesn't trigger.

So i need to double click the cell and press enter, which triggers it.

Is there no faster way?
 

Drake2007

Expert Member
Joined
Oct 23, 2008
Messages
4,408
Look at using INDIRECT to create the cell reference formula.

Edit: Might need to use ADDRESS also.
 
Last edited:

Boomstomp

Well-Known Member
Joined
Jun 22, 2009
Messages
428
I think the problem is one of formatting. Had the same problem a while ago.

When the final copying of the formula is done Excel thinks its text, therefor it displays as text and doest trigger a calculation. When you click the cell itsort of resets its thinking and then reads it as a formula.
 

remybfg10k

Expert Member
Joined
May 24, 2007
Messages
3,798
I think the problem is one of formatting. Had the same problem a while ago.

When the final copying of the formula is done Excel thinks its text, therefor it displays as text and doest trigger a calculation. When you click the cell itsort of resets its thinking and then reads it as a formula.

Exactly the problem i have.
 

SirFooK'nG

Executive Member
Joined
Feb 13, 2009
Messages
8,395
what about ="EMP001" & A2

edit.. maybe not! Use VBA...
 
Last edited:

greggpb

Expert Member
Joined
Apr 22, 2005
Messages
1,816
When you do a copy in excel you are taking a cell that is already calculated and putting it in another position. Excel will not recalculate the cell because the cell you copied it from had already worked out what the result should be, unless the references changed.

Post the work book and I can sort it out for you or pm me
 

Bikerza

Well-Known Member
Joined
May 7, 2007
Messages
262
It has to do with copy & pasting and the format of your spreadsheet. Did you try to do paste special?
 

PallBearer

Well-Known Member
Joined
Sep 18, 2009
Messages
260
Use a macro

for example (ignore the {}'s)

C9 contains {'=data}
D9 contains {!A1}
E9 contains {=C$9&D$9}
F9 will be the cell to paste the answer in the macro

here is the macro

Range("E9").Select
Selection.Copy
Range("F9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Formula = (Range("E9"))

with a little manipulation you can get the macro to scan through a range of cells and do the "copy - paste values" and the answer will appear correct

my answers are on a sheet call "Data"
 
Last edited:

Rkootknir

Expert Member
Joined
Dec 8, 2005
Messages
1,151
The data is formatted as text...

Do a search & replace: Search for "=" and replace with "=." This forces Excel to access every cell and has the same effect as double clicking & pressing enter on the cell. Often happens with dates as well, in that case just search & replace "/".
 

remybfg10k

Expert Member
Joined
May 24, 2007
Messages
3,798
Thanks for the feedback,

Rkootknir, yours looked promising but didnt pan out

Where can i post the file for everyone to look at?
 

Borrels

Senior Member
Joined
Jan 9, 2009
Messages
697
using a macro and your existing formulas, you can just calculate the relevant cells e.g. Range("F:F").calculate

but without a macro, the Indirect function is probably the way to go. Problem is, with Indirect you can't specify a range or formula as parameter; only a single cell, so you will have to do something to the like of: =SUM(INDIRECT("EMP002!H18"),INDIRECT("EMP002!H19"),....)

however, looking at your sheet and guessing what you wanna do, you may save yourself some effort by restructuring the employee sheets in a flat, database format (use a form if you want it easier to use) and slapping some pivots on top of it.
 

kronoSX

Honorary Master
Joined
Feb 28, 2005
Messages
14,583
I have a bit of a problem i can't get my head around.

Yet its a very basic problem, i think :)

I have tried google but i cannot think of the right question to ask!

Anyway,

If i do a concatenation between two cells and i arrive with a formula, when i paste that cell into another cell, the formula doesn't "trigger" until i double click the cell and press enter.

Does that make sense?

I.e. if i concatenate the following two cells:

'=EMP001 and !A2

I get the following results in the third cell '=EMP001!A2

I then copy and paste Value over itself

I then do a replace all to remove the ' symbol

I then copy the result to a new cell
it then shows the following:

=EMP008!A2

i.e. it doesn't trigger.

So i need to double click the cell and press enter, which triggers it.

Is there no faster way?

Any excel problem there is a lady who i call ,SHe is very good.pm for here number:)
 

Drake2007

Expert Member
Joined
Oct 23, 2008
Messages
4,408
Check in the code there is a macro defined that runs on Ctrl + D

take that code out, and put this in

Code:
' Keyboard Shortcut: Ctrl+d
'
acf = ActiveCell.Value
Debug.Print acf
ActiveCell.Offset(0, 1) = "=" & acf & "!C25"
ActiveCell.Offset(0, 2) = "=sum(" & acf & "!B16:B414)"

End Sub

I'm sure you can build the rest of it from there, this reads the active cell, so you run it when you have one of the EMP00x cells selected and will insert the formulas across the row that reference that worksheet needed.

You really should think about doing this in a database.

Edit: so for instance the one you're after would be
Code:
ActiveCell.Offset(0, 5) = "=sum(" & acf & "!H16:H414)"

Just noticed you'll need to have the correct codes in each of the EMP00x worksheets, it's missing a 0 in Cell A2.
 
Last edited:

remybfg10k

Expert Member
Joined
May 24, 2007
Messages
3,798
using a macro and your existing formulas, you can just calculate the relevant cells e.g. Range("F:F").calculate

but without a macro, the Indirect function is probably the way to go. Problem is, with Indirect you can't specify a range or formula as parameter; only a single cell, so you will have to do something to the like of: =SUM(INDIRECT("EMP002!H18"),INDIRECT("EMP002!H19"),....)

however, looking at your sheet and guessing what you wanna do, you may save yourself some effort by restructuring the employee sheets in a flat, database format (use a form if you want it easier to use) and slapping some pivots on top of it.
Fully agree, just trying to keep it as "dumb" as possible for the lady who is going to use it
 
Top