Can Excel tell the difference between a formula and a value

[singlefin]

I have kind of a problem, I think...

I have distributed a large number of sales forecast spreadsheets to our sales branches. The sheets overall structure are the same.

My problem is that I have discovered some formula errors that I need to correct. But most if not all of our sales guys have already come back with their forecasts. Some have keyed in their sales values in manually, while others in some cases, decided to leave it alone and let my original (and wrong) formula generate a value for them.

Is there a way to merge their spreadsheets, which contains the manually entered and formula driven numbers, and a re-worked spreadsheet with the correct formulas while retaining their manually entered numbers?

Is there a way in Excel to only lookup and replace formulas while leaving non-formula cells alone?

Thanks in advance!!

 

[agihcam]

review help for SpecialCells(xlCellTypeFormulas)

 

[Damon Ostrander]

Hi singlefin,

I don't know of a built-in feature that can be used to do this, but it certainly would not be difficult to write a macro that would do it. How to write that macro depends on whether you want it to process all the files at one time, or to run it manually one at a time for each of the other workbooks. In addition, do the other files have exactly the same sheet names, and do you want to copy formulas from all sheets in your template to the corresponding sheets in the other sales guys' workbooks?

If you want to process all files and all sheets within each file with one click, the easiest way I can think of would be to put all the sales guys' files in a particular folder, and then make this the working folder. The macro could then be placed in your "template" workbook and when it is run it would simply loop through all the other files, and all sheets within each file, replacing the formulas with the formulas from the template.

Damon

 

[singlefin]

The worksheets have different names, named after the salesman. So, for example, I have a workbook called W03.xls, which contains 3 sheets, Eric, John and W03 (which is a summary of Eric and John's numbers) for warehouse #3.

The automatically generated formulas are all the same and in the same cell location across the board for everyone. Only the sheet names are different and workbooks named different for every warehouse that I have.

So I am assuming that I have to do a macro and run it for every sheet under every workbook.

 

[singlefin]

review help for SpecialCells(xlCellTypeFormulas)

I just looked this up, and tried to apply a few examples I found on another site.
This is what I have modified:

Sub AllNummericCells()
Dim rCcells As Range, rFcells As Range
Dim rAcells As Range

'Set variable to all used cells
Set rAcells = ActiveSheet.Range("AA3:AZ321")

On Error Resume Next 'In case of no numeric formula or constants.

'Set variable to all numeric constants
Set rCcells = rAcells.SpecialCells(xlCellTypeConstants, xlNumbers)
'Set variable to all numeric formulas
'Set rFcells = rAcells.SpecialCells(xlCellTypeFormulas, xlNumbers)

'Determine which type of numeric data (formulas, constants or none)
If rCcells Is Nothing And rFcells Is Nothing Then
MsgBox "You Worksheet contains no numbers"
End
ElseIf rCcells Is Nothing Then
Set rAcells = rFcells 'formulas
ElseIf rFcells Is Nothing Then
Set rAcells = rCcells 'constants
Else
Set rAcells = Application.Union(rFcells, rCcells) 'Both
End If
On Error GoTo 0

rAcells.Select
End Sub

----------------

I am no macro-expert, I can kinda guess enough to comment out the part where the macro looks for formulas.

With that, I am able to get the macro to high-light the cells the sales guys had manually entered numbers.

But how do I write the macro to have it copy these highlighted numbers from the workbooks the sales guys sent back to the workbooks that have the corrected formulas?

I need to do this over 2 ranges aa3:az321 and bz3:cv321

Thanks!

 

[singlefin]

Anyone?

Burning the midnight oil, gotta get this done by tommorrow and I would really appreciate any help to automate this whole process.

Thanks

 

[Damon Ostrander]

Hi again singlefin,

A few more quick questions:

1. Do all the workbooks have the same number of sheets? Or do they have a variable number of "person" sheets followed by one summary sheet whose tab name starts with "W"?

2. In the example you just gave, do you want the formulas from the Eric sheet copied to all the "person" sheets in all the other workbooks, plus the formulas from the W03 sheet transferred to the "W" (summary) sheet in each of the other workbooks?

Damon

PS. Because of your time crunch, I'm going to PM you with my phone number.