Transferring Cost From One Sheet To Another
I am trying to figure out how to update cost from a manufacturers price sheet in excel. I have one worksheet that has the manufacturer's part number and my current cost. A=item number B= current cost. on the second sheet is the same setup but my price list has roughly 3500 rows and the vendor price sheet is 23000 rows. Is there a way to automate the cost replacement by comparing item number and if they are the same replacing cost on sheet one with cost on sheet two?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Transferring Data From One Sheet To Another
I wanted to : when i write data on one cell (sheet A), i want to see the same i wrote into another cell (sheet B), thts very simple, i know, but when its in blank (Sheet A), in sheet B appears a zero. I dont want to have zero, i want to remain blank as in sheet A. i did in sheet B "='sheet A'!A117". i am a noob in VBA, just beginning, and i am loving....
View Replies!
View Related
Transferring Data Onto Another Sheet
I'm entering some sales data on a worksheet named 'Sales'. Some items are sold on a 'sale or return' basis, and others are not. What I would like to do is to have all items that are sold on a sale or return basis copied automatically into another worksheet named 'Sale or Return', along with some other data related to that sale. The data transferred would have to be: Date, Agent, Item, Units, Date of return. I would like the data to be transfered into consecutive rows in the 'Sale or Return' sheet. After some Googling I'm vaguely aware this may have something to do with VLOOKUP. The challenge I'm having is that there may be one sale or return entry for every ten rows of data on 'Sales' yet I would like them to appear consecutively in 'Sale or Return'.
View Replies!
View Related
Calculating And Transferring Result To Other Sheet
I need to calculate data from one sheet 'Promotions' and paste/drag the formula onto the 'Summary Sheet'. The paste/drag is important to include all data. Trying to multiply [on sheet Promotions] 'NumberOfPromos' times (*) 'ProfitPerOffer' (using rows 1-8, let's say). Then paste/drag it in its place on the 'Summary Sheet' in the 'Total Receipts' column. I've been playing with =Promotions!$E$2:$E$8 * $J$2:$J$8 = 'Summary Sheet'!$F2 and things but cannot get it to work (I know I am WAY off base). I've been working with Access and trying to duplicate a simple database in Excel. Attaching a simple project example.
View Replies!
View Related
Simple Job Cost Sheet
The job cost controller is not exactly what I need, so I have been trying to create my own. So far my progress has got me to be able to separate each laborer for each job. Here is my query: I have a drop down menu that lists ten different tasks(i.e. carpentry, painting, demo, etc.) and I want each of the ten tasks to separate themselves. Is this a vlookup function? How do I make excel listen?
View Replies!
View Related
Transferring Data From Many Sheets To A Single Sheet
I'm loving these new formulas that i'm learning here and applying them to whatever sheets i come across to make my life easier at work... So now i've created another worksheet, this one is to help my colleague who wastes atleast an hour everyday to generate a consolidated report of our bank accounts... Now i've sorted out half of the report where he can give an overview of the position of cash flow on a daily basis. Theres however 1 more addition i wanted to make to make this workbook absolutely perfect! On sheet 2 (which is my consolidated report sheet) I want to add all the transactions that occur on a particular date from all the different bank accounts (Each account having a seperate worksheet) I'm not sure if advanced filters can achieve this or not or even easier/short macros... Any tips/hints as to how i can achieve this would be awsome.. I've attached a sample workbook which has sheet1 for a bank account and sheet2 for report generation... I want to add all the rows having "Clearing date" (column A) in Sheet1 to report date in Sheet2 ..And this for all the account sheets i can add...
View Replies!
View Related
Transferring(Transposing) Data To A Finished Sheet
I am trying to find an automatic way to create a finished report format that draws information from a different sheet within the same workbook. The trouble I'm running into is: The raw data consists of some lines without any data, and some lines with data. The purpose of the final report is to display only those rows with data in them. I have a sample spreadsheet attached, with the first sheet containing raw data, and the second containing a rough Idea of what I'd like the final infomation to look like. Somehow this seems like it should be really simple, maybe doable with an if statement, but I can't figure out how to get it to just ignore the empty rows without skipping a row in the final doc.
View Replies!
View Related
Userform Combobox Data Barely Transferring To Assigned Sheet
I am writing a UserForm and only one of my comboboxes is correctly outputting to the assigned worksheet. All of the textboxes, radio buttons, and checkboxes are working properly. It is my first time designing a UserForm. Attached is the code - maybe someone can see the error that is allowing only cboq4d to be transferred to the worksheet (named DataBase). Private Sub cmdClear_Click() Call UserForm_Initialize End Sub Private Sub cmdOK_Click() Call UserForm_Initialize ActiveWorkbook.Sheets("DataBase").Activate Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select................
View Replies!
View Related
Transferring And Looking Up
i have attached a sheet to show what i want to do in cell f13 i want it to return the full name and number dependant on what the initials are in c3 ie in this example it is dd which should then look up e3:e7 and then return the answers in f3:g7 so in f13 it would return donald duck 111 however there is a problem in the fact there may be a factor in the seperator ie in cell c3 it is / but in c4 it is -
View Replies!
View Related
Transferring Macros ...
I have a macro that I use on this computer, and I want to put it on my coworkers computer...I am having trouble...I have the vba code in a text file, I emailed it to her, and then put the vba editor on HER computer, picked "insert new module" and then pasted it...but....no go....
View Replies!
View Related
Transferring Data To Another Worksheet
I have somewhat of a complex issue (at least i view it as one). I have included a sample Excel Doc. I want to be able to enter data into the spreadsheet titles LIST as i receive it. i receive invoices and checks and i keep a running tally. but i am trying to avoid re-entering the same data into the BREAKDOWN tab. but i need the data to go to the proper place in BREAKDOWN. The problem is that i have no idea how big the list will be and how many invoices i will be receiving. so i think new lines will needed to be inserted in BREAKDOWN when i add to the running total in LIST.
View Replies!
View Related
Transferring Updated Data
I am trying to get a macro to update information from attached sheet GTS807 to the Stock sheet. The code in Module 26 works only for the first line and will be very long if I repeat it for the 20 lines of the input sheet! Basically we need to copy the new stock balance from GTS807 column AA13 to AA32 to the individual corresponding product name on the stock sheet (Column E)!
View Replies!
View Related
Calculating Cost Per Second
I'm trying to make a worksheet where I can calculate the cost of a mobile postpaid subscription. It is charged per minute and the cost differs depending on which of the 2 available networks the customer is calling to. The first 20 minutes are free, not depending on network. Edit: Charges to network A is 1,79,- per minute after the first 20 minutes are spent. Charges to network B is 2,29,- per minute after the first 20 minutes are spent. To sum up: 1. The customer makes a call. 2. If there there are available free minutes, these should be spent first. 3. The customer is charged per minute, depending on network called.
View Replies!
View Related
Least Cost Routing
im working on a VoIP company, and having a hard time to figure out a result on a little time. the problem is finding a "least cost routing" wherein you will find the cheaper to higher price, and putting the results on a designated columns: I am including an image, sorry if the image seems to be heavy and large: as you can see: Column C contains the sale rate for the client, while Columns D to I, are the buy rates from our source carriers. While the columns K to P, are the columns where should one source carrier should be fitted base on the buy rates, that is best fitted to the sale rate. Red marks=are not suitable or non-profitable buy rate for the sale rate. Sorry for the bad english, but i hope you get what i mean. this is only a sample from our Internal data.
View Replies!
View Related
Calculating Cost
Problem - billing spreadsheet for prisoner fee. 1 - 8 hrs = $55 9 - 24 hrs = $55 + $65 or $120 Anything over 24 hrs - $65 for each additional (24 hrs) ($185) So if you were locked up for 6 hrs it is $55. If you were locked up for 18 hrs it is $120. If you were locked up for 28 hrs it is $185. And if you were locked up for 49 hrs it is $250. Cell F5 contains number of hours locked up - I would like cell I5 to calculate the cost of the stay. I am proud of myself for figuring out the date and time subtraction - but this part has me stumped.
View Replies!
View Related
Transferring Text Boxes To Cells
I want to transfer the contents of around 20 textboxes to 20 different cells. Allthough I have seen many code examples for doing this with a single text box e.g. Sub getext() Dim s As String ActiveSheet.Shapes("Text Box 1").Select s = Selection.Characters.Text Range("A100").Value = s End Sub I can't seem to get it to loop through 20 boxes automatically using a For next loop for instance. Also I would like it to skip any empty text boxes it finds. From what I've seen I don't think it would take too much code but I'm afraid I'm a complete beginner with macro's and completely stuck.
View Replies!
View Related
Transferring Information From A Seperate File.
I'll get straight to the point. I have a file with an (.ASC) extension that has information that I need in my workbook. The first problem is that in the .ASC file ... a) there are french characters that don't translate into excel when I open it up b) the date format is dd/mm/yy and excel doesn't seem to like this and only converts it as mm/dd/yy for those it can and the rest it leaves as text. When I manual open the file problem b) disappears but if I use a macro problem b) pops up. Right now I'm using this code to open the file
View Replies!
View Related
Transferring Data/Auto Sort
I am trying to combine rows of data, each 4 cells long from multiple worksheets into one master sheet. I need to know if there is an easy way to do this and also I would like to setup an Auto Sort if possible to automatically sort the rows by date. The data that will be transferred is: Date, Amount, Description, and Company Billed. I have my workbook organized by a Vendor per worksheet and would like to have the data I enter inside the vendor worksheet to automatically transfer to the master sheet and sort by date.
View Replies!
View Related
Color Formatting & Transferring
Is it possible to take the color generated from conditional formatting in a cell on sheet_1 and have that color value be painted or transferred into a cell on another (sheet_2) without writing VBA/macro code? For example: Cell B32 on Sheet_1 has conditional formatting that when TRUE turns the background cell color to GREEN. I also have it formatted to turn RED when another condition is TRUE. I want cell C1 on Sheet_2 to call whatever color value is in Sheet_1!B32 and show it on the other page.
View Replies!
View Related
Transferring Data Betwween Two Worksheets
I have created a small input area which contains columns for the user to input a new piece of stock. When the data is in i want the user to click submit and for it to transfer into the set places on the stock page! I know this is possible as i have done it before but have been hitting a brick wall with it for a month now! I just cant remeber.
View Replies!
View Related
Transferring Data To Right Cell In Calender
I have made a cash register in excel with a cell that counts the total of all cash coming in per day whenever you push the button with "payed". I have another sheet where there is a column with all the days of the year. What is the correct syntaxis for transferring the total cash to the cell next to the right date in the other sheet every time i push the "payed" button (am I making any sense...?). And is there a way to reset the total amount back to zero automatically whenever the day changes?
View Replies!
View Related
Transferring Data To Text File
I would like to transfer the data in my Excel Sheet to a txt file while still keeping the widths the same as they were in the Excel Sheet. A portion of what I want to convert is in the the attached Excel File. The txt file is what I would like it to be converted to. When I cut and paste, it adds a tab space between each column which I dont want.
View Replies!
View Related
Cost Vs Units Graph
I'm wondering how to display the following cost model on a graph (this is a piecewise-linear cost model for some production company): Cost per unit for 0-10 units: $1 Cost per unit for 11-20 units: $3 Cost per unit for 21-30 units: $5 Cost per unit for 31-40 units: $8 Fixed cost: $100 (this is regardless of the number of units produced) Basically I'm looking to construct a cost vs units graph.
View Replies!
View Related
Cost Based Mark Up
set up a cost based markup spreadsheet? I need to be able to mark up values from 0.01-9.99 by 20% of their value, and 10.00 and above by 10% of their value. I only know how to mark up values by a single percentage.
View Replies!
View Related
Calculate X Percentage Of Cost
I am trying to create a simple formula to extract cost from a total that includes both cost and and a percentage for maintanance. Assume $100, 10% of which is maintenance the remainder is cost. If I just subtract 10% from $100 I get $90, however 10% of $90 is $9 which equals $99.
View Replies!
View Related
Cost Average Effect
Example number 1: You invested 10.000 Dollar in the year 1985 into a tempelton fund and waited 20 years. Result can be seen at many forums and stock plattforms Example number 2: In 1985 you had no 10.000 Dollar, but only 100 Dollar a month. You invested this 100 Dollar each month for 20 years. How big will the difference be between example 1 and 2 ? *** Background. If a "share" will cost 100 Dollar, you will get 1 share for 100 Dollar. Next month share is down to 50 Dollar, you will get 2 shares for your monthly 100 Dollar. Next month share is up at 200 Dollar, you will get only a half share for your 100 Dollar. Its called cost average effect. Tool should allow "play arround", to simulate various scenarios, talking into account growth rate, time period, monthly investment, fluctuation of a theoretical share. Idealy we would love to " import" e.g. famous fund history (eg. Tempelton, Pioneer) etc. to see how investment would have developed when a steady monthly payment would have been made.
View Replies!
View Related
Time And Cost Calculations ..
I am compiling a simple worksheet that will keep an ongoing track of labour costs in a production environment. The objective is to end up with a labor cost "per unit" for packing punnets of soft fruit. The source data I have is; 1 - Start time and end time of the job 2 - Any breaks taken during the job 3 - The number of staff it took to do it 4 - The status of the staff (Supervisor, temp, etc) and their hourly pay rates 5 - The number of punnets packed. With all of the above it should be a relatively simple exercise to calculate the cost per unit (and with a calculator is!). My problem appears to be that I am not formatting something correctly, because when I try to calculate the costs for the employees' my costs are obviously wrong. I have attached my early draft for reference, in the example shown I am showing that a supervisor earning £7.50 an hour worked for 1 hour 15 mins at a cost of £0.39
View Replies!
View Related
Getting Info From A List And Input Then Transferring Data
a user inputs the drawing number and revision in a box on the first sheet in the workbook. Once that is done, the user then selects the type of drawing from the drop-down menu, once "Submit" is clicked it takes that info and puts the information in the next available cell for the other worksheets (if that worksheet calls out for that certain part). I've been having trouble trying to figure out how to be able to accomplish this task. If you have any ideas, that'd be great. I've attached a basic worksheet I drew up to give you a better idea of what I'm looking to do. Forgot to mention that there are totals involved! The "destination" worksheets have set totals for each drawing type, so what also has to be taken into consideration is that the specific drawing needs to have the correct total beside it. I created a "Totals" worksheet to show what drawings are what totals for each cell. Realizing this may always change or be updated, I created a seperate worksheet to pull all the numbers off of. I thought this might be easier rather than going in and changing the coding each time.
View Replies!
View Related
Transferring Print Format Info Via VBA
I am using VBA to copy and paste multiple sheets from one workbook to another. The workbook where the sheets are being pasted has no prior formatting. I want to transfer the *print* formats from the first to the second sheet via VBA. One key thing to note is that the print formats are constantly being changed in the first workbook. So I think I need code to "grab" the formats, then transfer them to the second workbook.
View Replies!
View Related
Transferring An Image From Webcam To Hard Drive
Need macro that automate our QC department. What the macro does is it looks up the item number based on the barcode that the scanner reads and brings up the item number, the item description, total of units in a case, how much a single box of the item will weigh and how many units in the inner box may be sold at one time. The trouble is that not all items have an image available so that the person who is doing the checking knows what the item looks like. There is a Logitech webcam setup to take pictures of the items. The following code allows us to import the image of the item into excel in a given cell:
View Replies!
View Related
Switching Two Workbooks And Transferring Data With Variables
I have create a nice invoice with macros to random insert pages and summations etc. Works fine, but to complete it I want to go one step further and there ìt got out of hand. I want to create the following, but I can not get al this combined in two Macros. Basis: Workbook 1 = Invoice, Workbook 2 = Invoicelist Required actions Macro1: 1. Starting point is that Workbook “Invoice” is open. 2. The user activates Macro1. 3. Check if Workbook “Invoicelist” is active. 4A. If YES then continue, 4B. If NOT then reply with some sort of Alertbox “Invoicelist not active! Click OK to open (needs to open Workbook “Invoicelist” or Cancel to stop (needs to stop Macro). 5. Workbook 2 “Invoicelist” is now open. 6. Find the last invoicenumber in the list. Add “1” and put the new number to the list (to prevent using double numbers). Then I want to take this number to the “Invoice’ workbook, but that can be done by copy and paste. Now I get really stuck because I want to use variables instead of multiple copy paste actions. Required actions Macro2: 7. We are in Workbook “Invoice”, the user activates Macro2. 8. Same check if Workbook “Invoicelist” is active as in Macro1. Several data from “Invoice” has te be transfered to “Invoicelist”. And to be safe, this does not have to be the last cell if the user “forgot” to store date for a previous invoice! 9. Store data from sevaral cells in variables, including invoicenumber! 10. Find invoicenumber in “Invoicelist”. 11. Paste data from variables into fields on same row as correct invoicenumber. 12. Save Workbook “Invoicelist”. 13. Go back to Workbook “Invoice”. The two problems I cannot fix is checking if Workbook is Active and store and use cell values with variables.
View Replies!
View Related
Transferring Files To A New Location Messes Up Formulas
I have a bunch of spreadsheets in one folder with several subfolders. On one spreadsheet, I have a bunch of formulas referencing another spreadsheet. This is an example of the formula: =C:UsersKatie DestktopMake Your Day Program8th Grade[Math 8,xls]Sheet1'!G2. I am building this program at home, but then when I take the Make Your Day Program folder and put it on the J drive at my school, the formulas still try to reference my desktop instead of changing the formula to J:Make Your Day Program8th Grade etc.. Why? It is a pain to go through all the formulas and change them to reference the j drive because there are a LOT, and I have already done that once. How can I ensure that when I transfer this program to the school drive, after the holidays, that the formulas will change to their new reference location?
View Replies!
View Related
Transferring Values From Different Sheets By Text Match
find attached sheet of example. In the attachment the supplier sheet is the sheet we get from our suppliers and it shows us the products they have in stock along with the prices and other information. This sheet has over 900 products on it. It is updated daily which we download and then paste it over the existing data. The Deviltronics sheet is the products we have added onto our website. It has a code in column E which tells us if the products on the supplier sheet is in stock or out of stock. (this was done by the help of someone here) Both sheets have been cut down considerably so that we can add them as an attachment on here. So now I am going to tell you what I am trying to do! What we need is a code that will automatically copy the trade price, suggested retail price and the shipping cost from the supplier sheet and insert it into the Deviltronics sheet next to the relevant product (say in columns F, G and H). This done by matching the product code part numbers in both sheets as done with the existing code on the Deviltronics sheet (column E).
View Replies!
View Related
Calculating Cost Based On Several Factors
i. I currently have a spreadsheet which is used to forecast resource cost for a project. The forecasted cost is calculated on a few factors - rate, allocation, contract start and end date, and expected days worked per month. One of the mods actually helped me out with this a few weeks ago. I now have been told that there is a possibility that certain resource costs may change in the new year and that will need to be reflected in the sheet whilst keeping the historic information. For example - XXX has a rate of £200 p/d, allocation is 1, working 18.83 days p/m and is working from 01/01/09 to 01/06/09. The current formula will work out his cost per month until contract end. Now say his rate will be changed to £150 p/d from the 01/03 and all other info remains the same, I need the sheet to calculate his revised cost from 01/03 onwards and not change the calculation previous to that month. Now Ive actually managed to figure that part out myself by adding in two columns (over-ride rate and over-ride date) using a nested IF statement. The only problem is that if the new rate starts mid month then it will still calcuate the original amount for the full month and the revised amount from the next month. Edit - Also, could someone advise as to how do I remove my old attachments as I have almost used up my allocation.
View Replies!
View Related
Cost Price Vs Quantity Discount
I'm trying to work out how to get a spreadsheet to calculate when it works out cheaper to buy more of something, due to quantity price breaks, than less... e.g. 1-10 are £12.50 each, but 10-20 are £10 each and 20-30 are £8.75 each.. so it's as cheap to buy 10 (10x10=100) as it is 8 (8x12.50=100) and cheaper to but 10 (100) than it is to buy 9 (112.50) than 10... but there are similar price breaks at 20, 50 +
View Replies!
View Related
Way To Check Whether Any Cost Centre Has Been Missed Out ...
I attached a workbook and it shows data in row 2 - 960. and in cells BA1574 -BM1604 are the summary by using 'sum if' function. As you can see, row 160 countercheck the results and it shows differences. So my questions are 1) any formula that i can use to ensure all cost centres are taken up in Col AZ1574 - AZ1604? I am sure that i have missed something out. 2) My goal is to summarise the total of each cost centre. Is there other options to reach this goal apart from using 'sum if' function? I would like to learn something new.
View Replies!
View Related
Manufacturing To Retail Cost Calculator
I am trying to make an EASY Manufacturing Cost to Retail pricing calculator. This calculator would have ability to include cost of goods, labor, markup etc of components manufactured and sold as retail products, example: small bookshelves versus large bookshelves or cabinets all have different materials (wood types, stain etc) in determining the final retail product costs that would reflect time of labor hours involved in producing to determine final retail costs. Should be simple in Excel 2000 (my version). I have the basic template created and have used Data Validation Drop Down Lists and utilized LOOKUP function. While my knowledge is limited in Excel, I am frustrated as how the LOOKUP function works, I can only get it to work where in the formula, the costs per unit are input manually, whereas I would prefer to have the data input automatically from columns of calculated wholesale cost plus markup per square foot data, ie: =lookup(A1, X1:X30, Y1:Y30) but get errors when doing this. Instead this works: =lookup(A1,{"pine", "oak", "birch"},{"3.99","7.87","5.15"}) and using this cell (A1) in my calculations for including the square foot costs in the final retail calculations. While this seems to work, it is not easily modifiable as costs change rapidly and would like to easily input the cost per square foot of the different woods in their own cells rather than in the formula calculation of the lookup. Hope that makes sense..... any suggestions? perhaps lookup is not the best function for what I am attempting? - sample file is attached, light green are notes of where my data is located.
View Replies!
View Related
ISDN Call Cost Formula
I have an Excel workbook with 2 worksheets, labelled as "codes" and "data" The "codes" worksheet contains three columns of data. Column A shows Country names Column B shows Country dialling codes Column C shows Cost per minute to the country ...
View Replies!
View Related
>>> Finding Lowest Cost/Vendor
I need to find the lowest price, mark it up and designate which vendor it’s from so the CSR knows which one to quote back to the customer. Besides going through it line by line, is there a way to do this? It seems like there could be a formula where it pulls the lowest of the 3 prices and vendor name into a new “price” and “vendor” columns and then I can go through and mark it up. Here’s a small sample of what the sheet looks like: Item #Desc V1 V1 CostV2V2 CostV3V3 Cost 123456brush Advance 2.56Weiler5.65Osborn3.25
View Replies!
View Related
Drop Down List Isn't Transferring In New Row
I have a spreadsheet that has 5 columns. The first row is the Header row, the second row is for the data input, here are the columns: Column 1: Company Name, I have a drop down list associated with this column. Column 2: Sales Person, I have a VLookup function to check for the company name in Column 1 and fill in with correct Sales Person. Column 3: Total Sale, Manual input. Column 4: Commission %, I have a VLookup function to check Company Name and fill in the correct Commission. Column 5: Commission, Basic function of Total Sale * Commission %. I have a macro created that when run it will take all the information from row 2 and move it to row 3 and at the same time inserting a blank row 2 with the functions for me to fill out new information. The problem that I am having are 2 things: 1: When running the macro the functions will get applied to the newly inserted row but the drop down list for the Company column doesn't. 2: I would like to be able to start typing in the company name i.e "F" and have it jump to the F's in the drop down list.
View Replies!
View Related
|
TRACKER
