Calling fellow Excel power users

R

remybfg10k

Expert Member
Joined
May 24, 2007
Messages
3,798
Reaction score
0
Location
Centurion
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?
 
Look at using INDIRECT to create the cell reference formula.

Edit: Might need to use ADDRESS also.
 
Last edited:
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.
 
Boomstomp said:
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.
Click to expand...

Exactly the problem i have.
 
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
 
It has to do with copy & pasting and the format of your spreadsheet. Did you try to do paste special?
 
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:
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 "/".
 
Thanks for the feedback,

Rkootknir, yours looked promising but didnt pan out

Where can i post the file for everyone to look at?
 
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.
 
remybfg10k said:
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?
Click to expand...

Any excel problem there is a lady who i call ,SHe is very good.pm for here number:)
 
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:
Borrels said:
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.
Click to expand...
Fully agree, just trying to keep it as "dumb" as possible for the lady who is going to use it
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X