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

Found 11th Mar 2009
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

14 Comments

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?

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

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 )

Original Poster

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

Original Poster

AberBargoed;4611232

Once you have done that can't you select all calculations and do a paste … 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

AberBargoed;4611232

Once you have done that can't you select all calculations and do a paste … 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.

Copy, Paste Special > Values does remove the formula - or am I reading this wrong? :thinking:

Copy the results from column b and right click Paste Special ,Values

gdp-it;4611255

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:

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!)

Original Poster

AberBargoed;4611282

Try it on one cell -Ctrl + CThen go to Edit Paste Special and select … Try it on one cell -Ctrl + CThen 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

Original Poster

thanks guys for all your input

rep left

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:

gdp-it;4611296

Absolutely spot on. Thanks you have saved me hours of manual inputs. 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
Avatar
@
    Text
    Top Discussions
    1. I just found out that I have been paying £65 p/m to sky for tv/broadband/la…89
    2. Google maps on Plusnet46
    3. GoPro Hero 5 Black33
    4. spam regarding paypal33

    See more discussions