# Reference Cell & Add Amount If Positive & Subtract Amount If Negative

Jun 5, 2008

Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do:

I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.

ADVERTISEMENT

## Subtract Amount From Cell Until It Reaches 0 Then Move And Subtract From Next Amount?

Apr 28, 2012

I am trying to subtract an amount from a cell until it reaches 0, then move and subtract from the next amount, and so on.

AmountFixed BudgetedResult53.50Subtracted 5 tell it hit zero1.30Subtracted remaining amount until zero43.8Subtracted remaining amount until zero3.8 was left over without hitting zero is good. Is there formula for this?

## Formula To Add Row Of Numbers And Then Subtract If From Another Cell Amount

Dec 1, 2013

I have a row A through N. In cell A1, have a dollar amount. In cells C1 through N1, I have different dollar amounts. In cell O1, I want to have a formula that will take the dollar figure in A1 and subtract from that amount the total of all the amounts in cells C1 thorugh N1. But I don't know how to write that formula.

Let's say I've got \$100 in A1, and then in C1 through N1, I have \$2, \$3, \$4, etc. for a total amount of \$94 in cells C1 through N1. I'd like to have a formula in O1 that would show \$6.

How to write up that formula in cell O1.

## Subtract Positive And Negative Numbers

Sep 14, 2009

IF formula.

I am trying to subtract 2 numbers, and they are 4 different combinations of positive and negative numbers.

I wrote the formula as
=IF(A5*B5>0,IF(AND(A5*B54,458 2,367 (322)FALSE(1,277)1,751 FALSE

## Subtract X Amount Every Y Years

May 28, 2009

I am building an investing strategy model and am looking for a fomula or combination of formulae that would subtract an amount (lets say a 100) every so many years (lets say 10). Data is set up horizontaly, i want to be able to set how much will be subtracted and how often in a "control panel" next to other inputs and variables. I am not proficient in Macros and most often have trouble with them so if its possible to solve this without using any that would be great.

## IF Statement: Use The First Amount In Column P That Is Positive?

Apr 6, 2009

My Column P (Payback) has amounts ranging from negative to positive. In one of my calculations, I would like to use the first amount in Column P that is positive.
Depending upon scenario, sometimes this could be P4 other times it might be P17

## Formula To Subtract Invoices From Total Amount Available

Sep 25, 2009

I have 2 tables, one with invoices, the other with purchase orders. I would like to have a "PO Amount Remaining" column on the invoice table that looks up the PO listed on the invoice with the PO \$ amount on the PO table. Once it is matched, I would like to subtract the total invoices to give me the amount of the PO that is left. Basically I would like the "PO Amount Remaining" column right now to have \$4,200 listed in each row for invoices 1 & 2.....

## Formula- To Calculate The Amount Due Based On Cumulative Sales Once A Breakpoint Amount Is Reached

Jan 28, 2009

I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.

Example:

Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%

month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00

and so on...until the end of year.

I tried using an if formula by could not get it to work.

## Currency Formatting Show A Negative Amount

Jan 19, 2010

When a user enters an amount in a cell, in �'s, i need it to show a negative amount. So if they enter �100 I want excel to regard it as -�100.

## Macro For Base Amount And Net Amount Calculation

Nov 20, 2012

For the below macro.

ABCDG
CodeTotalBaseNetA1 = 20%
A110083.3316.67A2 = 10%
A2350318.1831.82A3 = 7%
A3220205.6114.39A0 = 20%
A015015030

A1 is the code, take the total(B) and calculate the base(C) on the percentage given in the G, net = B-C. for A2 and A3 it is the same process.

For A0 the total (B) is equal to Base(C) and calculate the net on the given percentage in G.

## How To Change Reference Cells Specific Amount When Dragging

Aug 14, 2014

So I'm dragging my formula down one cell and as expected the reference cell changes the same amount when I really want the reference to shift Down that column 11 cell so when I take my formula

=IF(ISERROR(LOOKUP(21474836472147483647,'Revenue Book'!L5:L11)),"",(LOOKUP(21474836472147483647,'Revenue Book'!L5:L11)))

when I drag it down one cell the reference should look like this

=IF(ISERROR(LOOKUP(21474836472147483647,'Revenue Book'!L16:L22)),"",(LOOKUP(21474836472147483647,'Revenue Book'!L16:L22)))

## Positive To Negative If Cell On Left Negative

Sep 1, 2007

I have data starting in E7. I want it to go down the column and find the negative numbers. If it finds one then I want it to change the number in the row to the left of it to a negative. So if E67 is a negative number, make D67 a negative and so forth down the line Sounds "simple" but how do I do it?

## Iteration Inconsistency: Allow For A Cost Being Added To Loan Amount Where The Cost Is Based On The Total Loan Amount

Mar 15, 2007

In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001

On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?

## Cell Colours For Negative And Positive Values

Jan 24, 2014

I've got 12 months worth of business accounts which have been downloaded onto a single spreadsheet.

These show negative and positive values. As I only want to work on the negative figs at the moment, how do I highlight the colour of the positive value cells or font to prevent any mistakes ?

## Cell Text Based On A Positive/negative Value

May 11, 2006

if c12 = \$(55.63) which is a negitive value i wont d12 to display OWE.

if c12 = \$55.63 which is a positive value i wont d12 to display CREDIT.

These value can vary

## Find Column "Amount". Insert Column Next To Amount

Feb 22, 2008

I need some code to do the following.

Look at worksheet 1. Find column "Amount". Insert column next to amount.
For all highlighted cells in column "amount" insert text "R" in adjacent (inserted) column.

## Cell Value In A Column Less Than Specified Amount

Sep 29, 2009

I just need a warning box that indicates a user has entered a cell value under 200. Here's what I have but it's not working.

## Amount Of Days In A Cell?

Dec 13, 2012

figure out the amount of days in a cell.

I have dates in "A"

and need the amount of days to show up in "C"

EX:

A B C
11/24/12
12/13/12 18

So i need the code to have the "18" show up when I enter the date in "A"

## VBA Code To Change Positive Value To Negative Upon Entry Into Cell

Jan 16, 2008

I have a worksheet in which the values in cells B8, H37, H42, H48 and H49 must be negative. Occasionally the values get entered as positive numbers.

I would like to use VBA code to automatically change the value from positive to negative.

It appears that the WorkSheet_Change event could be used to do this but I don't know what code to enter into the Sub.

## Zero Amount If Cell C14 Is Blank AND Add Cells

Apr 15, 2008

I need a formula that will give a zero amount if cell C14 is blank AND add cells C14 & C13 if cell C14 & C13 are not blank AND add Cells C14 & C12 if Cell C13 is blank but cell C14 is not.

I tried this formula but it will not work: ....

## Limit The Amount Of Characters Per Cell

Feb 10, 2009

Is there a way of limiting the amount of characters you can enter in each cell?
Also is there a way to apply this to an existing sheet that has cells with more characters in it than I would like? ie. If an existing cell has 25 characters in it, could i cut it down to the first 10?

## If Statement To Accumulate An Amount In A Cell

Sep 10, 2009

In a cell I have a description, say "FOOD" then another cell with amount say �2, then in another cell I want it to be looking and if it sees "FOOD" entered, it takes the amount �2 and adds it to a running balance.

Cell A1 = Food
Cell B1 = �2

Cell H1 = (looks and if Food) adds �2 to cell

## Putting In A Minimum Amount In A Cell

Sep 25, 2009

I have a row that is a percent of sales with this:

=(F4*0.2+G4*0.1)*I4

"I4 will either be 1.0 or 0.5"

Now I want to have it so if this equation equals less than 100 with I4 being 1.0 to equal 100 OR if I4 is .5 then it should be 50. I have looked all around and I am no excel wizard so this is troubling me significantly.

## Copying Cell X Amount Of Times?

Feb 29, 2012

To put this simply, I have a spreadsheet that has 2 columns on it with multiple names and numbers on it. We'll say ABC and 123 as the example name and number. When I type in ABC and 123, I have it where it appears on the next page.

The question is that if I want to duplicate it onto the next page, how do I do it? In other words, I want to create a 3rd column with which to add a number of times I want the numbers and letters to appear. For example, I write| ABC | 123 | 5 | and ABC appears on the next page at a certain start point and is copied 5 times in certain cells. The same happens for 123 at its own start point.

## If Cell Contains Character Then Use Amount With + Or - Sign

Apr 4, 2013

I have a spreadsheet where column E needs to be filled with an amount depending on whether another cell in column F has either a + or - sign.

For example:

F2 has "+ ACH PreAuthorized", G2 has "322.1". I would like to have cell E2 fill in the amount "+322.1". So in cell E2, if F2 has a plus sign, then put in the amount in E2. If it has a - sign, put in the amount from G2 into E2 but put a - sign in front of the amount. If there is no sign at all, put a zero or leave blank.

## Showing Both Positive And Negative Numbers As Postive In Adjacent Cell.

Jan 19, 2009

In my first column I will be entering both negative and positive numbers. In the cell next to it I need the amounts to all show as positive only. So both negative numbers and positive in the first column will all show as positive in the adjacent column/cell.

## Data Validation - Negative And Positive Numbers Based On Another Cell?

Mar 15, 2012

I have created a template using data validations. I have a drop down in column O, which you can choose from Credit or Charge. In column V, I want:

- If selected Credit in column O, only negative numbers allowed in column V

- If selected Charge in column O, only positive numbers allowed in column V

## How To Change Positive Into Negative Number Inside Cell Formula

Feb 7, 2014

How do I change a Positive number into a Negative number inside a cell formula?

I have a figure in Cell AE 101 which has the positive number 141

I have another cell W1 which has the positive number of 437

I want to create a formula in cell X1 that takes the number in cell AE 101 of 141 and convert the number inside the formula of 141 into a negative number of -141 and then subtracts that number from Cell W1 of 437 leaving an equation inside the formula to read. 437-141 = 296

Since the number in Cell W1 changes all the time and the number in AE101 Changes all the time the new formulated number in X1 will change all the time too as the cells get updated.

## Change A Cell To Be Positive Or Negative Based On Name From List On Another Cell

Dec 8, 2013

My goal from image is to enter a number for example in cell F4 and have in appear in L6 so simply L6=F4 but I want it to be negative if cell M2 reads "Outgo" and positive if cell M2 reads "Intake" as selected from the list. Is this possible?

## Cell That Doesn't Reach The Desired Amount

Jan 14, 2006

function that add to any cell that doesn't reach the disired amount.

If the required number is 14 and a calculated cell comes up with 13 or less; I need a funtion to take that number and add a penalty number to it.

For example: For arguements sake the penalty is \$150.00. Suppose cell 1A has 5 and cell 1B has 5; cell 1C adds them for a total of 10. I need cell 1D to realize that the number in 1C did not equate to atleast 14 and therfore 1D should show \$150.00 and for every cell selected that falls short of 14 should add an additional \$150.00 to 1D.

Copyrights 2005-15 www.BigResource.com, All rights reserved