Reference Cell & Add Amount If Positive & Subtract Amount If Negative
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 totalscell 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 onhand 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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Subtract X Amount Every Y Years
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 Replies!
View Related
Formula To Subtract Invoices From Total Amount Available
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 Replies!
View Related
Positive To Negative If Cell On Left Negative
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 Replies!
View Related
If Statement To Accumulate An Amount In A Cell
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 Replies!
View Related
Putting In A Minimum Amount In A Cell
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 Replies!
View Related
Limit The Amount Of Characters Per Cell
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 Replies!
View Related
Formula To Put An Amount Into A Cell Next To Only Unique Item
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 CTRLSHIFTENTER 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 Replies!
View Related
Cell That Doesn't Reach The Desired Amount
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 Replies!
View Related
Cell Format: Only Numbers (no Date) With Any Amount Of Decimals
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 Replies!
View Related
Making A Cell Change Background Color Based On If Positive Or Negative Sum Total
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 Replies!
View Related
Formula To Show A Date Range Based On The Amount Shown In Another Cell
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc. I added a new cell called Track Back on the employee search sheet. What I want to do is only show variances for the amount of days back selected in the Track Back cell. For example if I select the last 30 days, only the last 30 days would show up below in the sheet. I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
View Replies!
View Related
Subtract But With Positive Sum
Im working on a time schedule form for my work. It all works good but it have one little error. Lets say it looks like this cell a1 has start time 22:00 and a2 has a end time 07:00. i know that i can switch the cells in the formula =sum((a1a2)*24). like this i´ll get a negativ answer and that f.cks up the totalt becuse of the negativ answer i get. is there a way to get 7:00  22:00 and 22:00  7:00 to calculate as positiv with one formula?
View Replies!
View Related
Operational Model  Shift Start Time Plus Amount Of Hours Worked In One Cell.
Is it possible that a cell contains both numeric and alphanumeric data and to do calculations on that? For example: if a cell conatain the value "10a" or "8.5b" etc. Would it be possible to have a column that gives me the hours worked (the numeric value in the cell) and a line that gives me the amount of people that are working on shift "a" (the alphanumeric value in the cell). Is this at all possible? Or does that require VBA/Macros and stuff (in which case this is posted in the wrong part of the forum )
View Replies!
View Related
Add Positive Or Negative From Division
I have a forecast for a week, all days I type in, the real order, in the end of the week I got a difference between the forecast and the real orders. the difference can be positive or negative, in any case I want that difference to be added on the next cell on next week, plus the difference between the forecast and the real order from the day before, and so on. I'm using this formula "E4*($L$3+1+IF(K4,ABS(K3/K41),0))" but if the real order is less than the forecast the formula add the difference increasing in a positive way, when what I want is decrease the value if the difference shows a negative tendency. hope this make sense. I attached a file with what I intend to do.
View Replies!
View Related
Matching Negative Value W/ Corresponding Positive Value
I have a spreadsheet of invoices and credits memos (500+ rows). Is there a way to match the positive and negatives that match? For example if there is 40.39 in one cell and positive 40.39 in another that it could highlight both those cells. I was thinking to do it in conditional formatting however I can not get it to work. It does not have to be fancy I just need to highlight them because there will not be a lot that do actually match.
View Replies!
View Related
Convert Positive # To Negative
Is there a simple way to convert a positive number to a negative number ? Fromto 10(10.00) 56(56.00) 116(116.00) Additionally, would the same procedure work for time? So if I had a column listing hours that looks like this: 10:05:00 4:34:00 2:18:06 is there a way to convert this to negative hours? Understandably, there are no such thing as negative hours, but I am trying to illustrate a situation where an internal procedure was completed x amount of hours before the specified start time.
View Replies!
View Related
Ensure A Negative Or Positive Value
My form has a combobox with three options "Withdrawal" "Deposit" "Fee". I want to make sure that whatever number a user puts into a textbox, if they select "Withdrawal" or "Fee" that number will be converted to a negative number, and if they select "Deposit" it will be positive. I have written the following code and am just wondering if there is some super slick way of doing it other than an if statement.
View Replies!
View Related
Pair Up Positive And Negative Numbers
************************************************************************>Microsoft Excel  Book2.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2B3B4B5B6B7B8B9B10B11B12B13B14B15B16B17B18B19B20B21B22B23B24B25B26B27B28B29=ABCD1 amount Abs amtMark x when paried up 2 (43,746.73)43746.73x 3 43,746.73 43746.73x 4 43,746.73 43746.73 5 (60,579.30)60579.3 6 (60,579.30)60579.3x 7 60,579.30 60579.3x 8 (98,416.85)98416.85x 9 98,416.85 98416.85x 10 98,416.85 98416.85 11 (500,000.00)500000 12 (500,000.00)500000 13 (500,000.00)500000 14 (500,000.00)500000x 15 500,000.00 500000x 16 (700,000.00)700000 17 (700,000.00)700000 18 (700,000.00)700000x 19 (700,000.00)700000x 20 (700,000.00)700000x 21 (700,000.00)700000x 22 (700,000.00)700000x 23 (700,000.00)700000x 24 700,000.00 700000x 25 700,000.00 700000x 26 700,000.00 700000x 27 700,000.00 700000x 28 700,000.00 700000x 29 700,000.00 700000x Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR. ****** httpequiv="ContentType" content="text/html; charset=utf8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"> Hi everyone, I can't figure out how to mark a "x" for the paired up figures in column A as shown in the above spreadsheet using a formula(e) and/or VBA. At the moment I'm doing this manually. Imagine doing this for thousands of line... once a week
View Replies!
View Related
Formula To Change A Negative No To A Positive
This may be more of a math question rather than an Excel one. If I have a spreadsheet with a cell that produces different negative number each time it is used, is there a way i can add a formula to make it a positive number. So if Cell A1 showed £244.22, I want to add an equation that changes if to £244.22 Cell A1 changes so it would need to do the same for any negative number within that cell.
View Replies!
View Related
Convert Positive Numbers To Negative
I have created a Pivot table from a source data file that includes a document status column ('Type I Or C') (I for Invoice & C for Credit) and an Invoice / Credit value column (Invoiced Price). However the values for both invoices and credits are positive so when summed distort the total values. I am attempting to create an additional column within the Pivot table to convert the credit note values to negative by inserting a calculated field under the formula tab, i.e. =IF('Type I Or C'="C",'Invoiced Price','Invoiced Price') The above formula works fine in a standard worksheet but it is not reckonsing the "C" within the pivot table. If I manually change the value to numeric (i.e. 1 for Invoices and 2 for credits) it works fine, i.e =IF('Type I Or C'="2",'Invoiced Price','Invoiced Price') so it appears to be an alpha issue?
View Replies!
View Related
Repeat Rows By Mutiplying Current Amount Of Rows By Cell Value
I have a table which contains a list of branches eg; 01 02 04 06 I then have a spreadsheet which contains a number of columns and rows. I want to create a new spreadsheet in the first column it will contain the branch from the table above then the rows and columns in the above spreadsheet The rows and columns would then repeat for each of the other branches in the table. IE if I have 4 branches in my table and 100 rows in my spreadsheet my new spreadsheet would contain 400 rows
View Replies!
View Related
Dollar Amount
How can I display the $xx.xx into a statement? I have these $ amounts in column A: 99.12 107.65 14500.99 Have the result in column B as: Please Pay Exactly$00000xx.yy (where xx is tenth, hundreds, thousands and yy is the cent) 00000 is to prevent forgery and to have the results in column C using same column A: Please Pay Exactly$0000xxDollars and yycents For example like Please Pay Exactly$000099Dollars and 12cents or Please Pay Exactly$000014500Dollars and 99cents
View Replies!
View Related
Add Amount In Column
Trying to add a formula in my spreadsheet that will add every amount in column AQ8:AQ200 if the date in AQ8:AQ200 is march. The format for the date is 31209. Here is what I'm trying. =SUMIF(AU8:AU200,MONTH=3,AQ8:AQ200)
View Replies!
View Related
Replacing $ Amount With X
I need a formula that will replace a dollar amount with an X. In other words...I have 10,000 cels and half of them have dollar amounts in them...I would like to replace them with the letter X without having to manually do it...
View Replies!
View Related
Cap Calculated Amount
I've written a custom function that calculates commissions (as a %) based on three variables. I wanted to know what code I can use to cap the amount of commission paid out to a specific % amount. Something like "as soon as X exceeds 3 times the amount of Y, payout 3 times Y as the maximum".
View Replies!
View Related
Fornula For Displaying Positive Or Negative Variance
I need a formula that will display a 'yes' or 'no' if the following condition is met: If the value of cell (L17) is greater than 10% positive variance, then 'YES' else 'NO'. Currently the value of L17 is 14%, therefore should be a 'YES', however how can I get the following to work: IF(AND(L17=<0,"NO",L17>10%,"YES")) I apologise as I know there are too many arguements here but is there a way around this?
View Replies!
View Related
