how to replace cell references with actual numbers

[famehunter]

i need to find a shortcut, maybe a marco to replace the formula the
refers to cell references on another worksheet to dispay actual
numbers.

For example, I have =sum(Sheet1!B5+Sheet1B4) in sheet2, and i need to
have =sum(14+15), where B5=14 and B4=15 in sheet2.

please help, i have a really, really long list to work with and
wouldn't want to do this by hand....

 

[Guest]

Would it be acceptable to simply have the cell that is doing the referencing
have the equivalent of =29 in it? After all =SUM(14+15) does equal 29.

If so, you can use Edit | Copy combined with Edit | Paste Special and choose
the [Values] option in the Paste Special dialog.

How to use it:
highlight all of the cells with the formulas you want to convert to "hard"
numbers and use Edit | Copy, then without unselecting anything, turn right
around and choose Edit | Past Special and make sure that the [Values] option
is selected and hit OK. Then the cell with =Sum(Sheet1!B5+Sheet1!B4) will
simply have 29 placed into it.

Hope this helps some.

 

[famehunter]

Thank you JLatham,
I wish I could use this, but they want actual numbers in formula
format.

Would it be acceptable to simply have the cell that is doing the referencing
have the equivalent of =29 in it? After all =SUM(14+15) does equal 29.

If so, you can use Edit | Copy combined with Edit | Paste Special and choose
the [Values] option in the Paste Special dialog.

How to use it:
highlight all of the cells with the formulas you want to convert to "hard"
numbers and use Edit | Copy, then without unselecting anything, turn right
around and choose Edit | Past Special and make sure that the [Values] option
is selected and hit OK. Then the cell with =Sum(Sheet1!B5+Sheet1!B4) will
simply have 29 placed into it.

Hope this helps some.

i need to find a shortcut, maybe a marco to replace the formula the
refers to cell references on another worksheet to dispay actual
numbers.

For example, I have =sum(Sheet1!B5+Sheet1B4) in sheet2, and i need to
have =sum(14+15), where B5=14 and B4=15 in sheet2.

please help, i have a really, really long list to work with and
wouldn't want to do this by hand....- Hide quoted text -- Show quoted text -

 

[Guest]

We could do this with VBA in a macro, but to even suggest the code I'd need
to have an idea of:
The sheet names that need the conversions made in
The range of cells on those sheets we need to look at

Or I might be able to do it by just knowing the name of the sheet(s) that
represent what you've shown as Sheet1 in your examples.

In VBA you can examine the .Formula property and you can create new formulas
to replace an existing formula.

The process would generally be to look at a cell and examine it's .Formula
property and if the name of the 'foreign/source' sheet was mentioned in it,
then determine the source value(s) and rewrite the formula to use the actual
source values. In that fashion, with a little effort, you could change
=SUM(Sheet1!B5+Sheet1!B4)
to
=SUM(14+15)
or any other formula that happened to be in there. It's primarily a problem
involving parsing a string (the formula) complicated a little by determining
where the external reference ends at. And if that source sheet is in another
workbook, that would need to be known and factored in also. Best/easiest if
all concerned sheets are in the same workbook, at least during the use of the
code.

Thank you JLatham,
I wish I could use this, but they want actual numbers in formula
format.

Would it be acceptable to simply have the cell that is doing the referencing
have the equivalent of =29 in it? After all =SUM(14+15) does equal 29.

If so, you can use Edit | Copy combined with Edit | Paste Special and choose
the [Values] option in the Paste Special dialog.

How to use it:
highlight all of the cells with the formulas you want to convert to "hard"
numbers and use Edit | Copy, then without unselecting anything, turn right
around and choose Edit | Past Special and make sure that the [Values] option
is selected and hit OK. Then the cell with =Sum(Sheet1!B5+Sheet1!B4) will
simply have 29 placed into it.

Hope this helps some.

i need to find a shortcut, maybe a marco to replace the formula the
refers to cell references on another worksheet to dispay actual
numbers.

For example, I have =sum(Sheet1!B5+Sheet1B4) in sheet2, and i need to
have =sum(14+15), where B5=14 and B4=15 in sheet2.

please help, i have a really, really long list to work with and
wouldn't want to do this by hand....- Hide quoted text -- Show quoted text -

 

[David Biddulph]

You could display what you ask for by the formula
="=sum("&Sheet1!B4&"+"&Sheet1!B5&")"
but of course it won't evaluate it. If you want to evaluate it you can use
the real formula in another cell [or you can use the EVALUATE() function in
a named reference].

Note also that you don't need the word SUM in your formula. The formula
could have been =(Sheet1!B5+Sheet1B4) or =(14+15).

 

[famehunter]

Thanks for replying to my posts. I took all the suggestions and came up

with this scheme:

1. ="type this around the entire formula"
2. "&type this around the specific cell reference&"
3. copy the cells, and paste special as values
4. now the new formula will be displayed, but excel will see it as text
make sure that the cell format is set to general, or number
5. create a macro:
-enter ALT+F11
-go to insert, module
-type this in the module:
Sub Enter_Values()
For Each xCell In Selection
xCell.Value = xCell.Value
Next xCell
End Sub
6. On the spreadsheet, select the cells you're working on
& run the macro Enter_Values (ALT+F8)
7. that did it for me

You could display what you ask for by the formula
="=sum("&Sheet1!B4&"+"&Sheet1!B5&")"
but of course it won't evaluate it. If you want to evaluate it you can use
the real formula in anothercell[or you can use the EVALUATE() function in
a named reference].

Note also that you don't need the word SUM in your formula. The formula
could have been =(Sheet1!B5+Sheet1B4) or =(14+15).
--
David Biddulph

i need to find a shortcut, maybe a marco toreplacethe formula the
refers tocellreferenceson another worksheet to dispay actual
numbers.

For example, I have =sum(Sheet1!B5+Sheet1B4) in sheet2, and i need to
have =sum(14+15), where B5=14 and B4=15 in sheet2.

please help, i have a really, really long list to work with and
wouldn't want to do this by hand....- Hide quoted text -- Show quoted text -