• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 23
  • Last Modified:

Clarifications on Excel

Hello Gurus ,
I have a scenario in excel , where I have three cells
Cell 1 is the Qty
Cell 2 is the unit price
Cell 3 is the total price

there is formula added Cell 3 = Cell 2 * Cell 1
Now I don't want to show the value of Cell 1 and needs to mention as " LS" .

Question is : how can I change the particar cell ( in this case , Cell 1) to "LS" , without affecting the formulae of calculation

Regards,

Sid
0
infiniti7181
Asked:
infiniti7181
  • 5
  • 2
  • 2
  • +1
1 Solution
 
Roy CoxGroup Finance ManagerCommented:
What is LS?
0
 
infiniti7181Author Commented:
Sorry I meant lumpsum as "LS" , so that I don't want to reveal the quantities
0
 
Roy CoxGroup Finance ManagerCommented:
So is LS a always the same value?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

can you step back and explain the bigger picture? As in "why" do you need to hide the quantity? If the total is the product of quantity and unit price, it can be calculated from the total price.  

What do you want to achieve? Maybe there are different approaches.

cheers, teylyn
0
 
infiniti7181Author Commented:
hi , all
just to rephrase my requirement

the sheet is a Bill of Material , so when I prepare a particular cell lets say example shown below.

Description           Qty      Unit Price    Total Price
Services Prices        1            20                    20

consider the example , total price is formulated to be Unit times Qty

Now , I want to change the Qty 1 to be renamed as LS' Lumpsum' . inorder not to affect the formula . So that whoever reads the excel will not see the quantities mentioned .
What I know , if I format that particular cell , it can be renamed to whatever words you need to type on it .

BR
0
 
Roy CoxGroup Finance ManagerCommented:
It's still not clear. Will only one cell be used for QTY. Will quantities vary?

 If you want to hide the contents of the formula then use Sheet protection
1. First select all the cells on the sheet by pressing the icon next to A and above 1.
2.Right click on the sheet and choose Format cells
3.In the Protection Tab clear the selections
4.Next select the cell(s) with Formulas
5.Format cells -> Protection and tick both boxes.

Protect the sheet and the formulas will not be visible.
0
 
Saqib Husain, SyedEngineerCommented:
You can try a formula like

=IF(B5="LS",C5,C5*B5)

as shown in the attached file.
LSformula.xlsx
0
 
Roy CoxGroup Finance ManagerCommented:
Possibly you can create a Named Range on a hidden sheet called LS then enter quantirties in that cell. The formula in the Bill would be

=LS*G2
LS.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
Some of the accepted comments are not solutions. The two solutions are miles apart. They both cannot be correct. Please explain the distribution of points. Also please tell us what was deficient in the solutions which deserved a B grade.
0
 
Roy CoxGroup Finance ManagerCommented:
We now have a request for clarification marked as accepted solution!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now