Calculating value across multiple worksheets
 

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

Calculating value across multiple worksheets
 

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" wrote in message
...
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

Calculating value across multiple worksheets
 

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





<Andy wrote in message ...
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" wrote in message
...
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

Calculating value across multiple worksheets
 

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.

"neilr" wrote in message
...
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





<Andy wrote in message ...
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" wrote in message
...
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

Calculating value across multiple worksheets
 

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

On Fri, 20 Jan 2006 14:20:45 -0000, "neilr" wrote:

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





<Andy wrote in message ...
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" wrote in message
...
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

Calculating value across multiple worksheets
 

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

"Gord Dibben" <gorddibbATshawDOTca wrote in message ...
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

On Fri, 20 Jan 2006 14:20:45 -0000, "neilr" wrote:

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





<Andy wrote in message ...
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" wrote in message
...
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