Spreadsheet Help - Anyone out there who can help !!! Please ! - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HUKD, you accept our cookie and privacy policy.
Get the HUKD app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit

Spreadsheet Help - Anyone out there who can help !!! Please !

gdp-it Avatar
7y, 10m agoPosted 7 years, 10 months ago
Hi Guys

Any spreadsheet Guru's out there? I need to take a value from a cell add 40% then I need to have the next cell display the result. That's the easy part. The hardest part is I need to delete the original cell so only the final value is shown;

eg. Cell a1 value =10
Cell b2 =SUM(I3*1.4) - ( this displays 14 )

I then need to delete the original cell a1 and replace it with the cell b2 result. This is because I have a price from a shop but want to mark it up by 40% and send out the spreadsheet to potential customers without seeing my buy price in cell a1.

There are approx 600 parts so I do not want to type in the result in each time from b2 into a1 each time and I cannot copy and paste the result from b2 as this copy the actual formula and not the figure. Hope this makes sense !!

Rep left for any help
gdp-it Avatar
7y, 10m agoPosted 7 years, 10 months ago
Options

All Comments

(14) Jump to unreadPost a comment
Comments/page:
#1
why dont you put your prices in an adjacent cell, colour the text white and protect it from editing, then you dont have anything to delete?

Or did i read it wrong?
#2
If it's not vital that you send it out as an xls file then I would just hide the column (highlight column>format>columns>hide) then pdf it. if it is then i have no clue, sorry :?
#3
Once you have done that can't you select all calculations and do a paste special on them - then replace the original values via copy & paste?

Ps - never ever hide things if it's going out to a customer, there are far too many tools around to discover 'hidden' things on spreadsheets and protection removers (I have most of these :) )
#4
not a bad idea however I was hoping there was a simple way to copy a value from a cell and not the formula.

If I dont get any further then I will certainly look at your suggestion as they would work - thanks
#5
AberBargoed
Once you have done that can't you select all calculations and do a paste special on them - then replace the original values via copy & paste?

Ps - never ever hide things if it's going out to a customer, there are far too many tools around to discover 'hidden' things on spreadsheets and protection removers (I have most of these :) )


Thanks - did try that but it just copied the formula's not the cell value
1 Like #6
AberBargoed
Once you have done that can't you select all calculations and do a paste special on them - then replace the original values via copy & paste?

Ps - never ever hide things if it's going out to a customer, there are far too many tools around to discover 'hidden' things on spreadsheets and protection removers (I have most of these :) )


yeah do that - paste special and click on value this will copy the value rather than the formula. I was only suggesting hiding the column if it was going to go to a pdf btw.
1 Like #7
Copy, Paste Special > Values does remove the formula - or am I reading this wrong? :thinking:
1 Like #8
Copy the results from column b and right click Paste Special ,Values
#9
gdp-it
Thanks - did try that but it just copied the formula's not the cell value



Try it on one cell -

Ctrl + C

Then go to Edit > Paste Special and select Values - if you just press OK it will copy all parts of the cell including the formula. If you have no luck you can email me the spreadsheet and I'll do it in a matter of seconds for you (assuming it's not too confidential!) :thumbsup:
1 Like #10
I'll assume you are using MS-Excel...

Values starting in cell A1 for instance & going down column A (A2, A3, A4, etc).
First formula in cell B1 (referencing cell A1), then incrementing down column B.

Once you have the upshifted amounts in column B, select the entire column, copy to your clipboard, then with the column still selected, use the "Edit" / "Paste Special" menu item.

Select the "Paste" / "Values" option & confirm with [OK].

All the formulae in column B will now be values not based on the corresponding cells in column A.

Select column A.
Delete column A entirely.

Job done.

BFN,

fp.
[Experts-Exchange "Expert" in Microsoft Excel :)]


(And someone who typed out all the steps & missed everybody else providing the same answer several comments back!)
#11
AberBargoed
Try it on one cell -

Ctrl + C

Then go to Edit > Paste Special and select Values - if you just press OK it will copy all parts of the cell including the formula. If you have no luck you can email me the spreadsheet and I'll do it in a matter of seconds for you (assuming it's not too confidential!) :thumbsup:


Absolutely spot on. Thanks you have saved me hours of manual inputs. It worked a treat, I use open office so the options were slightly different.

Thanks again rep left
#12
thanks guys for all your input

rep left
#13
No problemo, glad to have helped :) Have yet to use the Excel equivilant on Open Office but am guessing pretty much all the short cuts are identical! If you ever have any other problems just drop me a line :thumbsup:
#14
gdp-it
Absolutely spot on. Thanks you have saved me hours of manual inputs. It worked a treat, I use open office so the options were slightly different.

Thanks again rep left



Always best to mention this in your question! :)

Glad you resolved your issue in any respect.

BFN,

fp.

Post a Comment

You don't need an account to leave a comment. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!