#### keeping values

```I am sure that this question has been asked and answered. I am creating a
worksheet for my class.  I have a value in cell b2( I want use this value
thru out the sheet). Let say   b2 = 10

b     c     d
3    5      6     8
4    3      7     1

I want to subtract each value from b2(10). How can I do this without doing
it manually?
```
 0
Utf
4/1/2010 9:06:04 PM
excel.misc 78881 articles. 0 followers.

2 Replies
277 Views

Similar Articles

[PageSpeed] 37

```On Thu, 1 Apr 2010 14:06:04 -0700, AFL-math
<AFL-math@discussions.microsoft.com> wrote:

>I am sure that this question has been asked and answered. I am creating a
>worksheet for my class.  I have a value in cell b2( I want use this value
>thru out the sheet). Let say   b2 = 10
>
>       b     c     d
>3    5      6     8
>4    3      7     1
>
>I want to subtract each value from b2(10). How can I do this without doing
>it manually?

Does that mean that you want the following end result
(if not, explain what result you do expect).

b     c      d
3    5     4      2
4    7      3     9

This may be accomplished by the following command sequence

Put -1 in any spare cell, say F10
Select cell F10 (that has the value -1)
Copy
Select cells B3:D4
Paste Special -> Operation = Multiply
Select cell B2 (that has the value 10)
Copy
Select cells B3:D4
Paste Special -> Operation = Add
Clear cell F10 (the spare cell)

Hope this helps / Lars-�ke
```
 0
Lars
4/1/2010 9:44:07 PM
```Refer to the cell via its absolute address: \$B\$2.  That way as you
'drag/fill' formulas using it around on the sheet, it won't change.

To subtract a value from it
=\$B\$2 - 4
or even another cell reference
=\$B\$2 - D3

"AFL-math" wrote:

> I am sure that this question has been asked and answered. I am creating a
> worksheet for my class.  I have a value in cell b2( I want use this value
> thru out the sheet). Let say   b2 = 10
>
>        b     c     d
> 3    5      6     8
> 4    3      7     1
>
> I want to subtract each value from b2(10). How can I do this without doing
> it manually?
```
 0
Utf
4/1/2010 9:46:42 PM

Similar Artilces:

How to keep the macro running?
Hi All Is it possible to include a code to let the macro to run non stop? i yes, would excel crash if i activate another macro at the same time? Cheer -- Message posted from http://www.ExcelForum.com Only one macro can run at a time. If a macro is running, you can't do anything else with XL. Take a look at the OnTime function to have a macro repeat at fixed (or variable) time intervals: http://cpearson.com/excel/ontime.htm In article <hce.12pg7a@excelforum-nospam.com>, hce <<hce.12pg7a@excelforum-nospam.com>> wrote: > Hi All > > Is it possible t...

keeping values
I am sure that this question has been asked and answered. I am creating a worksheet for my class. I have a value in cell b2( I want use this value thru out the sheet). Let say b2 = 10 b c d 3 5 6 8 4 3 7 1 I want to subtract each value from b2(10). How can I do this without doing it manually? On Thu, 1 Apr 2010 14:06:04 -0700, AFL-math <AFL-math@discussions.microsoft.com> wrote: >I am sure that this question has been asked and answered. I am creating a >worksheet for my class. I have a value in cell b2( I want use...

Keeping photos in line
Several questions about moving photos in columns: I have a two column page, a manuscript of 150 pages, and want photos t slide with the type as I insert text in previous pages. I use th format, inline, move with copy instruction. Sometimes it moves th photo over to the other column. I want all photos inserted from files to automatically be embedded, o inline. Is there a command for that? Is there a better way to do a caption for the photos, what we printer call a cutline, than use a new text box? I have to in line it too, an it sometimes wants to move one or two lines when I give the order...

How do I keep my own formatting for a pivot-chart on refreshing
I have many pivotcharts which need to be continually refreshed to represent statistics from data within the workbook. I need to display these charts with my own colour scheme - however the charts are refreshed they revert back to the dodgy Excel default colours. To complicate matters further they are part of a shared workbook - so my macro I recorded to re-apply my colour scheme doesn't work. Can anyone help???? ...

Select last value
I am trying to select the last (bottom) value on a one-column list. I am using the COUNT function to designate the bottom value that is not zero, and the CHOOSE function to select the designated value. But, I can't make that work. Help appreciated. try =match(a number larger than possible,your range) -- Don Guillett SalesAid Software donaldb@281.com "Carl" <c@invalid.com> wrote in message news:eciLEKsvFHA.3236@TK2MSFTNGP14.phx.gbl... > I am trying to select the last (bottom) value on a one-column list. I am > using the COUNT function to designate the bottom va...

How do I hide zero values but keep currency format in P.O. form?
I'm creating a purchase order for our company and am having trouble formatting some cells. I need a currency value in the Total section only if there is a value in the Quantity and Unit Price section. I formatted the Total cells to Currency. But if there are no values in Quantity or Unit Price, zero values appear in the Total cells. How do I format the total cells to only show price values when there are values in Quantity and Unit Price? When you format a cell as currency you get a custom format like this (depending on the otpions you chose)... \$#,##0.00;[Red]\$#,##0.00 Choose the ...

Formatting page header/footer using cell values.
Hello experts! Excel has a nice option to format various parts of a chart using cell values. Can the same be done for headers/footers, e.g. that the header displays value of cell A5. Thanks, Marko. Hi Marko, Try this... ActiveSheet.PageSetup.CenterFooter = Range("A5").Value Regards Ankur/Kanchan www.xlmacros.com Pygmalion wrote: > Hello experts! > > Excel has a nice option to format various parts of a chart using cell > values. Can the same be done for headers/footers, e.g. that the header > displays value of cell A5. > > Thanks, Marko. Thanks! ...

Combining Worksheets and keeping them separate
I have a workbook that contains two worksheets. Worksheet one as information such as name, address, etc. Worksheet two has the same fields/columns of information, however the data itself is different. I need to keep these two lists separated, however, I also need to have them combined at times. Is it possible to create "Worksheet 3" that will automatically update when I add/modify records in Worksheet 1 or Worksheet 2? Thanks. Glenn, There is not an easy way to do that. Instead of three worksheets, use one worksheet (a single database). Then you have two choices. 1) Enter...

Contacts 'display as' keeps showing email address even when I dele
I'm using Outlook 2007 recently upgraded from Outlook 2003 contacts. I don't like email addresses showing in emails, so I have always written 'display as' with name only in Contacts. However, Outlook 2007 has added the email address in 'display as' for everyone. When I go in to delete the email address, SAVE and Close - and go back to check, nine times out of ten, the email address is back in there. Very frustrating. Is this a known problem? Is there a fix? I just want 'display as' to simply reflect what I put in there - not what Outlook decides...

Money2004 keep crashing on startup
just install in my WinXP SP1, which has IE6.0 SP1 installed already. Money2004 Std keep crashing on startup after install. with error dialog of "restart Money - Send / don't send report" any idea ? Any further details in the error, such as whether it's an Invalid Page Fault or a module name? -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.microsoft.com/mswish/suggestion.asp or email mnyukwsh@microsoft.com especi...

personal folders keep disappearing
Hi, This is the setup. When we create a personal folder and point the location of the pst file to the users my documents, which is really a redirected network location everything is great until the pc is rebooted. After a reboot their are no personal folders showing and users have to add a new personal folder and browse to their my documents to locate the pst file. The outlook profile was automatically created by running a script that pointed to an edited .prf file. Here is an extract from the prf file that may be the problem: [Service1] OverwriteExistingService=No UniqueService=Yes M...

Forms![yourFormname]![yourComboboxname].Value
Dear all Sorry for the stupid question I got a very good advice, but since I am very noob I can not put it to work. I haev a button in a form that performs a query. Th query uses the syntax in the subject as a variable in the query. However how do I have to write down the the command in SQL in order for SQL not to recognize it as a string. I am using the query below, but I know it is wrong because the "" signs make SQL think it is a string SELECT [HORA1 rauwe data].ID, [HORA1 rauwe data].DTTM, [HORA1 rauwe data].Para, [HORA1 rauwe data].Machine, [HORA1 rauwe data].Value, [HORA1...

Parameter Value in Crosstab Query
I created a Select Query using a Parameter Value defined in my criteria [Enter Group Number]. The records are retrieved when query runs. Now... I take that query and build a Crosstab query using my select query as the criteria. When it runs, I get an error message that [Enter Group Number] is not recognized as a valid field name or expression. Are Parameter Values allowed in Crosstab queries? Any help to steer me in the right direction is appreciated. Recent versions of Access have become very picky with parameters and crosstabs. Have you defined the [Enter Group Number]...

Disk keeps filling up
Hello everyone... My Exchange server (Version 5.5) has a directory (Folder)... That keeps filling up with files and making my disk full. The folder name is: D:\EXCHSRVR\IMCDATA\IN\ARCHIVE I can't for the life of me figure out why... Can anyone help me? Just write2me@daleallen.com www.daleallen.com Thanks in advance Dale Allen CCNA, MCSE, CNE On Fri, 12 Aug 2005 09:50:24 -0400, "Dale" <banana_pages@yahoo.com> wrote: >Hello everyone... > >My Exchange server (Version 5.5) has a directory (Folder)... That keeps >filling up with files and making my disk f...

placing a value in a field
Hello: With the use of VBA, what code would you use to populate the Employee ID field in one window with the same empliyee ID (value) from another open window? Thanks! childofthe1980s Set the value of the second window field = to the value of the first window field. You will need to qualify the name of the field. Put on whichever window event is appropriate to your case - probably the After Open event. I can give you more exact information if you'll give me the object names involved. EmpID.focus (windowname.fieldname) Leslie "childothe1980s" wrote: > Hello: >...

keeping parts of a formula same
I need to ask a simple question which involves when draging the formula to the cells below to make copies. If I had a Fromula like say =A5/SUM(B5:B103)*B5 and I want to drag it to a couple of cells below making copies and saving my self some time. How to I prevent one part of the formula from changing. Because when I drag in down to the other cells I only want *B5 to change to *B6,*B7,*B8,*B9 etc. etc. so is there any way to do this? Put \$ in front of any references you want to keep the same ie =\$A\$5/SUM(\$B\$5:\$B\$103)*B5 You can also use this to keep partial refs constant eg \$A1 whic...

Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns: ColID - PRIMARY KEY Col1 - Number Col2 - Date/Time Next I created a multi-field index using Col1 & Col2. I entered the following row in the 1st row: 1 5/10/2007 Next when I tried to add the above row again, as expected, I wasn't allowed to enter the same row. Next I entered the following 2 rows: 1 5/11/2007 2 5/10/2007 Both the rows were accepted. After this I deleted the 3 records, went back to the design view & changed the data type of Col2 from Date/Time to Number keeping the multi-field index...

keeping font the same size
Every time I close a visio drawing and reopen it later to make updates, the font size keeps going from 12 back to 8 and it is annoying to keep changing. How can I keep it that the font stays at 12 when I re-open the drawing. I'd also like to do the same with the line weights. Visio 2003 is installed with latest updates. It is part of the office 2003 pro. package. Thanks. ...

how we can retrieve the value of edit control of combobox.
hi guys, how r u, can you please tell me how we can retrieve the value of edit control of combo box. actually i want to allow the user to input data in the combobox and then save this data in the database. i don't want to select already exist data in list box of combobox. please help me. raghunandan_1081@yahoo.com wrote: > hi guys, how r u, can you please tell me how we can retrieve the value > of edit control of combo box. actually i want to allow the user to > input data in the combobox and then save this data in the database. i > don't want to select already exist da...

Keeping macros
I've got lots of Macros on my Excel from Office 97. Now, I'm upgrading my computer and office suite to Office 2003. I have tons of macros that I use all the time. How can I export them to my new Excel? Thanks for the help. Don't know where your macro's are but, in my case, I record my macro's in a special workbook that I keep in the startup folder. When I went to Of2k I just copied the workbook to the startup folder on the new machine. Maybe something's different in Off2003(?). -- Regards; Rob ------------------------------------------------------------------------...

criteria with null value
Hi, I am running this SQL to find the price of an item: SELECT PARTS.UNITPRICE, PARTS.ITEMDESC, PARTS.WIDTH, PARTS.LENGTH, PARTS.LHEIGHT FROM PARTS WHERE (((PARTS.ITEMDESC)=forms!form1.descrip) And ((PARTS.WIDTH)=forms!form1.text2) And ((PARTS.LENGTH)=forms!form1.text4) And ((PARTS.LHEIGHT)=forms!form1.text6)); The problem is, if any of these items are null - the query shows no records. How can I conditionally check and ignore null values? Thanks in advance, Bonnie On Wed, 7 Nov 2007 14:08:00 -0800, Bonnie <Bonnie@discussions.microsoft.com> wrote: >The problem is, if any ...

keep or not keep SMTP log files?
Hi, I'm curious about what do you do with your SMTP lof files? How long do you keep your smtp lof files? Save them onto a tape media or external harddrive? Do you delete them after 6 months? Or don't keep them at all? I'm trying to get a general idea what administrators do to their smtp log files. Thanks and appreciate it. Ricky On Wed, 25 Oct 2006 13:01:04 -0700, Ricky T <RickyT@discussions.microsoft.com> wrote: >Hi, > >I'm curious about what do you do with your SMTP lof files? How long do you >keep your smtp lof files? Save them onto a tape medi...

how do I keep comments with cell?
I want the comments that I post to stay in the same format and with the same relationship to the cell that I post it in. Now,when I edit an existing file with comments, the existing comments float all over the file. I would also like to be able to reset the existing comments back to their original format. Code from Debra Dalgleish....... Reset Comments to Original Position If comments have moved out of position, you can reset them using the following code: Sub ResetComments() Dim cmt As Comment For Each cmt In ActiveSheet.Comments cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.L...

keeping aspect ratio when changing page size
hi I was wondering is their a way to lock the aspect ratio on all items when you change the page size? thanks bruce Bruce Leavitt wrote: > hi > I was wondering is their a way to lock the aspect ratio on all items > when you change the page size? > > thanks bruce ======================== Including text boxes? No. You could save the project as a picture and insert the picture on a new page. -- John Inzer MS-MVP Digital Media Experience Notice This is not tech support I am a volunteer Solutions that work for me may not work for you Proceed at you...

Outlook 07 keeps crashing