Taking Multiple Data Block To Another Sheet
I need to use Excel 2007 in a stock sheet situation.
I have attached a picture of what the sheet will look like.
What I need is a formula that will take a value in column c and then add the value in colum a behind that. Preferably with a X or - between them ie.
If i took the first row and applied the formula it should kick out 5 x Jam
The main thing is it must output the answer to another file/page so that the main page stays the same. And if there is no value in Colum c it must ignore it.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Taking Data From A Cell
Is there a fomula that would take data from a cell. I am looking to take everything before and includuing the word LTD For example if a cell contained Joe Bloggs Ltd (Mr Jones) C/o USA I would like to be able to take "Joe Bloggs LTD"
View Replies!
View Related
Taking Data From Other Spreadsheets Using A Macro
I have a number of reports in excel (all in the same format) which I need to take certain data out of and store it into one main spreadsheet. Searching through the forums I found some code which I think will do this: Sub test() Dim myDir As String, fn As String, ws As Worksheet myDir = "C: est" fn = Dir(myDir & "*.xls") If fn = "" Then Exit Sub Do While fn "" Set ws = Workbooks.Open(myDir & fn).Sheets(1) ws.Range("a7", ws.Range("a" & Rows.Count).End(xlUp)).EntireRow.Copy ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp).Offset(1) Workbooks(fn).Close False fn = Dir Loop End Sub I have tried using this code, altering the Dir String to the folder the documents are in. However, when I run the code, nothing happens. I don't get any errors, and nothing appears in the spread sheet. To be honest, I am not totally familiar with macros in Excel. I can get the gist of what the code is doing having using VB before, but am not totally familiar with some of the functions being used here.
View Replies!
View Related
Data Entry Taking An Age!
However data now takes far too long to enter; for example if I type the number 9 into a cell it takes around 30 seconds to let me enter more data, it is as if it is working on some calculations. The VBA I'm using is as shown below.
View Replies!
View Related
Copy The Block Of Formulas And Formats Down The Sheet A Few Hundred Times
In my spreadsheet, I have approx. 300 'blocks' of data, one for each 'vendor'. I am tracking 5 stats for each vendor for 12 months. Each 'block' has 5 formulas WITH conditional formatting (only one condition used) for each month, so each 'block' has 60 individual cells with conditional formatting. Excel throws an error when I try to copy the block of formulas and formats down the sheet a few hundred times. I have figured out it isn't the formulas that is the problem. It must be some kind of limit Excel 2003 has for how many cells in a workbook can have conditional formatting.
View Replies!
View Related
Put Data In Column B As Common Row For Each Block
I have a matrix with info of persons of different cities in consecutive rows, where the city data is in column B. I would like to eliminate columnb B and divide all persons from any particular city with 2 new rows, their city name and below a field for every column data. *The number of employees for city would be variable. Original data (The columns used go from A to E) 1 City_1 Employee_1 Address_Employee_1 Other data_Employee_1 2 City_1 Employee_2 Address_Employee_2 Other data_Employee_2 3 City_1 Employee_3 Address_Employee_3 Other data_Employee_3 4 City_1 Employee_4 Address_Employee_4 Other data_Employee_4 5 City_2 Employee_1 Address_Employee_1 Other data_Employee_1 6 City_2 Employee_2 Address_Employee_2 Other data_Employee_2 ...
View Replies!
View Related
VLOOKUP For Multiple Data On 1 Sheet?
Can you use VLOOKUP or a similar formula to lookup and add (or even better average) multiple data from one sheet?. For example the source sheet holds certain codes for each of our agents and I need the total of one particular code for each agent and then eventually work out the average for the day. But as the source sheet can hold 30+ agents it would be a long and tedious job to do seperate Vlookup's (especially as we are backdating the last year!). I have attached a snapshot of the source data to try to explain what I need better. From that source data I need to get the total/average of all the 101 codes.
View Replies!
View Related
Pulling Data From Multiple Sheets Into 1 Sheet
I am creating a spreadsheet for mutiple clients, which has around 5 columns. This spreadsheet will have 7 sheets on it. 1 sheet for each client, and the 7th will be to display all of the information together. Basically I am looking to find out if this is possible? I will try and explain a bit more, as above is just beifly what I am loking for. I Have Client.xls On my Tabs I have Client1, Client2, Client3, Client4, Client5, Client6, All Clients In Each individual spreadsheet, I have the following Columns Incident Reference; Description; PMDB number Each Day data will be entered into each client sheet, and I would like this information to be populated into the All Clients Tab. I am not sure if this is possible, and if it is, would I then be able to put it into the All Clients tab, and have it auto sort by the incdient reference column? As this is an autogenerated reference for all of our clients.
View Replies!
View Related
Multiple Emails From Single Sheet Based On Data
I have an excel file having different columns. The test data is as under Email HQ City Product Value S@y.com XYZ BAC abc 10 S@y.com XYZ CAD bcd 20 S@y.com XYZ BAC abc 10 S@y.com XYZ CAD bcd 20 A@b.com XYZ BAC abc 10 A@b.com XYZ CAD bcd 20 A@b.com XYZ BAC abc 10 A@b.com XYZ CAD bcd 20 The above is sample data and the rows will change depending on data. Based on email id the range should be picked up and the email should be send as a attchment.
View Replies!
View Related
Copy Data From Multiple Sheets To Single Sheet
trying to copy data from multiple sheets to one single sheet. I am pretty sure this is possible. The problem I am running into is that the number of sheets at any given time is dynamic. The numbering of the sheets is from 000 to 999 (they must be a three-digit code). The other issue I am having is I only want to copy the rows in each sheet that have an "x" in column "A". Also, the row in which the first "x" occurs can differ from sheet to sheet. I have attached a copy of the spreadsheet that indicates how the spreadsheet is layed out.
View Replies!
View Related
Distribute Data In Single Sheet To Multiple Sheets
The worksheet is formatted as follows: It contains data in columns A-L. I want to breakdown the data further into sheets according to the data that is found in column G. As an example, say I have 1,000 rows of data and in column G there are a number of different branch numbers in each row. So there may be 250 rows with branch 450, another 250 rows with branch 360, another 200 rows with branch 777, and finally 200 rows with branch 888. In this case I want to create 4 new sheets (450, 360, 777, and 888) and copy only the data in the main sheet that corresponds to same branch. Actions would be as follows: copy 250 rows to tab "450" copy 250 rows to tab "360" copy 200 rows to tab "777" copy 300 rows to tab "888" Any comments are much appreciate. I have attached a data sample along with desired output, if needed. One note: the data comes in each day as one single sheet of data and the number of branches varies day-to-day, as such, code would need to create new sheets for each branch found.
View Replies!
View Related
Copy Data From Multiple Sheets To One Master Sheet
I have been trying to make a code that copies all the data in A2:K50 in all the sheets (about 32 of them right now) and paste that information in 1 sheet (Master List). The code does work but for some sheets it only copies the first 2 or 3 records. Also, this code puts the name of the sheet the data came from but for the first 2 records in puts in the Master List its pasting the wrong Sheet name. Sub SummurizeSheets() Dim ws As Worksheet Dim lastRng As Range Application.ScreenUpdating = False Sheets("Master List").Activate
View Replies!
View Related
Pulling Data From Multiple Worksheets Onto Master Sheet
I have an identical worksheet for all the days of the month - i.e. the May workbook has 30 identical worksheets. I am wanting to pull only certain rows from each worksheet onto a master. Something like - copy row from all worksheets where that rows cell "D" has any value other than " ". I have used the following macro to pull all the data from every sheet onto a master - but i don't want all the data - just the qualifying data. Sub Combine() Dim J As Integer On Error Resume Next Sheets(1).Select Worksheets.Add ' add a sheet in first place Sheets(1).Name = "ALL" I am not sure what the range "A1" is - i am thinking i can modify it to only grab certain rows.
View Replies!
View Related
Multiple Timesheets, Auto Fill From Data Sheet
I have been tasked with creating a workbook that will take a time log file (excel) from an electronic time clock and import the dates/times into individual employee timesheets. The timesheet layout has already been provided to me and cannot change. I’m really struggling here and would appreciate any help. Please reference the attached workbook for the issues described below: 1) Sheet "1_attlog" is the log that is imported from the electronic time clock. 2) Sheet "Sorting & Filtering" is what I have developed so far that manipulates the data from sheet "1_attlog" into something that can be easily understood, it also matches employee #'s to a name, and allows for date ranges to be set that copies over to the individual timesheets. Please do not change any formulas on this page if possible. 3) Sheets "#2 Dan through #10 Blank" are all individual employee timesheets that I need to import the data from "Sorting & Filtering" and populate the "Time In" (column D) and "Time Out" (column G) based on matching the data from "Sorting & Filtering" sheet, "Time Clcok ID" (column D) "Date" (column E), "Time In" (column F), "Time Out" (column G), "Emp Name" (column I). The dates on the individual timesheets have been linked to the "Date Range" (cels F2 & F3) on the "Sorting & Filtering" sheet, please keep this link intact. 4) The individual timesheets already have formulas inplace to calculate the hours worked after the "Time In" and "Time Out" data has been imported.
View Replies!
View Related
Copy Data From Single Sheet To Multiple Sheets
I recieve a daily spreadsheet with ~25,000 rows of data and is 4 columns wide. Each day I need to break the data equally up into 19 or 20 different sheets within the same workbook. This is very time consuming as I need to copy the first ~1,000 rows and paste it into the first sheet, then I need to take the next ~1,000 rows and paste it into the second sheet, and so on. I have attached a small example with desired output. In my example, the raw data file contains 19 records. For these 19 records, I need to distribute the records evenly between 5 individual sheets. In this case, each sheet will contain 4 records or less.
View Replies!
View Related
Copy Data From Multiple Sheets (different Columns) Into A Main Sheet
I have multiple sheets Names(Sheet1-Sheet5). Im trying to compile them all on the same sheet (Main Sheet). But each sheet is NOT the same. I need to choose what Columns needs to be copied (I can use one macro for each tab if needed) For example I need Sheet1, Columns (A,C,E,G,S) copy that and then go to (Main Sheet) and paste in the next blank line (column A) Seems like it does not know where to paste. Here is one I have tried ....
View Replies!
View Related
Simultaneously Copying Data From Multiple Sheets To A Single Sheet
I have a workbook containing about 20 sheets. I need to find a way of pasting all the numbers in the A column in each sheet (sheets 1-20) to the last sheet so I get a great big list of all the numbers in the 20 sheets. How would I go about doing this? Right now I'm cutting and pasting from sheet 1 to my last sheet, then from sheet 2 to my last sheet, then from sheet 3 to my last sheet... Is there a quicker way? The ranges in each of the sheets start at A2 and go down a few cells. Sheet 4 might have 4 numbers in the A column, sheet 12 might have 47, sheet 17 might have 8 and so on. How would I go about getting all the A columns in the sheets to the last sheet? I'll throw up a few screenshots if the problem is unclear
View Replies!
View Related
Macro- Multiple Worksheets Which Has Data (similar To Sheet "Homewares")
i have multiple worksheets which has data (similar to sheet "Homewares") which need to be summarised in the summary sheet, i need macro that will grap all the info from all worksheets and pull data into sheet summary. The headings in data sheets will always be in Col A, i need to populate the summary sheet with this data please see below example Homewares AB1 2 3CompanyHomewares Limited4 5Register NameInvestment Services Pty Ltd6 7CodePCABO1 8 9Sec TypeFPO10 11Option Expiry Date 12 13Total Holdings5,947,500.00 Excel tables to the web >> [url] Macro to return result as per below screenshot Summary ABCDEFG1 2 3 4 5 6CompanyRegister NameCodeSec TypeOption Expiry DateTotal Holdings 7Homewares LimitedInvestment Services Pty LtdPCABO1 FPO 5,947,500.00 8 9 10 11 Excel tables to the web >> [url]
View Replies!
View Related
Taking Numbers Out Of A Number
what I have is a spread sheet of over 3000 numbers. Now this is what the number looks like: 0000123456000 what I need is basically a formula or something to take out the zeros (They are not really zeros on the spreadsheet, its just an example to make it easier to display). And leave the six digits in the middle. Is this possible?
View Replies!
View Related
Positive Cells Taking A Zero Value
I have a code below to only allow a number to be entered into one of 2 cells. I'm trying to do conditional formatting based on these to cells, and the evidence is showing that even when I type a value into either I21 or I22, they are taking on a value of zero. I have conditional formatting stating that if I21>0, then do one thing. And in a separate cell, I have =I22>0, then do another thing. Neither works, and even using =I22<>0, then do formatting, and it doesn't work, telling me that the assumed value is always zero.
View Replies!
View Related
For Loop Taking Too Long
I've got a sheet which I need to analyse and split into several different sheets but the raw data format leaves a lot to be desired as there are blank rows inserted randomly between rows of data. I need the 'good' data to stay in the same order so I've written a macro to sort through the data and delete any blank rows leaving the good stuff behind. The problem is this takes ages as there can be up to 30000 rows that need to be checked and I need to do this 5-6 times a day. I just wondered weather there was a quicker way to do this? The code I've got is detailed below: Sub Prep2() 'Delete all blank data rows Dim Rows As Double Dim Rownum As Double Application.ScreenUpdating = False Rows = Selection.SpecialCells(xlLastCell).Row For Rownum = 2 To Rows If Cells(Rownum, 11) "" Then GoTo NxtRownum Else Cells(Rownum, 11).EntireRow.Delete shift:=xlUp Rows = Rows - 1 NxtRownum: Next Rownum Application.ScreenUpdating = True End Sub
View Replies!
View Related
Using Loops For Taking Averages
I have a column of 96 numbers (observations every 15 minutes for a 24 hour period), and I want to take the average of these numbers in groups of four (the hourly average). My data starts in cell A2 and goes to cell A97. My first batch of averages are labeled as follows: Range("A2").Select startCell = ActiveCell.AddressLocal ActiveCell.offset(3,0).select stopCell = ActiveCell.AddressLocal Now, clumsy programming aside, I just want to say something like this averageCell = AVERAGE(startCell:stopCell)
View Replies!
View Related
Taking Number And Turning It Into Time
I have created a simple spreadsheet to keep track of work hours. I simply enter in each days hours and then I get a total. I have each cell formatted for time (hh:mm). However it is annoying to have to type in the colon for each days time. Is there a way I can just type in the three digits '9,3,0' into the cell and have it come out as '9:30'?
View Replies!
View Related
Taking Matching Fields And Numbering Them
I have a column where a lot of the fields match. What I want to do is set up a formula where all of the matching fields are numbered (1,2,3,etc). For instance:....... I want the numbering to read the matching items and count them (not a sum but rather just a numbering/ordering column) This is what I would want it to look like:...........
View Replies!
View Related
Taking Percentage Of The Larger Of Two Cells
I hope my title was clear enough, but I’m not sure how to set this up. I have numbers in cells B4 and B13. I want to take the largest of these two numbers and multiply it by 0.85. I then want to take this number and compare it to a number in cell B23. If B23 is greater than the larger of B4 and B13 X 0.85, I want B33 to display “No” in a boldfaced red font. If B23 is less than the larger of B4 and B13 X 0.85, I want B33 to display “Yes” in a normal black font. This formula will be in cell B33.
View Replies!
View Related
Routine Taking Ten Minutes To Update
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Application.EnableEvents = False If Intersect(Target, Range("E:E")) Is Nothing And _ Intersect(Target, Range("H:J")) Is Nothing Then Exit Sub End If Is there a way to make this run a little slicker , as at the moment it is taking almost 10 mins to update RngDate is $L6:$FB1038
View Replies!
View Related
CALCULATION TAKING BUSINESS HOURS INTO CONSIDERATION
I need to do an hour calculation on two cells which have dates and times in both. the first cell is a call that we get from a customer and the second is the date and time in which that call is closed by us...meaning that call is complete. I need to calculate how much time in hours did it take us to complete that call for the customer. I need this calculation to respect our business hours of Monday to Friday 8am-5pm and closed on Saturdays and Sundays. here are some examples. from - 2/12/2004 13:00 (thursday) to - 2/13/2004 9:00 (friday) answer should be 5 hours from - 2/13/2004 14:00 (friday) to - 2/16/2004 10:00 (monday) answer should be 5 hours
View Replies!
View Related
Taking An Index Match Function One Step Further!
I'm matching ISO country codes to country names. This works great as long as the 'lookup_value' portion of the match fuction matches the 3 digit code on Sheet3. Is it possible to return my desired result without this matching perfectly. So a value in 'Sheet1 Column A' that begins A344xxxxxxx will return the same result in 'Sheet1 Column F' as if 'Column A' just had a 3 digit number.
View Replies!
View Related
Spreadsheet Taking A Long Time To Open
I have a couple shreadsheets I have created, one is for Purchase order's and has 4 different sheets. with one sheet holding addresses linked a drop down menu in the first sheet, all in the same workbook. Also there is a macro within this sheet to print to a specific printer, and a specific number of copies when CTRL p is pressed. The total file size is approx 9 mb. Opening this file takes approx 30 to 45 seconds. There are no links to any outside files, only links within the workbook itself. The second File I have recently started having problems with (Our Quotation log) is A workbook containing 3 sheets, 2 sheets independant and one sheet linked to the second that Summarizes the first and also has cels to enter information on the person the quotation has come from. I have this sheet formatted as a table so I can sort by name etc when doing followups. This workbook also takes approx 30 to 45 seconds to open, and on occasion longer. All workbooks are stored on my computer. The PO workbook has had the problem since I created it, although the Quotation log workbook has only had the issue this week.
View Replies!
View Related
Taking Area Codes Off Phone Numbers
I have a column full of 10 digit pone numbers. All different numbers and many different area codes (No dashes): Example: 3305555555 4583651586 4128563254 Is there a way to remove the three leading numbers from all of the numbers at once for the whole column?
View Replies!
View Related
Taking The Value Of A Range Of Cells From One Workbook And Place Them In Another
I've been trying to learn how to write code in VBA. I've been learning for about 2 weeks now (trying to learn it for work) and heres my problem. I have a macro that will create a pivot table from raw data that I input. From this pivot table, I want to go to the last row in the pivot table (the row that takes grand totals of each column of data). I want to write a macro that will take those values (located in a workbook called AR age sorting) and place them in cells in another workbook (called AR aging analysis). Basically cells D6-D9 in workbook "AR aging analysis" should equal the values of the last row in my pivot table in workbook "AR age sorting" (last cell with data in columns J-M). I thought this would be easy but I've run into some issues. Heres the code I use: Sub aging_summary() Dim Sheet1 As Worksheet Dim Sheet2 As Worksheet Set Sheet1 = Sheets("AR age sorting") Set Sheet2 = Sheets("AR aging analysis") For n = 10 To 13 For M = 6 To 9 Sheet2.Range(Cells(M, 4), Cells(M, 4)). _ Value = Sheet1.Range(Cells(n, 1), Cells(n, 1)). _ End(x1Down).Value Next M Next n End Sub I don't want it to copy and paste the values since I just need the value, not all the extra formatting and what not that is incorporated in the pivot table. When I try to run this I get an error message, saying that there is an application-defined or user-defined error.
View Replies!
View Related
Combine Workbooks With Multiple Sheets Into 1 Multiple Sheet Workbook
I have about 20 workbooks with different file names for different projects all saved in the same folder. Each workbook has about 10 worksheets and each worksheet is named in a similar fashion in each of the 20 workbooks (eg. revenue, cost, variance etc.). I want to pull out a worksheet named ' forecast' from each workbook into a master workbook so that the master workbook would contain the 20 forecast worksheets.
View Replies!
View Related
Lookup Single Value In One Sheet, Return Multiple Results From The Other Sheet
i have two sheets, one to display results (Reults tab) & the other tab containing the data (Data tab) what i am trying to do is some how create a search function and have a forumula which contains a LIKE function that looks up the data table RANGE = Data!A2:K255 the search needs to lookup the primary column Data!B2:B255 ... if any results are found .. show them on the results tab.. and if multiple results are found, display those as well.. (in either instance, the whole row of information in respect to the results need to be dislayed and hopefully no duplicates are found .. eg, Data!A:K of a hit) is there a formula that can achieve this? oh, the search is TEXT based and there should be no empty cells within the dataset after some MASSIVE googling, i have stumbled accross this B1 = Search box (txt field) A6 (which will be a hidden column) contains =MATCH($B$1,Data!A2:A255,0). this formula provides the first instance of the result and provides the row number A7 contains =MATCH($B$1,OFFSET(Data!$A$1,A6+1,0,8-(A6+1),1),0)+A6. this is supposed to look for the next row number which contains a match and provide that row number and througout my other columns, i have B6=OFFSET(Data!$A$1,A6,1) B7=OFFSET(Data!$A$1,A6,2) B8=OFFSET(Data!$A$1,A6,3) and so on 2 things i cannot recitify.. 1, the match has to be EXACT ... unfortunately i cannot use exact .. needs to be LIKE .. eg, i cant use the search word "boat" as the range of data has "boats" 2, it comes up with multile .. irrelevent results.
View Replies!
View Related
VLOOKUP Performance Is Taking Very Long Time To Recalculate
I designed a spreadsheet which uses a lot of VLOOKUPs and it takes a very long time to recalculate. I don't know if there is another way I could do this, but this just seemed to make sense and it works just the way we want it but it just takes too long. Here is what the spreadsheet is designed to do: We have data that is pasted into excel from another source. The data is broken up into account number, date, check number, and amount. Multiple payments from the same account can occur in one month so the data must be totaled. There is a custom function called concatif which works just like sum if but concatenates text. There are VLOOKUPs performed on the account number and the corresponding date, check number, and amount are inserted in the table.
View Replies!
View Related
Concatenate Formula Not Taking Decimal Places/currency Sign
i have lets say a table with rows that contain the following data. SKU - PRICE - SALEPRICE - DESCRIPTION - SALEDESCRIPTION What I am trying to do, is to make a concat formula that says: SALEDESCRIPTION = DESCRIPTION+price was+PRICE+now+SALEPRICE In essence so that i can simply pull down the formula, and all our product descriptions have 'special offer, was xx (price) is now xx (sale price). This works fine, except that in the SALEDESCRIPTION, the amounts from the price and saleprice fields are being brought over without decimal places and without currency symbol. I have tried formatting those two columns as currency or accounting but neither works.
View Replies!
View Related
Copy Data From One Sheet (Fixed Cells And Sheet) To Another Sheet
I want to be able to copy a name from one sheet (Available Players), paste it to a cell in another sheet (Round 1 through Round 20). The cell that will be copied is fixed but the place where it will be pasted will be different and may be on a different sheet. also i would like to change the color of the copied cell to "greyed" out or cut if it can not be greyed out. I have created a button and put in a macro that i created but have been having problems with it, generic 1004 errors that i can not figure out. i am attaching the document.
View Replies!
View Related
Data From The Data Sheet Is Copied Over To The Report Sheet With Formula
The data from the Data sheet is copied over to the Report sheet with formula. Basically, what I need to do is If statement: -when there is no any data in csv file (so the Data sheet will be empty); -the range (A7:N1000) to be hidden (on the Report Sheet) -the Report sheet to be saved and also... I would like to ask, how to select from the Data sheet, the rows with Data only. I copy them over with formula instead of macro- but would like to hide all the empty rows on the report. Option Explicit Sub Update_Report() ' ' Macro1 Dim extract1 As String Dim dReport As String Dim rSheet As String Dim dSheet As String extract1 = "LeadSheetAll_0001.csv" dReport = "Appointments.xls" rSheet = "Report" dSheet = "Data1" Application.ScreenUpdating = False Application.DisplayAlerts = False
View Replies!
View Related
Creat An If Block
I'm new to VBA and I'm trying to creat an If block. If "named range" is TRUE then.... Could someone quickly tell me the syntax for this. The range is actually on a different sheet than I am working on, but the named range is global. In my case my named range is "WBSCheck" and it is a logical/text value of True or False.
View Replies!
View Related
Breaking Up An Address Block
I have another question. When address come through our DB into Excel, they are in one big block. In other words the city and state and zip are in a lump with the street address. Text to columns does not work because it does not break up the text consistently. What can I do?
View Replies!
View Related
|
TRACKER
