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.

" wrote:

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

On Nov 20, 10:59 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
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.



" wrote:
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 -