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

View 4 Replies
ADVERTISEMENT
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?

View 6 Replies
View Related
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.

View 1 Replies
View Related
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

View 9 Replies
View Related
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.

View 9 Replies
View Related
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

View 3 Replies
View Related
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.....

View 18 Replies
View Related
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.

View 9 Replies
View Related
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.

View 4 Replies
View Related
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.

View 1 Replies
View Related
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)))

View 5 Replies
View Related
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?

View 7 Replies
View Related
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?

View 5 Replies
View Related
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 ?

View 1 Replies
View Related
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

View 5 Replies
View Related
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.

View 9 Replies
View Related
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.

View 2 Replies
View Related
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"

View 1 Replies
View Related
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.

View 9 Replies
View Related
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: ....

View 10 Replies
View Related
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?

View 3 Replies
View Related
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

View 9 Replies
View Related
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.

View 4 Replies
View Related
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.

View 3 Replies
View Related
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.

View 4 Replies
View Related
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.

View 2 Replies
View Related
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

View 2 Replies
View Related
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.

View 6 Replies
View Related
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?

View 5 Replies
View Related
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.

View 9 Replies
View Related
Jul 10, 2012

I'm decently competent at VBA but no real experience with userforms. I have a very simple 3x2 table:

Type $Amount

Cash $xxxxxx

Product A $yyyyyy

Product B $zzzzzz

I want a simple userform that moves money between the products, ie the user selects a money amount in the form (ex: $1000) and that amount is added from one product and simultaneously subtracted from the other. Note that the amount selected would be subtracted from one product's current balance and added to the existing balance of another product. No new money is coming into the system.

View 6 Replies
View Related
Dec 31, 2008

Col A contains SS#'s and Col B contains names. Quite often there are duplicates and sometimes triplicates because they may be sending payments for multiple providers. We charge one fee per client (unique SS#), regardless of the number of times they may be on the spreadsheet.

I can get a grand total by summing the unique items by using this formula:

=SUM(IF(LEN(A2:A4970),1/COUNTIF(A2:A4970,A2:A4970)))

and then doing a CTRL-SHIFT-ENTER to put brackets around it so it will "take."

However I now need to show the fee by each unique name. Right now I am inserting Col C, typing in 3.00 into C2 and copying on down, but then have to manually remove the amounts in the rows which contain duplicate ss#. Long painful process. How can I put a formula in Col c so that it will only show fee one time by the first unique ss#?

View 9 Replies
View Related
Dec 4, 2013

I am using the following code to update an invoice summary sheet from an invoice sheet but when a Credit Note is issued I would like the Nettï¿½ value copied across to be a negative figure. When a credit note is issued the value of Service Invoice Cell L2 = 2 and an invoice L2 =1.

VB:

Sub UpdateSummary()

' Updates Summary Sheet with Invoice Details

Dim InvoiceDate As Date, InvNo As Integer, JobNo As Integer, Company As String, Location As String, Nettï¿½ As Currency

[Code].....

View 4 Replies
View Related
Aug 13, 2014

I want to link a number of cells on one worksheet back to another worksheet within the same workbook. Say everything from Cells A1 to M90.

I find the fastest way to do this link A1 on second worksheet to A1 on first worksheet (by using = and click on worksheet 1 Cell A1).

I then click on cell A1 on worksheet 2, grab small square in bottom right hand side of cell, and drag accross to M1.

While A1 TO M1 is still highlighted, I then grab small square on bottom of cell M1 and drag down to M90.

This makes all cells within this range reference back to worksheet1. In advanced settings I have unchecked for all blank cells to show a zero in woksheet 2.

Therefore I now have what I currently want, although I would like to make all reference absolute?

I tried doing this from the beginning again making A1 absolute at the start, but dragging the cells across and down does not provide the info I am looking for in all cells?

View 1 Replies
View Related
Sep 8, 2009

I need a cell to restrict the input:

-Only numbers are allowed.

-No date posible.

-Any amount of decimals (they must all be shown in the cell).

I tried using the data validation and using the IsNumber() to restrict any non numeral input. The problem with this approach is that if the user enters a date; it apprears as a date format (eg: "5.May"). I'm using an european excel, where the decimal separator is a comma instead of a point; so if a user accidentaly types "5.5" instead of "5,5"; the cell will show "5.May".

I also tried the cell format/number/number format. The problem in here is that I dont know how many decimal positions will the input number have; and I need them all to be shown.

View 7 Replies
View Related
Mar 5, 2014

I have a report that I want the total amount (in the bottom right corner, but can vary in row number) to appear in a particular cell in the header. In the macro, I selected the cell & entered "=CTRL+End". It works in reality, but not in a macro. It returns a visual of 0.00, but "=XFB1048565" in the formular bar. How can I get the header cell to return the correct total amount?

View 3 Replies
View Related
Nov 17, 2006

1. jump to any cell > 100000

or

2. write a formula to check entire range of sheet for any one cell over this amount... maybe returning TRUE if so, FALSE if not?

View 9 Replies
View Related
Jul 3, 2006

I have excel sheet with colored cells.

Positive cash flow cells all have green background,

Negative cash flow cells, all have red background.

I calculate all the "sub- total" columns into one "total sum" cell. Based on this "total sum" I want the cell background to be red if the total sum is negative, or green if total sum is positive. How do I make the cell change to the proper background color based on whether it's a negative or postive total sum?

View 2 Replies
View Related
Jul 23, 2014

I have a table that looks like:

7300

3

7301

2

7302

5

And I am trying to make a macro that will go concatenate the first 3 columns of all rows in worksheet 7300, the first two columns of all rows in worksheets 7301, etc, and insert them into column A of each corresponding worksheet.

View 7 Replies
View Related
Nov 25, 2008

I have an excel sheet and I want a Msg pop up to show a certain amount from a specific cell. So if the cell shows $100. I want to a do a macro that if I hold control + shift + a I will get a pop up saying "Your amount is (value of the cell).

I can only get it to work if I want the vale from an active cell.

MsgBox "The value in the active cell is " & ActiveCell.Value & "."

View 9 Replies
View Related