software

Hi folks,

{Using Excel 2000}

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

For example, I have three worksheets named:

quot;CostPricequot; - the wholesale price
quot;Markupquot; - the profit markup in %
quot;RetailPricequot; - 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 quot;RetailPrice) cell.

Any guidance much appreciated.

Cheers -- NeilHi

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?

quot;neilrquot; gt; wrote in message
...
gt; Hi folks,
gt;
gt; {Using Excel 2000}
gt;
gt; I'm trying to calculate a value across three worksheets.
gt;
gt; For example, I have three worksheets named:
gt;
gt; quot;CostPricequot; - the wholesale price
gt; quot;Markupquot; - the profit markup in %
gt; quot;RetailPricequot; - the calculated result of (CostPrice Markup)
gt;
gt; It's been along time since I've used Excel (V4?) in this way, but I'm sure
gt; I used to be able to point and click my way through the various worksheets
gt; and Excel would build the correct formula for me. When I try this now I
gt; receive a #VALUE error in the quot;RetailPrice) cell.
gt;
gt; Any guidance much appreciated.
gt;
gt; Cheers -- Neil
gt;
gt;
gt;
gt;
gt;
gt;
gt;
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 quot;0.25quot; 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,

Neillt;Andygt; wrote in message ...
gt; Hi
gt;
gt; Excel shouldn't have a problem with what you are trying. There is
gt; obviously a problem somewhere, though.
gt; Once you have completer the formula, select areas of it in the formula bar
gt; and hit F9 - to show what that part of the formula equates to. This should
gt; help spot what's going on.
gt;
gt; Andy.
gt; PS How can you add a cost price and a percentage markup together?
gt;
gt; quot;neilrquot; gt; wrote in message
gt; ...
gt;gt; Hi folks,
gt;gt;
gt;gt; {Using Excel 2000}
gt;gt;
gt;gt; I'm trying to calculate a value across three worksheets.
gt;gt;
gt;gt; For example, I have three worksheets named:
gt;gt;
gt;gt; quot;CostPricequot; - the wholesale price
gt;gt; quot;Markupquot; - the profit markup in %
gt;gt; quot;RetailPricequot; - the calculated result of (CostPrice Markup)
gt;gt;
gt;gt; It's been along time since I've used Excel (V4?) in this way, but I'm
gt;gt; sure I used to be able to point and click my way through the various
gt;gt; worksheets and Excel would build the correct formula for me. When I try
gt;gt; this now I receive a #VALUE error in the quot;RetailPrice) cell.
gt;gt;
gt;gt; Any guidance much appreciated.
gt;gt;
gt;gt; Cheers -- Neil
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;
gt;
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.

quot;neilrquot; gt; wrote in message
...
gt; Andy,
gt;
gt; You're quite correct - the problem lies elsewhere altogether. Since
gt; posting this I've found the underlying reason is that the data in the
gt; source cells (CostPrice) contain two leading and two trailing spaces. The
gt; reason why is that we have the pricing tables already made up in web
gt; pages. I've gone and copied the data from the webs page and pasted it
gt; into excel. I really don't know why the values contain the spaces at all,
gt; however removing them form one cell value enables the formula to work
gt; properly.
gt;
gt; My problem now is how to strip the spaces from hundreds of cells (?) apart
gt; from going through them manually - anybody got any advice?
gt;
gt; As to your PS - the formula I am using to calculate the RetailPrice is
gt; this: CostPrice*Markup CostPrice (the Markup value is entered as quot;0.25quot; in
gt; a cell formatted as %).
gt;
gt; It works for me but quite possibly not the proper way to go about it. I'd
gt; welcome any advice as I'm only feeling my way around things.
gt;
gt; Many thanks,
gt;
gt; Neil
gt;
gt;
gt;
gt;
gt;
gt; lt;Andygt; wrote in message ...
gt;gt; Hi
gt;gt;
gt;gt; Excel shouldn't have a problem with what you are trying. There is
gt;gt; obviously a problem somewhere, though.
gt;gt; Once you have completer the formula, select areas of it in the formula
gt;gt; bar and hit F9 - to show what that part of the formula equates to. This
gt;gt; should help spot what's going on.
gt;gt;
gt;gt; Andy.
gt;gt; PS How can you add a cost price and a percentage markup together?
gt;gt;
gt;gt; quot;neilrquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; Hi folks,
gt;gt;gt;
gt;gt;gt; {Using Excel 2000}
gt;gt;gt;
gt;gt;gt; I'm trying to calculate a value across three worksheets.
gt;gt;gt;
gt;gt;gt; For example, I have three worksheets named:
gt;gt;gt;
gt;gt;gt; quot;CostPricequot; - the wholesale price
gt;gt;gt; quot;Markupquot; - the profit markup in %
gt;gt;gt; quot;RetailPricequot; - the calculated result of (CostPrice Markup)
gt;gt;gt;
gt;gt;gt; It's been along time since I've used Excel (V4?) in this way, but I'm
gt;gt;gt; sure I used to be able to point and click my way through the various
gt;gt;gt; worksheets and Excel would build the correct formula for me. When I try
gt;gt;gt; this now I receive a #VALUE error in the quot;RetailPrice) cell.
gt;gt;gt;
gt;gt;gt; Any guidance much appreciated.
gt;gt;gt;
gt;gt;gt; Cheers -- Neil
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;
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 SubGord Dibben MS Excel MVP

On Fri, 20 Jan 2006 14:20:45 -0000, quot;neilrquot; gt; wrote:

gt;Andy,
gt;
gt;You're quite correct - the problem lies elsewhere altogether. Since posting
gt;this I've found the underlying reason is that the data in the source cells
gt;(CostPrice) contain two leading and two trailing spaces. The reason why is
gt;that we have the pricing tables already made up in web pages. I've gone and
gt;copied the data from the webs page and pasted it into excel. I really don't
gt;know why the values contain the spaces at all, however removing them form
gt;one cell value enables the formula to work properly.
gt;
gt;My problem now is how to strip the spaces from hundreds of cells (?) apart
gt;from going through them manually - anybody got any advice?
gt;
gt;As to your PS - the formula I am using to calculate the RetailPrice is this:
gt;CostPrice*Markup CostPrice (the Markup value is entered as quot;0.25quot; in a cell
gt;formatted as %).
gt;
gt;It works for me but quite possibly not the proper way to go about it. I'd
gt;welcome any advice as I'm only feeling my way around things.
gt;
gt;Many thanks,
gt;
gt;Neil
gt;
gt;
gt;
gt;
gt;
gt;lt;Andygt; wrote in message ...
gt;gt; Hi
gt;gt;
gt;gt; Excel shouldn't have a problem with what you are trying. There is
gt;gt; obviously a problem somewhere, though.
gt;gt; Once you have completer the formula, select areas of it in the formula bar
gt;gt; and hit F9 - to show what that part of the formula equates to. This should
gt;gt; help spot what's going on.
gt;gt;
gt;gt; Andy.
gt;gt; PS How can you add a cost price and a percentage markup together?
gt;gt;
gt;gt; quot;neilrquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; Hi folks,
gt;gt;gt;
gt;gt;gt; {Using Excel 2000}
gt;gt;gt;
gt;gt;gt; I'm trying to calculate a value across three worksheets.
gt;gt;gt;
gt;gt;gt; For example, I have three worksheets named:
gt;gt;gt;
gt;gt;gt; quot;CostPricequot; - the wholesale price
gt;gt;gt; quot;Markupquot; - the profit markup in %
gt;gt;gt; quot;RetailPricequot; - the calculated result of (CostPrice Markup)
gt;gt;gt;
gt;gt;gt; It's been along time since I've used Excel (V4?) in this way, but I'm
gt;gt;gt; sure I used to be able to point and click my way through the various
gt;gt;gt; worksheets and Excel would build the correct formula for me. When I try
gt;gt;gt; this now I receive a #VALUE error in the quot;RetailPrice) cell.
gt;gt;gt;
gt;gt;gt; Any guidance much appreciated.
gt;gt;gt;
gt;gt;gt; Cheers -- Neil
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
gt;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
www.mvps.org/dmcritchie/excel/join.htm#trimall
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm

quot;Gord Dibbenquot; lt;gorddibbATshawDOTcagt; wrote in message ...
gt; Neil
gt;
gt; With the data coming from the web you could have non-breaking spaces(char 160).
gt;
gt; Try David McRitchie's TRIMALL macro.
gt;
gt; Sub TrimALL()
gt; 'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
gt; Application.ScreenUpdating = False
gt; Application.Calculation = xlCalculationManual
gt; Dim cell As Range
gt; 'Also Treat CHR 0160, as a space (CHR 032)
gt; Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
gt; LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
gt; 'Trim in Excel removes extra internal spaces, VBA does not
gt; On Error Resume Next 'in case no text cells in selection
gt; For Each cell In Intersect(Selection, _
gt; Selection.SpecialCells(xlConstants, xlTextValues))
gt; cell.Value = Application.Trim(cell.Value)
gt; Next cell
gt; On Error GoTo 0
gt; Application.Calculation = xlCalculationAutomatic
gt; Application.ScreenUpdating = True
gt; End Sub
gt;
gt;
gt; Gord Dibben MS Excel MVP
gt;
gt; On Fri, 20 Jan 2006 14:20:45 -0000, quot;neilrquot; gt; wrote:
gt;
gt; gt;Andy,
gt; gt;
gt; gt;You're quite correct - the problem lies elsewhere altogether. Since posting
gt; gt;this I've found the underlying reason is that the data in the source cells
gt; gt;(CostPrice) contain two leading and two trailing spaces. The reason why is
gt; gt;that we have the pricing tables already made up in web pages. I've gone and
gt; gt;copied the data from the webs page and pasted it into excel. I really don't
gt; gt;know why the values contain the spaces at all, however removing them form
gt; gt;one cell value enables the formula to work properly.
gt; gt;
gt; gt;My problem now is how to strip the spaces from hundreds of cells (?) apart
gt; gt;from going through them manually - anybody got any advice?
gt; gt;
gt; gt;As to your PS - the formula I am using to calculate the RetailPrice is this:
gt; gt;CostPrice*Markup CostPrice (the Markup value is entered as quot;0.25quot; in a cell
gt; gt;formatted as %).
gt; gt;
gt; gt;It works for me but quite possibly not the proper way to go about it. I'd
gt; gt;welcome any advice as I'm only feeling my way around things.
gt; gt;
gt; gt;Many thanks,
gt; gt;
gt; gt;Neil
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;lt;Andygt; wrote in message ...
gt; gt;gt; Hi
gt; gt;gt;
gt; gt;gt; Excel shouldn't have a problem with what you are trying. There is
gt; gt;gt; obviously a problem somewhere, though.
gt; gt;gt; Once you have completer the formula, select areas of it in the formula bar
gt; gt;gt; and hit F9 - to show what that part of the formula equates to. This should
gt; gt;gt; help spot what's going on.
gt; gt;gt;
gt; gt;gt; Andy.
gt; gt;gt; PS How can you add a cost price and a percentage markup together?
gt; gt;gt;
gt; gt;gt; quot;neilrquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt;gt; Hi folks,
gt; gt;gt;gt;
gt; gt;gt;gt; {Using Excel 2000}
gt; gt;gt;gt;
gt; gt;gt;gt; I'm trying to calculate a value across three worksheets.
gt; gt;gt;gt;
gt; gt;gt;gt; For example, I have three worksheets named:
gt; gt;gt;gt;
gt; gt;gt;gt; quot;CostPricequot; - the wholesale price
gt; gt;gt;gt; quot;Markupquot; - the profit markup in %
gt; gt;gt;gt; quot;RetailPricequot; - the calculated result of (CostPrice Markup)
gt; gt;gt;gt;
gt; gt;gt;gt; It's been along time since I've used Excel (V4?) in this way, but I'm
gt; gt;gt;gt; sure I used to be able to point and click my way through the various
gt; gt;gt;gt; worksheets and Excel would build the correct formula for me. When I try
gt; gt;gt;gt; this now I receive a #VALUE error in the quot;RetailPrice) cell.
gt; gt;gt;gt;
gt; gt;gt;gt; Any guidance much appreciated.
gt; gt;gt;gt;
gt; gt;gt;gt; Cheers -- Neil
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt;