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

# 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
• 5
• 2
• 2
• +1
1 Solution

Group Finance ManagerCommented:
What is LS?
0

Author Commented:
Sorry I meant lumpsum as "LS" , so that I don't want to reveal the quantities
0

Group Finance ManagerCommented:
So is LS a always the same value?
0

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

Author 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

Group 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

EngineerCommented:
You can try a formula like

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

as shown in the attached file.
LSformula.xlsx
0

Group 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

EngineerCommented:
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

Group 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.