Purchase Order Formula

   After recently finding out that the Shipping Field does absolutely nothing 
in a purchase order reguarding Costs of Goods, or anything for that matter, I 
decided that "oh, I can just use a Formula to change the costs in my purchase 
order to reflect my shipping costs!" So, after going through and adding up 
all the quantities of items recieved, and dividing it by my shipping cost, I 
got a cost of $1.40/item. No problem right? Wrong. The Formula will in no way 
reflect a NEWLY updated cost. And I cannot use Formulas after the purchase 
order is committed!!!!! >:O

   FOR EXAMPLE: I have item 123. Item 123 used to cost me $3, it now costs 
me $5. So, in a purchase order, I enter item 123, and put in a new cost of 
$5. NOW, when I go to use the FORMULA to add in shipping, it only has options 
that reguard to the OLD costs and other things that dont do crap for me. So 
if I say, "Add 1$ for each item cost, my ending cost becomes $4 because the 
purchase order Formula does not take the NEW cost of $5. The only way the 
Formula will use the UPDATED $5 cost is to COMMIT the purchase order which 
then renders the Formula field useless because you then cannot click on it to 
edit the costs.

    I must say, shipping is a huge part of our end-of-the-day cost, and 
there is absolutely no way to make this work easily for us. Problems like 
this keep popping up in RMS and my Boss' are getting very frustrated with 
this system. 
0
Tim6814 (325)
12/10/2007 5:31:00 PM
pos 14173 articles. 0 followers. Follow

5 Replies
491 Views

Similar Articles

[PageSpeed] 25

Tim,

Would you be interested in custom solution for your problem?. If so we can 
look into this issue. Please drop a email.

Regards,
Manick
manick.m@hotmail.com

"Tim" wrote:

>    After recently finding out that the Shipping Field does absolutely nothing 
> in a purchase order reguarding Costs of Goods, or anything for that matter, I 
> decided that "oh, I can just use a Formula to change the costs in my purchase 
> order to reflect my shipping costs!" So, after going through and adding up 
> all the quantities of items recieved, and dividing it by my shipping cost, I 
> got a cost of $1.40/item. No problem right? Wrong. The Formula will in no way 
> reflect a NEWLY updated cost. And I cannot use Formulas after the purchase 
> order is committed!!!!! >:O
> 
>    FOR EXAMPLE: I have item 123. Item 123 used to cost me $3, it now costs 
> me $5. So, in a purchase order, I enter item 123, and put in a new cost of 
> $5. NOW, when I go to use the FORMULA to add in shipping, it only has options 
> that reguard to the OLD costs and other things that dont do crap for me. So 
> if I say, "Add 1$ for each item cost, my ending cost becomes $4 because the 
> purchase order Formula does not take the NEW cost of $5. The only way the 
> Formula will use the UPDATED $5 cost is to COMMIT the purchase order which 
> then renders the Formula field useless because you then cannot click on it to 
> edit the costs.
> 
>     I must say, shipping is a huge part of our end-of-the-day cost, and 
> there is absolutely no way to make this work easily for us. Problems like 
> this keep popping up in RMS and my Boss' are getting very frustrated with 
> this system. 
0
manick (194)
12/11/2007 5:29:00 AM
Our Power Ops add-in for RMS provides freight allocation for PO's.
You can choose to prorate by dollars or units.
You can also copy PO's, merge open PO's into one before placing, and much 
more.
30 day trial download:  www.digitalretailer.com/trial

Greg
Digital Retail Solutions
www.digitalretailer.com/RMSaddins

"Tim" <Tim@discussions.microsoft.com> wrote in message 
news:6CD6BF0A-A138-406E-812B-3266EB0FBBBD@microsoft.com...
>   After recently finding out that the Shipping Field does absolutely 
> nothing
> in a purchase order reguarding Costs of Goods, or anything for that 
> matter, I
> decided that "oh, I can just use a Formula to change the costs in my 
> purchase
> order to reflect my shipping costs!" So, after going through and adding up
> all the quantities of items recieved, and dividing it by my shipping cost, 
> I
> got a cost of $1.40/item. No problem right? Wrong. The Formula will in no 
> way
> reflect a NEWLY updated cost. And I cannot use Formulas after the purchase
> order is committed!!!!! >:O
>
>   FOR EXAMPLE: I have item 123. Item 123 used to cost me $3, it now costs
> me $5. So, in a purchase order, I enter item 123, and put in a new cost of
> $5. NOW, when I go to use the FORMULA to add in shipping, it only has 
> options
> that reguard to the OLD costs and other things that dont do crap for me. 
> So
> if I say, "Add 1$ for each item cost, my ending cost becomes $4 because 
> the
> purchase order Formula does not take the NEW cost of $5. The only way the
> Formula will use the UPDATED $5 cost is to COMMIT the purchase order which
> then renders the Formula field useless because you then cannot click on it 
> to
> edit the costs.
>
>    I must say, shipping is a huge part of our end-of-the-day cost, and
> there is absolutely no way to make this work easily for us. Problems like
> this keep popping up in RMS and my Boss' are getting very frustrated with
> this system. 


0
support7094 (156)
12/12/2007 12:09:59 AM
If I have 20 lanes, but only one back office computer and only need this 
solution one one back office computer, are you going to charge me for 20 
lanes, or one computer?


"DRS Support" <support@digitalretailer.com> wrote in message 
news:e1RAyYIPIHA.5140@TK2MSFTNGP05.phx.gbl...
> Our Power Ops add-in for RMS provides freight allocation for PO's.
> You can choose to prorate by dollars or units.
> You can also copy PO's, merge open PO's into one before placing, and much 
> more.
> 30 day trial download:  www.digitalretailer.com/trial
>
> Greg
> Digital Retail Solutions
> www.digitalretailer.com/RMSaddins
>
> "Tim" <Tim@discussions.microsoft.com> wrote in message 
> news:6CD6BF0A-A138-406E-812B-3266EB0FBBBD@microsoft.com...
>>   After recently finding out that the Shipping Field does absolutely 
>> nothing
>> in a purchase order reguarding Costs of Goods, or anything for that 
>> matter, I
>> decided that "oh, I can just use a Formula to change the costs in my 
>> purchase
>> order to reflect my shipping costs!" So, after going through and adding 
>> up
>> all the quantities of items recieved, and dividing it by my shipping 
>> cost, I
>> got a cost of $1.40/item. No problem right? Wrong. The Formula will in no 
>> way
>> reflect a NEWLY updated cost. And I cannot use Formulas after the 
>> purchase
>> order is committed!!!!! >:O
>>
>>   FOR EXAMPLE: I have item 123. Item 123 used to cost me $3, it now costs
>> me $5. So, in a purchase order, I enter item 123, and put in a new cost 
>> of
>> $5. NOW, when I go to use the FORMULA to add in shipping, it only has 
>> options
>> that reguard to the OLD costs and other things that dont do crap for me. 
>> So
>> if I say, "Add 1$ for each item cost, my ending cost becomes $4 because 
>> the
>> purchase order Formula does not take the NEW cost of $5. The only way the
>> Formula will use the UPDATED $5 cost is to COMMIT the purchase order 
>> which
>> then renders the Formula field useless because you then cannot click on 
>> it to
>> edit the costs.
>>
>>    I must say, shipping is a huge part of our end-of-the-day cost, and
>> there is absolutely no way to make this work easily for us. Problems like
>> this keep popping up in RMS and my Boss' are getting very frustrated with
>> this system.
>
> 


0
12/12/2007 1:55:32 PM
Try plaing with currency values. Invent a new currency, then convert it to US 
$$$. For this to work, you need to know the percentage of the shipping cost 
in the total value of the product for the given supplier. Then reflect that 
percentage in the currency conversion. We are importing our products from 
Poland and airfreight cost constitutes a big part of the total cost of the 
product.


"Tim" wrote:

>    After recently finding out that the Shipping Field does absolutely nothing 
> in a purchase order reguarding Costs of Goods, or anything for that matter, I 
> decided that "oh, I can just use a Formula to change the costs in my purchase 
> order to reflect my shipping costs!" So, after going through and adding up 
> all the quantities of items recieved, and dividing it by my shipping cost, I 
> got a cost of $1.40/item. No problem right? Wrong. The Formula will in no way 
> reflect a NEWLY updated cost. And I cannot use Formulas after the purchase 
> order is committed!!!!! >:O
> 
>    FOR EXAMPLE: I have item 123. Item 123 used to cost me $3, it now costs 
> me $5. So, in a purchase order, I enter item 123, and put in a new cost of 
> $5. NOW, when I go to use the FORMULA to add in shipping, it only has options 
> that reguard to the OLD costs and other things that dont do crap for me. So 
> if I say, "Add 1$ for each item cost, my ending cost becomes $4 because the 
> purchase order Formula does not take the NEW cost of $5. The only way the 
> Formula will use the UPDATED $5 cost is to COMMIT the purchase order which 
> then renders the Formula field useless because you then cannot click on it to 
> edit the costs.
> 
>     I must say, shipping is a huge part of our end-of-the-day cost, and 
> there is absolutely no way to make this work easily for us. Problems like 
> this keep popping up in RMS and my Boss' are getting very frustrated with 
> this system. 
0
Zajac (1)
12/29/2007 5:17:00 AM
Wow. Try training a new employee in that procedure.

My boss would hit the ceiling if he knew I was using a work-around like 
that.

"Jurek Zajac" <Jurek Zajac@discussions.microsoft.com> wrote in message 
news:EC09550C-C251-4AF7-951F-ECC22597DC93@microsoft.com...
> Try plaing with currency values. Invent a new currency, then convert it to 
> US
> $$$. For this to work, you need to know the percentage of the shipping 
> cost
> in the total value of the product for the given supplier. Then reflect 
> that
> percentage in the currency conversion. We are importing our products from
> Poland and airfreight cost constitutes a big part of the total cost of the
> product.
>
>
> "Tim" wrote:
>
>>    After recently finding out that the Shipping Field does absolutely 
>> nothing
>> in a purchase order reguarding Costs of Goods, or anything for that 
>> matter, I
>> decided that "oh, I can just use a Formula to change the costs in my 
>> purchase
>> order to reflect my shipping costs!" So, after going through and adding 
>> up
>> all the quantities of items recieved, and dividing it by my shipping 
>> cost, I
>> got a cost of $1.40/item. No problem right? Wrong. The Formula will in no 
>> way
>> reflect a NEWLY updated cost. And I cannot use Formulas after the 
>> purchase
>> order is committed!!!!! >:O
>>
>>    FOR EXAMPLE: I have item 123. Item 123 used to cost me $3, it now 
>> costs
>> me $5. So, in a purchase order, I enter item 123, and put in a new cost 
>> of
>> $5. NOW, when I go to use the FORMULA to add in shipping, it only has 
>> options
>> that reguard to the OLD costs and other things that dont do crap for me. 
>> So
>> if I say, "Add 1$ for each item cost, my ending cost becomes $4 because 
>> the
>> purchase order Formula does not take the NEW cost of $5. The only way the
>> Formula will use the UPDATED $5 cost is to COMMIT the purchase order 
>> which
>> then renders the Formula field useless because you then cannot click on 
>> it to
>> edit the costs.
>>
>>     I must say, shipping is a huge part of our end-of-the-day cost, and
>> there is absolutely no way to make this work easily for us. Problems like
>> this keep popping up in RMS and my Boss' are getting very frustrated with
>> this system. 


0
1/3/2008 9:53:46 PM
Reply:

Similar Artilces:

lock formulas
Is there a way to lock the formulas on a series of linked excel spreadsheets so the formulas can't accidentally be deleted? I have it set up so that 4 identical spreadsheets with 12 tabs feed into one other spreadsheet and want to lock all the formulas. Is there an easy way to do this? Thanks! Dooma You can select which cells to lock and then enable worksheet protection. By default all cells on a sheet are locked when sheet protection is enabled. Hit CRTL + A(twice for 2003 users) to select all cells. Format>Cells>Protection. Uncheck "locked" and OK out. Now s...

IIF formula
I have a formula in a query which works fine until I tried to test it by deleting values in the form. Instead of nothing showing up, like how the formula should work, it showed ",X" The formula Other Fills: IIf([Other]=0,Null,[Other] & "X" & [Type]) & IIf([Other 2]=0,Null,"," & [Other 2] & "X" & [Type 2]) Example: [Other] = 2 [Type] = 4 [Other 2] = 3 [Type 2] = 5 Should look like 2X4,3X5 When [Other 2] and [Type 2] values are deleted it should look like 2X4 instead of 2X4,X The default value for the [Other] and [Other 2]...

Formula ErrorHi
...

Purchase Price Variance
We have a large PPV and I think it has to do with the fact that we went from using Standard Cost to Current Cost. Is it possible that items on older purchase orders that were created when we were using std cost are cosing these PPV's? If so, where do I update in the POP10310 to have the items use currect cost (FIFO Perpetual)? Thank you PPV accrued when you work on stadard costing and your purchase price is different. Purcahse order price is not having any impact on costing. However, if your item receipt price and invoice price are different and you did not choose the option to re...

need help with formula #2
how to write a formula? if cell =>24 multiple by 22. if cell is <24, multiply 24 by 22, multiply difference by 33 add together -- Kerri Where do I find the difference "Kerri Olsen" wrote: > how to write a formula? > if cell =>24 multiple by 22. if cell is <24, multiply 24 by 22, multiply > difference by 33 add together > -- > Kerri See Help for "IF worksheet function" I can't be more specific without a better description of the alternative calculations. Jerry "Kerri Olsen" wrote: > how to write a formula? > if cell ...

Need help w/ formula
Is there a formula that will change another cells information w/o having a formula in that particular cell. For instance, the IF(N5=Removed" then J5=0) without putting a formula in j5? No. A cell formula cannot change another cell. You can write code to react to value changes in cell N5 and the *code* can change the value in J5 "Todd Nelson" wrote: > Is there a formula that will change another cells information w/o having a > formula in that particular cell. For instance, the IF(N5=Removed" then J5=0) > without putting a formula in j5? No, you cannot ...

Need Help creating a formula****PLEASE
I am a manager in a big office where agents log into their phones to get calls. We keep track of how many minutes an agent is "logged off" of their phone system. I would like to be able to have a formula to use in Excel to speed up the calculations for this info. Here is what the info looks like that I am given and what I need to calculate: Sunday, December 03, 2006 Agent Login Time Logout Time # of minutes logged out Doe, Jane 6:58am 8:51am (need to find # minutes from 9:39am-8:51am) Doe, Jane 9:39am ...

Formula Help 02-02-10
How do I enter a formula to look at a number and based on calculation round up or down to nearest hundreth? For example 421 would round down to 400 while 573 would round up to 600. try =round(a1,-2) where A1 has the number you want to round to the 100's "Jackie Morin" wrote: > How do I enter a formula to look at a number and based on calculation round > up or down to nearest hundreth? For example 421 would round down to 400 while > 573 would round up to 600. You could look up ROUND in Excel help. Try =ROUND(A2,-2) -- David Biddulph Jackie Morin wr...

Purchase Receipt posting to closed period
We have a problem with the invoice matching activity in the system in regards to closed G/L periods. For example: - The receiving is done in period 2 (February) - Two weeks later the supplier invoice comes in with a different cost, it is now period 3 (March). Period 2 is now closed The system will flag these transactions and prevent the posting because the period is closed but will "place" them in the master posting with the date from the previous period. So it is smart enough to identify that the period is closed but dumb enough to generate the transactions wi...

Excel not pasting the formula or reference
I am trying to take a worksheet "apple" and copy it to a new workbook "banana". It works for another worksheet but not this one. I am highlighting the whole worksheet and copying it and then opening the workbook sheet and clicking the corner box and selecting paste. When it works correctly the cell would look like this: cell from "apple" ='sheet1'!A2 then when it is pasted over to "banana" ='C:\Documents and Settings\Desktop\PERSONAL\[apple.xls]Sheet1'!A2 now when i paste it over it just pastes what the cell displays which...

Formula for > but <
For excel sheet, is there any other way than the formula I put in cel B1 as: =If(AND(A1>100,A1<200),"Yes","No") i.e. I want to detect the value in A1 if it is >100 but <200 -- Message posted from http://www.ExcelForum.com i what's wrong with this kind of formula. Not really a beter way available. You may try =If((A1>100)*(A1<200),"Yes","No") >-----Original Message----- >For excel sheet, is there any other way than the formula I put in cell >B1 as: >=If(AND(A1>100,A1<200),"Yes","No") >i....

VB6 formula not working in excel
Hi, I have a few formulas embedded in my vb6 code which calculate and enter the results in the excel sheet. These formulas work for me and everyone at my workplace. But for some reason these formulas have recently stopped working for some of our model users in other countries. I noted that the problem is only with the formulas that are calculating the percentage, the rest of the formulas are working on the sheet. I also wanted to ask, is there a difference in using N/A and N?A in VB6? I should probably ask this question in the VB forum, but if anyone knows, please advise. Thanks, ...

Inserting rows into sheet that contains a formula
I have a typical financial sheet where the columns sum (sum(a5:a15)) and the rows sum (sum(a5:h5)). The summation columns are locked and the sheet protected. Is there a technique whereby I can have the user insert a row which will keep the formats and row sum as well as changing the formula in the column sums? Thanks. Rick Hi Rick, The following method is not foolproof but generally it works OK. Insert a blank row between the bottom of data to be summed and the row with the sum formulas. Include the blank row in the sum formula. Protect the blank row along with the formulas and what...

Inventory Site/LocationCode Order Lines on MSCRM1.2 to GPS8.0
We are currently running MSCRM 1.2 and GPS 8.0, with the MS Integration between them. Our Sales force uses various Inventory locations when placing orders within our GPS system. Because of business reasons we can't consolidate into a single Inventory Location as the Integration documentation suggests. The documentation allow says that it only supports the use of one Inventory location, which you set in the "Settings and Administration" tool. It appears that you can modify the BizTalk Mapping for the SalesOrderDetail channel to route a value from a custom field in MSCRM...

Formula Help
I have first names in one column and last names in a second column and I need to combine them in one column for an import. The formula I usually use is =A1&" "&B1 but for some reason it doesn't want to work for this spreadsheet. It's Office 2007 and I've saved the file as a regular worksheet. Any ideas? Never mind, it's just something with this particular spreadsheet. I copied and pasted the columns into a new one and it worked. "Brittany" wrote: > I have first names in one column and last names in a second column and I need >...

cell formulas #2
I am creating a spreadsheet for my acount payables and recievables. I want to designate a cell to keep a total. I need one cell whenever I add a figure it add to the total and another cell whenever I subtract a figure it will subtract from that same cell. I don't know if I understand exactly what you're asking. Might this be it? Enter this is B1: =SUM(A1:A100,C1,-D1) Where your total cell (B1), which is adding Column A, will increase by the number you enter in C1, and decrease by the number you enter in D1. -- HTH, RD -----------------------------------------------------------...

Year formula 11-30-09
I have a row of birth dates on a sheet, and need a formula to determine how many are over 60 years old. The cell format for the dates is 11/30/2009 Try =IF(DATEDIF(A1,TODAY(),"y")>=60,"60 or above","") OR year to year comparison =IF(YEAR(TODAY())-YEAR(A1)>=60,"60 or above","") -- Jacob "Robert" wrote: > I have a row of birth dates on a sheet, and need a formula to determine how > many are over 60 years old. > > The cell format for the dates is 11/30/2009 > Assume your dates are ...

Formula needed #2
Right all you clever people, I need a formula for my tax spreadsheet who can help me? I'm doing my mileage. I can claim back 40p a mile for the first 4,00 miles, then 25p a mile after that. I've been using this formula for the 40p a mile: =D75*0.4 (D75 bein the "total miles" cell) Fairly straightforward, but how do I make it so it adds 0.4 for th first 4,000 then 0.25 for everything after that. Is this possible? Thanks in advance -- dancingbear7 ----------------------------------------------------------------------- dancingbear72's Profile: http://www.excelforum.co...

How to succesfully sort column of formulas?
I am trying to sort a column of formulas that consist of cel references. Example: Column: Cell 1 holds: =D10 Cell 2 holds: =D11 Cell 3 holds: =D12 Data: D10=1 D11=2 D12=3 When I try to sort the column of formulas, the formulas change cel references to other cells and mess up the data. How can I sort a column of cells of which all of them contain formula and have the cells keep the references to the right place -- Message posted from http://www.ExcelForum.com Hi- If you write your formulae with absolute references rather tha relative references, you can sort them. Formulae should look ...

Help to create a formulae
I have 5 sheets, Heath, Gary, John and Fred. They go to their sheet Heading: Date Question1 Question2 Question3 They ent 3/3 5 6 1 They enter a date for each monday its filled out. then enter a number between 0 and 10. I have a mastersheet. It has the same heading as above but with the sheet name in column b What i would like is in the master sheet is if in column4 row1 cell a date. How can i change the date in col 4 row 1 that when it looks at the other sheets it will display only the figures for that date? Hope you understan...

Changing series formula
Hello, Im using the WorksheetFunction.Substitute code to change a series formula. But when a series sources data which is #NA, the code wont work, as the data is not visible on the chart. Anyway around this to change those series also? many thanks ahead. Ali - Convert the series type to xlArea first, then you'll be able to access the series. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Ali wrote: > Hello, > > Im using the WorksheetFunction.Substitute code to change a series formula. But when a series sources ...

formula only works when file is saved.
For some strange reason my formulas do not work until I save the sheet.I am using 2007 with a file that was in 2003. Never had a problem before. It is in compatibility mode. when I enter the data nothing happens in the cell where the result is suppose to go. If I close and save the result shows up. If I open the file back up and take out the data I put in the result is still there, where it should be a zero. Something is screwed up. Multiple people have access to this file. Sorry about the post . I found the problem. Somebody changed calculations to manual "bob" <...

Need help with Multi-workbook COUNTIF formula!!
I have monthly worksheets that use drop down boxes to pick criteria from lists. The linked cell outputs a number based on what the chosen criteria was in the drop down box. Currently I use this outputted number on a summary sheet with a COUNTIF formula ie COUNTIF(Sept!$R$3:$R$200,2) to summarize monthly info. I would like to generate yearly info instead without having to generate monthly totals first. Is there a formula that can use both ranges - Jan:Dec and $R$3:$R$200 - with the criteria of the linked cell ie. 2? ...

Formatting cells for big numbers and formulas ...without the formu
Hello, I am stuck with this: If I want cells to display like 20-digit numbers in numerical form (that is, not the xxx+EXX way), formatting them as a text is okay. But then I need to add formulas and want the results to be displayed. But as the cells are text formatted, the result in the cell is of course the text of the formula plus the number. So my question is - in a cell I want e.g. "12345678901234567890-sum(A1,B1)" and I want the cell to display the resulting number, not the formula itself. How do I achieve this? The maximum precision for a number is 15 digits according to...

Closing orders
After we receive and order, we create the invoice with the create invoice button, however the order is still there as "new" or "pending". Is there any way to "close" the order as having been received? (CRM is expecting that you would be integrated to an accounting system) You could write a manual workflow rule to set the order status to Submitted and order status reason to (whatever); you may be able to get the workflow to trigger on something more useful like creating an invoice, but I haven't tried that Peter "Biyoojok" <biyoojok@gmail...