I have information on 7 seperate sheets in a workbook and need to bring the information from each individual sheet and compile on one sheet. I know this is simple and I'm making it a lot more complicated then it needs to be...
View 10 RepliesHave tried a few things but can't get it to work properly. I have attached the file I am working with because it is much easier to understand if you just look at it!
Basically, I have a single sheet with three columns:
Name, Post Code, Tenant
There are varying instances of 'Name' and each one has a different 'Tenant'.
What I need to do is merge the contents of the Tenants cells so that for each different 'Name' there is only 1 instance and then for Tenants all the different cells are merged i.e. "Tenant 1, Tenant2, Tenant3...etc"
I hope this makes sense, as I mentioned above it will be a lot clearer if you have a quick look at the attached spreadsheet.
I need to compile a list of all worksheets except Data, Attempted Calls, Completed Calls, Completed MOU, Incomplete Calls, Percent Complete, Busy Calls, and Percent Busy.
I need to put this list in B4
Then I need cells in C, D, etc to reference the value in B as the target worksheet.
All worksheets are formatted exactly the same, except the ones above.
Let me know if you need the data file or not?
I am starting up a project where I will be managing a certain amount of resources, for this example colored pencils (to simplify the scenario). I have just received lists from the other members of my team requesting particular colored pencils for their portions of the projects, but I do not have enough to fill all the requisitions at the same time. I have created a simplified workbook with two spreadsheets: the first of which has the list of the colored pencils I have and quantities, the second has a compilation of all the requested colors by my teammates.
My Question: Is there either a function or a known macro that would allow me to take the data in the second sheet to create a list of who is waiting for which pencils in the first sheet? And could this waitlist be organized by the order of how many pencils each person had requested with people with fewest requests first?
I am trying to achieve the following. In D2: AQ2 there a values indicating the subject identifier for that column, eg in D2 it is SEN and E2 is Art. Each row represents a pupil name into which grades are inputted. I want to compile a list of column identifiers that corresponds to a D grade (it could be D, D- or D+) eg
A2 Name ----D2 SEN --- E2 Art --- F2 Business Studies
A3 Bob ------D3 D+ ----- E3 A -----F3 C
A4 Tim ------D4 C -----E4 D------F4 D
A5 Pete------D5 D------E5 D+ -----F5 D-
Expected output for Bob is SEN. Expected output for Tim is Art, Business Studies. Expected output for Pete is SEN, Art, Business Studies.
I've enclosed a small sample of the actual worksheet. Expected results for row 3 start in cell CP3.
I want to essentially create a meal plan and then have my shopping list generated from the ingredients columns/areas into a separate sheet for printing. I need shopping list generation. I have attached a sample of what I am currently working with but still needs a lot of tweaking. I am not the most advanced excel user as you can see but I continually try to improve upon all of my work.
Test_Meal_Plan.xlsx
I'm trying to count the number of unique values from a list of over 8,000 records -- and it's very likely that nearly all of the records are unique. At the moment, I am keeping track of an array where I store every unique value I find, expanding the array and adding values as I go, and at the end return the size of the array to tell me how many unique values there are in the range. My method works, but it is very slow, even when I turn on manual calculation.
Here is what I have so far:
Function CountUniqueCases(inputRange As String)
Dim i, j As Integer
Dim cCaseID As Range
Set cCaseID = Worksheets("Results").Range(inputRange)
Dim uniqueCases() As String
Redim uniqueCases(1)
uniqueCases(1) = cCaseID.Cells(1).Value
Is there a faster way to do this? I was hoping there was an array search function built in to VBA, but apparently not. My first attempt at running this function returns about 7904 out of ~8000 as being unique.
I've been trying to create a formula that will take data with a specific status and put those in a list on a summary worksheet.
On my data worksheet I have two columns a category/ name column and a risk/status column, on my summary page I want to generate a list solely made up of names that have a specific status.
I've attached a sample.
I am a Microsoft Excel 2010 user and am trying to create a usage report for a website that I work on. I have been compiling the usage reports into one spreadsheet. The columns I use are Month, Device, Language, Title 1, and Title 2. I can filter each of these rows and it shows me the number of records found. Each row on this spread sheet refers to a time that someone selected something on the site. For example the row may say;
Month (filtered)
Device (Filtered)
Lan (filtered)
[Code].....
How do I automatically populate a table on a separate sheet with the number of records found for a certain combination of filters applied. So if I'm looking at monthly trending I want to know how many times the stress test was started in May, June, or July, but automatically using info from the multiple filters.
What is a formula I can use to compile the filtered data from above.
Jan
Feb
Mar
Aprl
May
Start
=formula?
Cells B2:B100 contain a list of sheet names, cells C2:C100 have entries TRUE or FALSE.
Is there away to setup a macro that goes through the list and if false, make the sheet very hidden, if true it should be visible?
I wanted to write a function that can search for the whole Workbook and give me the list of sheets that has the requirement/ name specified the first sheet. I have many sheets to search for and i cannot slist down each and every sheet using a Vlookup or countif function.
View 6 Replies View Relatedi am looking for a macro that renames existing worksheets from multiple lists and have the new name appear in cell b1 of each new sheet.
List one: Worksheet name "Stats", cells b7:b20
Worksheets i would like renamed: Sheet9:Sheet22
List two: Worksheet name "Stats", cells 26:b34
Worksheets i would like renamed: Sheet48:Sheet56
I have a long list that is geneerated each week and I need to split them based on their category acrross a number of sheets all named by the category. These sheets don't exist at the beginning.
My list would be like:
Col A Col B
UK Dave
UK Bill
UK Ann
US Bob
AUS Sheila
AUS Bruce
I want to run a macro down the list and build sheets for each unique ColA ie UK, US and AUS and then copy the ColB values into the correct sheets so UK has Dave, Bill and Ann in colA of the sheet named UK. anyone have an example of this type of process.
I got an employee list from couple of Site in different sheets it contains Employee name & Department but not a continued list, i.e.
on the attached file, i am looking to populate Summary Sheet, no of Sites, deparments & agents varies.
I want the Site, Department & Agent Name to Populate into one continues list without empty cells .
i have a names list in range P2:P100
like
P2 = A
P3 = B
P4 = C
P5 = D
P6 = E
P7 = F
then create the sheets with name A, B, C, D, E, F
I am currently working on a price configurator which has more than one sheet with parts and prices on it.
what i want to do is have a sheet where i can populate a parts list from the parts that i have values for.
i want it to only pull the lines out of the previous sheets with values next to them.
I'm trying to delete sheets based on a list in Excel 2003 using VB, any ideas how to accomplish it as the various ways I've tried haven't even come close to working!
View 2 Replies View RelatedIs there a way to make the macro only list the selected cell values of Sheet6 and higher (not list the selected cell values of sheets 1, 2, 3, 4, 5)
View 2 Replies View RelatedHow do I unhide sheets using a drop down list? For example I have two sheets one named East and the other West. I want to choose east from a drop down and have it unhide the sheet.
View 9 Replies View RelatedI have a macro that creates sheets from a list of names. I have a template which is copied.
Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range
Dim MySheetName As String
[Code]...
Sometimes I have to update that name list.
What I need is to have some addition that checks if the sheet does exist, if it does I want to skip and move on to the next name.
a workbook containing 4 sheets, all sheets have the same format, only the number of rows and the content of the cells changes.
What I would like is a new worksheet with a macro, so that when i push the button update (or whatever) that all 4 existing worksheets are copied to the new one, without leaving blanks etc. and this even if you have previously added items to one of the 4 sheets. Is that possible.
The final goal is to have 1 huge list which is composed of the 4 sheets
I have several sheets in one workbook. For ease, the sheets are labeled Customer1, Customer2, Customer3 etc. Within these sheets are 5 columns of data, with unique headings that are consistent for all Sheets.
In Column A of each Customer sheet, is the Product ID number. I would like a distinct list of all Product ID numbers in the workbook, across all Sheets. (The amount of data in each sheet may increase or decrease)
I have a wb containing several sheets that the field supervisors use to fill out for ordering material. Each sheet contains different types of material. The problem is that, if they only need one particular item from each type (sheet), they have to print 7 or 8 sheets just for those 7 or 8 items. I want to create a sheet that will list everything they are ordering in one compact, neat area, so they only have to print one sheet.
The set up of the sheets goes like this. There's a column containing the name of all the items. Next to each item is a place they can specify how many of that item they want. They leave it blank if they don't want any. That's it. Really quite simple.
So now, I need this new sheet to find all the cells that a quantity was entered on the other sheets, and list that and the description of the item wanted, along with what type of material it is. I have figured out one way of doing it, but it would take a crap load of hidden IF formulas, and I know there has to be a simpler way. I'm open to all suggestions - whether it's vb codes or formulas, it doesn't matter to me.
I have a workbook with a main worksheet "template", from which other sheets are created. Each worksheet has a specific value found in cell D8.
I want to collect the values found on each worksheet (in cell D8) and list them on the first sheet.
I am working on a worksheet. I have the first tab at the bottom and that sheet contains all my data, and the additional tabs moving on the bottom are portions of that data for clarity. What i have on the primary sheet is 5 columns ( Name(A), Total(B), Tax(C), Amount(D), Date(E)) and being the master lists everything. I can add a filter to choose the name i want and naturally that only shows the relevant data for that name. What i want to do on the second tab along the bottom which is only relevant for the name i have chosen, their own sheet if you like is to be able to extract the data from the master sheet on a periodical basis. For example I have the same data that i need to show (including some additional stuff that manually include) and i will need to include Total, Tax, Amount and Date columns. What i want is to be able to have a drop down list in the cells below the Date column so that when i select the date it automatically completes the Total, Tax, Amount information that is shown on the primary sheet. So Total will be A column through to Date in D column and changing the date in column D will populate A to C with the data automatically.
View 9 Replies View RelatedI am trying to get specific data from several worksheets and put it into a master list. My biggest problem is the the primary sheets are constantly updated and would need to extract some data fromt the master list for these updates.
What I have now is working fine, but I have to duplicate some of the data whenever I add a new sheet.
Can this be done using formulas or do I have to learn some VBA or use of macros?
I have Sheet1 with point numbers in column A; X, Y, and Z coordinates in columns B, C, and D. Other sheets are the same format for different jobs! I would like to be able to have all points in the master list and accessable from all sheets when the jobs overlap and same data is requested.
Is there a way to auto name sheets within a workbook by creating a list. or by a different way.
Each list will have a person name.
File attached to show problem
How do I select all the sheets that are Marked Y in column B when the button is clicked?
I have data contained in matching cells from several sheets. I need a worksheet that reflect the summary of the data from the worksheets. No formula is required. I just want my summary worksheet to list the all the data from my other worksheets. I might end up having about a hundred worksheets that I need included in the summary. Attached is a sample. It would be better if the summary automaticaaly updates as new worksheets are added.
View 3 Replies View Relatedi have generated a list of "Companies on the ASX" (Australian Stock Exchange)
There are several work sheets (all populated from data queries) of companies within different sectors e.g all companies within the "energy" sector and so on (one sheet for each sector)
i am trying to populate a master list of all the companies from all of the sectors however there are some conditions i am having trouble meeting...
1. it has to reflect the exact data on the "sector page" after the Query has been refreshed.
2. i have left some formated rows to allow for new listings as the Query updates so i need it to copy each company from each page, something like..
"Energy" Copy A2:K2 - A5000:K5000 (stopping at row 2500 if that is where the last company is on that list, then the same for the next sheet.)