Calculating value across multiple worksheets

[neilr]

Hi folks,

{Using Excel 2000}

I'm trying to calculate a value across three worksheets.

For example, I have three worksheets named:

"CostPrice" - the wholesale price
"Markup" - the profit markup in %
"RetailPrice" - the calculated result of (CostPrice + Markup)

It's been along time since I've used Excel (V4?) in this way, but I'm sure I
used to be able to point and click my way through the various worksheets and
Excel would build the correct formula for me. When I try this now I receive
a #VALUE error in the "RetailPrice) cell.

Any guidance much appreciated.

Cheers -- Neil

 

[Guest]

Hi

Excel shouldn't have a problem with what you are trying. There is obviously
a problem somewhere, though.
Once you have completer the formula, select areas of it in the formula bar
and hit F9 - to show what that part of the formula equates to. This should
help spot what's going on.

Andy.
PS How can you add a cost price and a percentage markup together?

 

[neilr]

Andy,

You're quite correct - the problem lies elsewhere altogether. Since posting
this I've found the underlying reason is that the data in the source cells
(CostPrice) contain two leading and two trailing spaces. The reason why is
that we have the pricing tables already made up in web pages. I've gone and
copied the data from the webs page and pasted it into excel. I really don't
know why the values contain the spaces at all, however removing them form
one cell value enables the formula to work properly.

My problem now is how to strip the spaces from hundreds of cells (?) apart
from going through them manually - anybody got any advice?

As to your PS - the formula I am using to calculate the RetailPrice is this:
CostPrice*Markup+CostPrice (the Markup value is entered as "0.25" in a cell
formatted as %).

It works for me but quite possibly not the proper way to go about it. I'd
welcome any advice as I'm only feeling my way around things.

Many thanks,

Neil

 

[Guest]

Hi

This may work - but it depends on what sort the trailing/leading spaces are.
Type 1 in an unused cell. Copy it (Ctrl-C). Select your range and then
Edit/Paste Special/Multiply.
Make sure you backup your data before you start.
If this does not do the trick, there are other options available.
Your formula looks OK. In your original post you simply had '(CostPrice +
Markup)'

Andy.

 

[Gord Dibben]

Neil

With the data coming from the web you could have non-breaking spaces(char 160).

Try David McRitchie's TRIMALL macro.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

 

[David McRitchie]

Please refer to my webpage for the macro code itself, the webpage
includes quite a bit of information on how to find out what is
actually causing the problem, the purpose of the macro and
reason for coding. The page for the TrimALL macro is
http://www.mvps.org/dmcritchie/excel/join.htm#trimall