Formula help needed!

I have an amount in cell "A". I want cell "B" to decrease by an amount when cell "A" is increased.I am doing inventory, Cell "A" will be a case a Dr. does. Cell "B" will be an Item in my inventory. If I increase then number in cell "A"(meaning the Dr. did another case) I want 1 subtracted from each item used in my inventory list (Item totals in stock are in cell "B")
I hope I explained this good enough.

Gene.
0
Ace56 (3)
6/11/2004 7:57:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
235 Views

Similar Articles

[PageSpeed] 4

Hi
this is not possible with formulas. This would require VBA 
(using an event procedure). is this a possible way for you?

>-----Original Message-----
>I have an amount in cell "A". I want cell "B" to decrease 
by an amount when cell "A" is increased.I am doing 
inventory, Cell "A" will be a case a Dr. does. Cell "B" 
will be an Item in my inventory. If I increase then number 
in cell "A"(meaning the Dr. did another case) I want 1 
subtracted from each item used in my inventory list (Item 
totals in stock are in cell "B")
>I hope I explained this good enough.
>
>Gene.
>.
>
0
frank.kabel (11126)
6/11/2004 8:44:35 AM
Frank, I am new to Excel. I took the intro class this semester and going to the next level in summer. You lost my on your reply. Can you explain farther? I built the inventory program with Excel but did everything the Long Hard way. I learned that in the class. Anything I can do to keep from entering each item used in a case 1 at a time would be great. I thought I could get multiple items subtracted from inventory be setting a master to click on and having it mean to Excel to subtract this group of items from the list when I click on the master.  Gene
-- 
Thanks for your help!


"Ace56" wrote:

> I have an amount in cell "A". I want cell "B" to decrease by an amount when cell "A" is increased.I am doing inventory, Cell "A" will be a case a Dr. does. Cell "B" will be an Item in my inventory. If I increase then number in cell "A"(meaning the Dr. did another case) I want 1 subtracted from each item used in my inventory list (Item totals in stock are in cell "B")
> I hope I explained this good enough.
> 
> Gene.
0
Ace56 (3)
6/11/2004 10:03:01 AM
Hi
in this case I would suggest not to use VBA if you're 
still learning Excel. Not sure about your inventory data 
but one way to crate such a sheet would be the following:
1. Create a sheet with the following layout
A      B       C        D
ID     item    In/Out   Description
#1     item1   10       Buying new stocks 
#1     item1   -3       Selling 3
#2     item2   30       Buy stocks of item 2
.....

Of course you can add additional columns (e.g. date, 
comments, responsible, etc.)

Having such a database like structure would enable you to 
create a status report for it. One way would be using 
pivot tables for this. Find below some tutorial links:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm


HTH getting you started




>-----Original Message-----
>Frank, I am new to Excel. I took the intro class this 
semester and going to the next level in summer. You lost 
my on your reply. Can you explain farther? I built the 
inventory program with Excel but did everything the Long 
Hard way. I learned that in the class. Anything I can do 
to keep from entering each item used in a case 1 at a time 
would be great. I thought I could get multiple items 
subtracted from inventory be setting a master to click on 
and having it mean to Excel to subtract this group of 
items from the list when I click on the master.  Gene
>-- 
>Thanks for your help!
>
>
>"Ace56" wrote:
>
>> I have an amount in cell "A". I want cell "B" to 
decrease by an amount when cell "A" is increased.I am 
doing inventory, Cell "A" will be a case a Dr. does. 
Cell "B" will be an Item in my inventory. If I increase 
then number in cell "A"(meaning the Dr. did another case) 
I want 1 subtracted from each item used in my inventory 
list (Item totals in stock are in cell "B")
>> I hope I explained this good enough.
>> 
>> Gene.
>.
>
0
frank.kabel (11126)
6/11/2004 10:18:10 AM
Thanx Frank.......I was hoping there was an easy way to do what I wanted. I appreciate the links and will check them out and see what I can do.

-- 
Thanks for your help!

Gene
"Ace56" wrote:

> I have an amount in cell "A". I want cell "B" to decrease by an amount when cell "A" is increased.I am doing inventory, Cell "A" will be a case a Dr. does. Cell "B" will be an Item in my inventory. If I increase then number in cell "A"(meaning the Dr. did another case) I want 1 subtracted from each item used in my inventory list (Item totals in stock are in cell "B")
> I hope I explained this good enough.
> 
> Gene.
0
Ace56 (3)
6/11/2004 1:12:02 PM
Reply:

Similar Artilces:

MS Excel 2003 cannot auto calculate formula, need to press F9 each time
hi, I don't know why my excel 2003 new worksheet cannot auto calulate formula (eg. summation), i need to press F9 and it will refresh and show the new figure. there is "calculate" word at the left hand bottom of the screen. what is the likely reason ? it was running fine 2 weeks ago. any advise is greatly appreciated. rgds. Tools>Options>Calculation tab, check Automatic -- Kind regards, Niek Otten Microsoft MVP - Excel <sg_s123@yahoo.com.sg> wrote in message news:d5393a73-eb7d-4e08-8fab-5f4ab895f77a@e23g2000prf.googlegroups.com... | hi, | | I don't know w...

Error message needed if incorrect Date Of Birth (DOB) entered
Hi, In cell D6 I have a DOB entered....in cell E6 I have a datedif function that has 3 conditional formatting set as: 1 - If D6 is blank, E6 is blank. 2 - If DOB in D6 is between 18 - 24 then E6 shows age and cell is yellow. 3 - If DOB in D6 is 25 or over then E6 shows age and cell is blue. What I need is when a DOB is entered in D6 under 16 years old, then D6 returns an error message -" You have Entered an Age under 16 Years - Retry!! ". I have tried Data Validation, but cant get a message from tha as I dont know what to set as the date validation. I do need a...

Formula Help (to many expresions)
Could one of you give me a hand with this... I'm trying to put a formula in a spreadsheet that has too many expressions in it. I understand there is a limit to the number of equations that can be in a formula but there must be a way around the cap. Or maybe another way to write the formula? What I am trying to say in the formula is that if... If X is less than 09 then B1 = what's in cell C2 If X is less than 25 then B1 = what's in cell C3 If X is less than 51 then B1 = what's in cell C4 The expression I have written looks like this... =IF(X<10,"N/A",IF(X<...

simple countif formula
Column B in my spreadsheet consists of 10 rows with one letter in each cell. I would like a formula to count cells B1,B3,B5,B7 and B9 only if the value in each of those cells is "H". I have tried a simple formula like this =countif(B1,B3,B5,B7,B9,"H") but it does not work. Thanks for your help with this formula. -- Brian Try =SUMPRODUCT(--(MOD(ROW(B1:B9),2)=1),--(B1:B9="H")) HTH Bob "Brian" <Brian@discussions.microsoft.com> wrote in message news:0F4B5C54-D6DC-47E5-A198-5AD7FE281C5E@microsoft.com... > Column B in ...

mscvr71.dll help
How do I make my VS.net 2003 software not depend on msvcr71.dll? Thanks. Use static linking. I don't know where to set the option in VS7, but it used to be under Code Generation where you selected the desired C runtime library to use. In VS6 we had a choice between a dynamically-linked runtime and a statically-linked runtime. I've not had to make this choice under VS7 so I'm not sure where, in their overly-clever and completely gratuitous reimplementation of the user interface, this has been hidden. joe On Sat, 21 Feb 2004 22:24:56 GMT, anonymous@coolgroups.com wrote: &g...

Help with ShowFilter Macro
I'm trying to use this ShowFilter UDF written by Tom Ogilvy (see bottom of post). It says to use... =showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) ....in a cell to show the criteria for Column B. For one thing, I don't understand the cells B2 and B3 business. What is supposed to be in those cells? I would like this function to appear in the cell directly above or below the Autofilter menu cell. How do I change the function if the Autofilter criteria menu is in, cell A3, for example, and I want the criteria (this function) to appear just above, in cell A2? After trying all so...

Publisher 2003 Viewer is needed
Can Microsoft create a free viewer program for .pub files like they have for the other programs in Office? Can they? Yes Will they? Unlikely Mike Larry M. in Illinois wrote: > Can Microsoft create a free viewer program for .pub files like they have for > the other programs in Office? Larry M. in Illinois <Larry M. in Illinois@discussions.microsoft.com> was very recently heard to utter: > Can Microsoft create a free viewer program for .pub files like they > have for the other programs in Office? Could they? Yes Is it profitable for them to do so? Probably not. W...

OR formula, ?????
I have Worksheet A containing a list of data: Red, Yellow, Orange, Purple which is defined in NAME MANAGER as COLOR Worksheet B on CELL A1, is user input data A2 has the following formula =OR(A1=COLOR) User input : Red = FALSE Yellow = FALSE Orange = True Purple = FALSE The OR formula should produce TRUE value on the cell for all input that is true. However, it is not the case. Where is the formula wrong? Try this instead: =3DISNUMBER(MATCH(A1,COLOR,0)) Hope this helps. Pete On Feb 24, 12:19=A0am, a...

Please help with last formula for order form.
I am able to accomplish this with 1 column by the formulas below. Cell H160 is the subtotal: =IF(SUM(H72:H111)>0,SUM(H72:H111),"") Cell H166 the total: =IF(SUM(H160)>0,SUM((H160*H163)+H160),"") Cell H163 is for Tax. I am almost finished creating an order form. I would like to get the SUM of 3 different columns that are separated. I am not able auto fill strait down the column, because the information is separated in groups with titles, and the cells are not identically sized. I tried varations of this formula: =IF(SUM(H72:H111)+(116:131)+(135:154)>0,SUM ((H72:H...

Help with automating file name
I have the following code that exports the below query to excell. I would like the files name to include the month and date. How would I format this? DoCmd.OutputTo acOutputQuery, "qryShopOrderSqFtShippedSummaryExport",_ acFormatXLS, "W:\Cokato\Production\ProdRoomRpt.xls" -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1 Hi, Matt. > I would > like the files name to include the month and date. Uh, . . . the date _always_ includes the month, unless you're r...

Need Hyperlink from Menu Page to work in email
I have a workbook with several tabs. First tab is a "menu" type page (summary of other pages). Currently I have hyperlinks to all the other pages from the menu page. What I want to be able to do is copy the menu page into an email and enable the users to click on the existing hyperlinks (whatever adjustments needed) to go directly to their page of the workbook. Using Excel 2007. Please advise how to do this and what changes may be need for both options to work. ...

Formula #27
How do I enter a formula to calculate a 7% sales tax? If A1 holds the pre-tax price then =A1*7% will compute the sales tax while =A1*1.7 will compute the price_with_tax-included. Now all this is mathematically correct but we work in dollars and cents (or pound and pennies etc.), so we need to do some rounding to the nearest cent sales tax: =ROUND(A1*7%,2) price-with-tax =ROUND(A1*1.07,2) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "MissM07" <MissM07@discussions.microsoft.com> wrote in message news:344FF6D4-...

Advanced Formula troubles
I need to do the following calculation: ((1-((1-AE5)*10))*V14) but only when: ((1-((1-AE5)*10))*V14)>=0 or <=V14*1.5 If greater than or = to V14*1.5 then =V14*1.5 And if less than or = 0 then =0 Replace CALC with your calculation =IF(AND(CALC>=0,CALC<=v14*1.5),CALC,IF(CALC<0,0,CALC)) Your last statement is confusing. "And if less than or = 0 then =0"...equal to zero is mentioned in the 1st condition..So this should be 'less than' . -- Jacob (MVP - Excel) "Eves" wrote: > I need to do the following calculati...

help plz
my account has been inactive how to i reacctivate it? What account and what does this have to do with Outlook? "heather" <tracyliepke@yahoo.com> wrote in message news:066001c36c53$bb68d180$a501280a@phx.gbl... > my account has been inactive how to i reacctivate it? ...

Help with queries
Hi Guys, This is the first time after school that I am trying to use ms access at work and i need ur help in creating a query. Any help will be highly appreciated!! Here is what I need... I have relatively small ms acces database with about 1000 I have 3 colums date ipaddress sitename 12/09 34.3.3.3 A 12/09 34.3.3.3 A 12/09 34.3.3.3 A 12/09 33.4.4.4 B 12/09 33.4.4.55 C What i need is if an ipadress is recorded more t...

SmartList Restrictions help
I built a SmartList that is based on the Year-to-Date Transaction Open file, and has the Account Master linked to it. I want to restrict it to accounts that begin with 36, 38, or 2504. I tried adding a restriction that says "Account Number:Account_Segment_Pool1 begins with 36 OR 38 OR 2504", but I got no results when I did it that way. I also set up 3 separate restrictions, but that didn't work either. Is this possible? I can't find much information about how to write restrictions in SL Builder. ...

Conditional formatting help #4
My problem is that, that i want to ignore blank i mean i had set a conditional formatting say A B C D 24.9 25.9 25 25.8 22.6 23.4 22.5 23.3 If value in ColA is less than value in ColC, cell A1 is shaded blue OR if value in ColB is greater than value in ColD, cell B1 shaded blue. I have done above formatting but my problem is that if i dont enter anything in colC then also colA is shaded in blue similarly if i dont enter any value in colD then also col B is shaded.I mean i want to ignore the blank.I need , if col C is blank then the Col A must be normal .& if col D is blank & i ent...

Plugins, more info needed
Hi, We are looking at adding plugins to our app, but i am not sure what the best way of doing would it be. Does anybody know where i could find some example of project with their plug-ins. Preferably some code to play around with would be great. How would my app load those plugins? would they all be Dll's that load at runtime? We want to offer an SDK eventually allowing users to create their own plugins, so we want to do it properly to ensure that it is as flexible as possible. Many thanks. Sims. Hi, Typically, you have developers create a dll your app will load that exports cert...

Need help to choose loyalty program integrated with website
Hello. My name is Alex and I am working for franchise company using RMS system. We are looking for loyalty program integrated with web store. We have 12 franchisee stores using RMS and they are all conneted to our HQ system in main office. We want customers to earn point for each sales and redeem their points only at our website. (not on off-line store) Can anybody recommend best solution for our plan? Thank you. ...

=Sheet!A1 formula alternative
I have 2 sheets. Sheet 1 contains text data from A1:A200 but not in consecutive order (for example): Sheet1 A 1 textone 2 texttwo 3 4 textmisc 5 6 textother I need Sheet2!B1:B50 to grab all the data from Sheet1!A1:A200 and list them in the order that they were entered in Sheet1 as shown below: Sheet1 Sheet 2 A B 1 textone 1 textone 2 texttwo 2 texttwo 3 3 textmisc 4 textmisc ...

Help And Advive For A Novice
HELP AND ADVICE FOR A NOVICE This Is Not A Question About Using Excell But How To Download E -books I Have Purchased 2 E-books (a)f1 Excell And (b) Financial Statements. Encountered Difficulty In Downloading Because The Instructions Were Not Very Clear For Novice And Message Flagged Up "zip File Corrupted" I Have Utilised My Permitted Downloads. Therfore I Have Not In A Position To Use The E-books -- Joe Williams ------------------------------------------------------------------------ Joe Williams's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23...

formula help #42
What formula would I use to search down a column find a name and report the number in the next column, this would be multiple times, the numbers to be added together. The added number reported then to be multiplied by another number and then to be subtracted from another fixed number in a specific cell. Thanks in advance Jason You can sum the corresponding cells matched without having a dedicated column of numbers. =SUMIF(A1:A100,"Name",B1:B100) =(SUMIF(A1:A100,"Name",B1:B100)*AnotherNumber)-SpecificCell HTH, Paul -- "Boenerge" <Boenerge@discussions...

HELP! Balloon Payments in Debt Reduction
I have had this problem for so long it is getting really frustrating. When I put a debt into the debt reduction plan, it always puts a balloon payment on my first payment. Then, if I don't make that payment, it just builds and builds and builds with each subsequent payment I do make. How do I get rid of this? I removed the debt from my reduction plan the first time and that cleared it. But, the moment I started paying on the debt, it returned. Now, it's to the point that I can't get rid of it at all. When I remove the debt from the plan, it will do the same thing w...

SUMPRODUCT help #7
Hello: My question is if I have 3 or 4 criteria that need to be met before excel acknowledges the row and then once it does it will sum the accepted rows and return that sum into another sheet. Category Price Date Customer # InorgStock $30.00 10/20/2005101643 Inorg CB $415.80 1/23/2006 109597 InorgStock $15.00 1/23/2006 101643 Matrix Mod $50.00 1/23/2006 101643 If I want excel to only add up the extended price for the columns that met the criteria Customer Number=101643 and Category=inorgstock and Category=Inorg CB and Category=Matrix Mod and Document Date is >=10/20/05 but <...

Help Required with Macro to Manipulate Data
I have a spreadsheet(s) with the following data on it (this is imported from a text file) A B C D 1 Branch Date Total 2 4501 030204 29 3 4 4501 030204 14 5 6 4502 030204 331 7 8 4502 030204 52 9 10 4503 030204 54 11 12 4503 030204 85 Hi Alay and what do you want to achieve :-) -- Regards Frank Kabel Frankfurt, Germany "Alan T >" <<Alan.T.12jthe@excelforum-nospam.com> schrieb im Newsbeitrag news:Alan.T.12jthe@excelforum-nospam.com... > I have a spreadsheet(s) with the following data on it (this is imported ...