I have two work sheets as-
Sheet1
idnameclassscores
1abc280
2efg276
3hij555
8klm478
9mno490
Sheet 2
nameschooldate of admission
abcpublic school2/9/2011
efgpublic school3/4/2010
hijprivate school5/9/2011
klmprivate school8/9/2011
mnoprivate school9/10/2011
now what i want is - on sheet 3 compiled data as-
idnameclassscoresschooldate of formation
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]
I have a workbook, see attached example, which has multiple columns. I want to extract the data for a certain criteria, in this example column E "product".
I then want to take all of the data in columns A to L for the chosen criteria e.g. product 1696 and place it in a new worksheet. I want to do this for every unique product. The example I have given only shows 2 products and limited rows, in reality I could have 50-60 products with hundreds of rows per product.
I have circa 500 sheets (all with identical layout) in the same workbook. All the data is displayed in Columns "A" and "B". The entries in Column A are variable (5-6 digit numbers) B contains numbers between 1 and 200.
I would like to be able to add together the sum of the numbers in B against an entry in A- I have a list of all the entries in column A.
The data in column A is in ascending order but may not always be in the same cell reference.
E.g. "123456" and appears in 3 sheets
(sheet2) A9 ="123456", B9= 5
(sheet 234) A111="123456", B111= 7
(sheet 456) A87 ="123456", B87=3
"123456", total 15
I'm trying to synthesize a fair amount of averages response time data from about 300 separate worksheets into one master sheet and I'm wondering what the best way is to do it using Excel's macros.
In each subject condition-worksheet there are 24 cell items that need to be pulled and put into the master worksheet; there are 2 conditions, for a total of 48 line items.
In each subject file, the subject number is in cell A1, my first desired average is in M7, then M15, M23, M31, M39, M47, M55, M63, then it repeats back to M9, M17, M25, etc..
How can I create a macro that creates a new line for each subject (starting with row 2) first with their subject number (A1 in all subject files), then M7, M15, M23, M31, M39, etc. moving across the row?
I am seeking a method of collecting data from one sheet in a workbook and displaying it on another, based on a search.
e.g.
Sheet1
Garcia Joe 11111
Garcia Matt 22222
Hernandez Efrain 33333
Matias Jorge 44444
What I want to do is to have Sheet2 be a query for Sheet1--that is, in a cell in Sheet2, the user can type 'Garcia' into a cell and thus display the matching entries from Sheet1, e.g.
Sheet2
'Garcia' displays
Garcia Joe 11111
Garcia Matt 22222
I have a file called "registrantExport" with "_great north", "_new england", "_new england", etc. I'm trying to sort them by Division and then by Last Name. Here is the macro I've tried to make, however it doesn't work properly, as it only applies to great north. How could I make so that it works for any worksheet I apply the macro to?
Code:
Sub macro1()
'
' macro1 Macro
'
'
ActiveWorkbook.Worksheets("great north").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("great north").Sort.SortFields.Add Key:=Range( _
[Code]...
I have an excel sheet which looks similar to below and with some more columns.
HTML Code:
AreaBranchEmpolyeesProductsShelfs
Area1Area1 Branch110Product 110
Area1Area1 Branch25Product 16
Area1Area1 Branch38Product 28
[Code] ......
I want to have data in other sheet With respect to Product details. Where It gives me the count of Product 1 and so on.
I am able to get the count using countif function and it displays like below.
HTML Code:
Product Count
Product 16
Product 28
Product 34
Product 49
Product 510
Product 68
Product 78
Product 89
Product 97
Product 108
What I want is when I select "Product 1" It should display some selected columns data from sheet where complete data is available.
HTML Code:
Branch Empolyees Other
Area1 Branch1 10 abc
Area1 Branch2 5 xyz
Area1 Branch5 7 pqr
Area2 Branch5 5 wxy
Which function we can use. I tried Vlookup but was not successfull.
I have the attached file with two worksheets D30(1) and Sheet 2.
I need to extract some of the information contained on the D30(1) sheet into the format as contained on Sheet 2. It is almost like a transpose but because all of the information is not needed and all cells don't have info in it, I didn't even try to transpose the D30(1) info.
Is there an easy way to extract the information from sheet D30(1) to present it in the format of Sheet 2? I need to do this for approximately 50 sheets similar to D30(1). Some sheets might even have more columns than the example provided.
I'm attaching the test file with one example sheet. Within the same sheet, there is month end dates in column A, and from column B till column E, there are 4 variables (Foreign LT Debt, Local LT Debt,Foeign ST Debt and Local ST Debt). Their respective month end values are already given from column G till Column R. How it is going to be extracted is that against each effective date for that variable (Foreign LT debt, for example), the Rating is given under Rating header. So, for instance, for Foreign LT debt, rating from 25/8/1993 till 4/2/1997 is BB-. So, for all CELLS in column B between these dates, the rating should be BB-. Before that, since there is no value given, I want to put it NA. I have filled the values in column B for demonstration. I want to write a function or perhaps a macro to do that automatically for these 4 variables. There are 50 + sheets with the same layout, so I hope the macro or look up function have not to be written 50+ times.
View 1 Replies View RelatedI need is for the Roster sheet, which has the exact same column and row headings as the Leave sheet, to read the data from the Leave sheet and change dynamically if the Leave sheet is changed. If there is data in any cell then I need to protect just that cell in the Roster sheet so that the user creating the staff roster is unable to edit it out.
I have created a plain cut and paste macro on open from Leave to Roster but this doesn't give me the dynamic linking, and doesnt help solve the problem of needing to check each and every cell in Roster against a corresponding cell in Leave, but also if its blank in Leave to allow the user to update that cell in the Roster sheet.
I have an application where I create up to 400 worksheets based on imported data.
These worksheets are named programatically with a main name and a sheet index number (to ensure no duplication of sheet names).
Typical Sheet names are "VARIOUS (1)"; "VARIOUS (2)"; "PART 1000 (3)"; "PART 1000 (4)"; "PART 1001 (5)"; "PART 1001 (6)" etc.
I want to be able to loop through all the sheets and copy similar named worksheets to seperate workbooks. So all the VARIOUS go to one workbook, all the PART 1000 goto another, and all the PART 1001 goto another. I can only determine the worksheet names by looping through all sheets - as I am not sure what they will be called because it is done programmatically. ALL sheets will contain a "(n)" where n is the sheet number, so I believe can extract similar names from the string preceding the left bracket.
At the moment I just lump ALL the worksheets irrespective of name to a single workbook creating an array and using the SPLIT function. This is very efficient and what it does, and I still want to use the SPLIT function and arrays.
this is the code I currently use, any help much appreciated!!! Please tell me how to modify this existing code to clump together similar names and copy - I guess I will need to loop multiple times to achieve this.
For Each mySheet In ActiveWorkbook.Sheets
Range("Arc").Value = "'" & Range("Arc") & "," & mySheet.Name
If Left(Range("Arc").Value, 1) = "," Then
Range("Arc").Value = "'" & Right(Range("Arc").Value, Len(Range("Arc").Value) - 1)
End If
Next
Sheets(Split(Range("Arc").Value, ",")).Select
Sheets(Split(Range("Arc").Value, ",")).Copy
I'm not sure if this is best handled in the Formulas & Functions section or elsewhere, but this is my best guess. I have a spreadsheet used for tracking hardware issues with data in two worksheets:
1. PageCount
Column 1: Date
Column 2: Serial#
Column 3: PageCount
2. Tickets
Column 1: Date
Column 2: Serial#
Column 3: TicketNumber
What I'm trying to do is find a way to extract from those two sheets the date, serial number, page count, and ticket number where the date / serial # are the same and dump this to a new sheet. I've thought that I may need to concatenate the date / serial number together into a single cell or something, but I'm really at a loss as to how to pull this data. Part of me thinks this should be done in SQL, not a spreadsheet, but that's how our reports are produced.
I want to create a sort of complex logging system for my work.
As of right now i use a simple temple. Column A has the job number, column B has drawing numbers. Each cell in column B has multiple drawings. So for example cell B2 might contain 101, 102, 103, 104 and so on or something like 101...104. Depending on the situation. The ... means drawings 101 thru 104. What i want to do is have a second sheet that has Column A be the job number but column B has just one drawing #, so an example would be . A1 = 10939 and B1 = 105, 107, 109..111. Sheet 2 would then have A1 = 10939, B1 = 105, A2 = 10939, B2 = 107, A3 = 10939, B3 = 109, A4 = 10939, B4 = 110, and so on. Is something like this possible.
Extract data from one sheet to another sheet then transpose automatically.
I have attached the work book of what I'm trying to accomplish.
I am working on a workbook in which I extract data from a SQL database into a sheet with more or less the following format:
ID_Company
ID_Level
ID_XX
[Code].....
The aim is to extract a list of any employee that has been tagged as "NEW" in the last column, with his/her detail as per the column headings - and place the resulting list in another sheet. The extract is done dynamically and varies from month to month. So in other words the column headings 2013.MAY and 2013.JUN will change next month to 2013.JUN and 2013.JUL respectively - and so would the detail of the data below them as well. The "NEW" tag is simply assigned through an IF statement.
I had a look at most of the Excel Magic trick video clips on YouTube and managed to get a data extract from my main data sheet - BUT was only able to do the extract on the SAME sheet and NOT onto another sheet in the same workbook.
I have sheet containing a schedule of data. It is a record of changes on a project, each change is sequentially numbered and contains a row of data (date of change, whether approved, cost of change etc). Each change is given a cost centre reference (1 of 10 cost centres are being used) depending on the type of change being made.
I need to keep that record of the overall schedule of changes but I also want to extract each of the cost centre categories into a sub table on the sheet to give a schedule of changes against each category.
I have almost achieved what I want but one method leaves blanks in the sub table which I want to avoid and the other method I used to remove the blank lines but needs the first method sheet calculated before it then goes to work and seems rather cumbersome and I suspect there is a much easier method. (I also want it to be compatible with excel 2003 so it can be used across several platforms.)
I have written basic macros (probably not the most efficient solution, but its what I know how to do). Up to the present point things are going well. (I tried to attach file, but without success - So I will email the file directly it is pretty easy to figure out) I have option buttons tied to macros that populate a data sheet based upon the input.
This works out quite well and I am able to generate the statistics I desire quite easily. Now comes the second phase, (this is where I am stuck) - when I click the option button "no", I would like this to generate a line item in a separate existing worksheet which lists the item number, tells the discrepancy (as written in the column on the inspection criteria sheet), has a text field for the inspector to briefly explain the exact nature of the discrepancy, and also keeps discrepancies in proper order. Conversly, if "yes" is selected, I would like to be able to check the associated comment page, search the page for that discrepancy number, and if found, delete the line item, then re-oder the page.
So in my excel document I have it such that on the first sheet (Labeled 'Sorted') the data is set up as follows: LOCATION, EXTENSION, NAMEWith the appropriate data under each header. On the second sheet (Labeled 'Locations_Ext') I have a named range called Locations; it contains extensions from a separate document, names of people associated with the extensions from the document, and their locations on the map. I am trying to have it such that the excel matches the extension number given on the first sheet with the ones that I extracted from the document on the second and output the location into the first column on the first sheet. The code I have set up for that process is:
=VLOOKUP(B(//SomeValue),Locations,1,FALSE)
I then want the excel to cross check the information that I took from the Visio to see if the visio is up to date with the latest info we have received (Which is the data on the first page under Extension and Name). The code:
=IF(C(//SomeValue)=VLOOKUP(B(//SomeValue),Locations,3,False),"Good","Error")
Both codes seem to be giving me this: #N/A as the results for every cell.. I'm using MS 2010
In the excel file is a VBA code that is combined with a button if someone click on the button a little menu shows with a multiple options of sheets that can be printed. Only the problem is when i hide the sheets the code doesn't see them anymore and the only I can print is the page where the button is placed.
View 4 Replies View RelatedI have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
I have an excel grid which have 2 sheets, sheet2 have all the data, I am doing a macro which based on a value that I put in sheet1 is supposed to find Items which have similar value +/-3 from sheet1. I made a code which I posted below, I don't have any error message but it doesn't work as it should. I explain you with an example: If in the data grid I have the following values
11.8
67.0
25.5
18.1
13.0
if the value I look for is 12 +/-3, it doesn't find anything.
if the value I look for is 11 +/-3, it finds 11.8 but not 13,
if the value I look for is 20 +/-3, it finds 18.1,
if the value I look for is 23 +/-3, it finds 25.5,
Here is my code:
Sheet1.Cells(1, 3).Select
Dim Amin As String, Amax As String ', Bmin As String, Bmax As String
Amin = Sheet1.Cells(3, 2).Value - 3
[Code].....
I use macros, where in DialogSheet EditBox i entered new sheet name and add it.
How to find, exist this new sheet name or not in this workbook?
I have a workbook that we use for our internal customers to use to place orders for equipment. There are five worksheets within that file that outline the different products. The requestor will indicate what items they want by putting a number in the quantity cell that is appropriate for the product. This could be using a combination of all five worksheets for the one request.
What I want to do is to create a summary page that only extracts the information from the other worksheets that has a value marked in the quantity spot only.
I have two sheets, one is "Summary" sheet and the other is "Sheet1". Both sheets has the same template. The "Summary" has color bordered cells and the "Sheet1" has no color. Whenever new color had been added to "Summary" sheet, I would like color from "Summary" sheet to be copied to "Sheet1" in same locations like if red been added to D12 cell in "Summary" sheet, D12 in "Sheet1" will automatically filled in red from "Summary" sheet. Similar to Lookup.
View 3 Replies View RelatedI've used the following formula successfully to pull data from multiple sheets and provide the total on an analysis worksheet. Example of worksheets and formula.
=DSUM(INDIRECT("'"&A2&"'!D:D"),$B$1,INDIRECT("'"&A2&"'!D:D"))
Example of one of the individual sheets holding original data:
A
B
C
D
[Code].....
Example of what I want to see using the Transpose formula (Analysis sheet):
=TRANSPOSE(INDIRECT("'"&A2&"'!C:C"),$C$1,INDIRECT("'"&A2&"'!C:C")) (My theory that currently doesn't work. I'd like to be able to pull data across worksheets and yield the transposed data into the corresponding row of the same merchant row.
A
B
C
D
[Code].....
Basically I inherited a Excel Workbook which has 128 different tabs, unsurprising this is almost impossible to manage.
My requirement is to merge all of the 128 tabs into 1 tab 'Sheet1'.
The macro should keep going even if there are blanks, it should move onto the next tab only when there are over 10 consecutive blanks in Column B.
I have tried searching the forum but nothing seems to cater for the specifics of this case. I would really appreciate some help. I mean really, i am in a mad panic thinking i will have to do 128 tabs manually otherwise.
So I have this problem in excel with comparing 2 columns.
Basically, I have 2 columns(a &B) that I need to compare with one another and find out the matching data. I am trying to use that matching data to enter in our reports.
Both columns might have duplicate items because I am dealing with premium numbers.
Formula to compare these 2 columns and find the matching data and extract it to a separate column. Keep in mind, i am dealing with almost 20,000 lines of data.
I have a lot of rows of data all divided with an empty row and if they are 6 or less all rows need to be entirely deleted any 7 or above need to be kept.
View 2 Replies View RelatedIs there a command or macro to display the name of a sheet in a cell?
I know how to do it in a header or footer, but not in a cell.
I need to copy the name that appears at the bottom of a spreadsheet in a cell at the top -- no need to reference the name of a different sheet, just the same one the cell is on.
