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 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 Replies View RelatedI 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.
From every sheet I want to create the same type of graph, so a macro could be very useful.
First I recorded a macro. The worked fine, but it could only be used by only one worksheet. Every time I used the macro it returned to the original worksheet where I created the macro.
With some searching I found out how to change the code so that the macro could be used for the active sheet.
But now I get stuck with the following error: "compile error: invalid qualification" (translated from Dutch).
With error tracking I get stuck at the first line "Sub grafiek()"
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?
Is it posible and how to create a list of barcodes from a list of numbers on the worksheet?
Strokescribe seems to have some ind of solution but the data can't come from the worksheet.
I need help with a macro for copying and pasting of cells. I believe this should not be a problem for the Excel VBA experts, but for someone who can only record macro, I'm really at a loss.
Attached is a sample file, where sheet 'Source' is an example of the sheet from which data are to be copied. The other sheet, sheet 'Final' is an example of the final format that I need. The reason I'm doing this is I'm planning to upload my data into Access and so I need to convert them into a list format.
List of target columns in sheet 'Final' and source cells in sheet 'Source':
Column A: Biz ID - not sure if I really need this, by right it should be listed automatically once I paste the data
Column B: B2 of 'Source'
Column C: B2 of 'Source'
Column D: B1 of 'Source'
Column E: row 6, relevant column
column F: column K
column G: row 5, relevant column
column H: the specific amount
So basically I'm creating an entry for every amount in the table.
I am trying to create a sub-list from a master list based on a column criteria.
Basically, we have a large group of people that are now being split into 3 different sub groups, but still part of the whole. I would like to only upkeep the master list by assigning one of 3 managers to each employee and then having Excel place the employee's info (5 columns) into a seperate worksheet based on the manager assigned (3 seperate worksheets, one for each manager). I have searched through books and Excel help, but still cannot find any formula that can return multiple lines based on one criteria. Am I out of luck and forced to maintain this database with an Auto Filter and manually moving/copying the info?
Need Help in resolving the following issue:
Update a list using a Combo Box works fine when the list is on the same worksheet but when the list is on a different Worksheet it does not work.
Is this achievable? I guess so! but was wondering how to do it.
e.g: if my range defined name called let us say "SP" with a range AD1:AD60 in Sheet1 when I use a combo box the Listfillrange will contain: SP
When inserting a new entry using the combobox it works fine but when the same defined name range points to a different Worksheet example: Sheet2 then the update does not work although the ListFillRange contains the same Range name: SP.
Im still pretty new toi VBA and im struggling with pulling data from another worksheet and placing it into a list. Below is the code currently I think I have identified were its not working but not sure what to do. the code should essentially do the following
on opening the workbook check CS PS data worksheet find the names of people within the sheet, These start at row six and the step between each is 82 it then should input these names one after another in the following range Range("AO3:AO43") one name per cell until the end of the dat,a it seems to run but only inputs the first name in the first cell even thought it picks up each name in the myformula piece
Within the myformula there is a concatenate piece to flip the names to opposite way round I think this is where is not working possibly something to do with the separators? each name is in the format surname, firstname it should flip it so it shows first name [space] last name
provide ive commented out the piece I think is the issue
Sub CSupdate_()
With Application
.Calculation = xlManual
.ScreenUpdating = False
[Code]...
My first sheet of the workbook is an employee roster which shows a weekly rotation between two locations. I have additional sheets that are used as daily sign-ins encompassing a two week pay period for AM, MID and PM shifts. I want to link the sign-in to the roster so that I may add or delete an employee on the roster and have it auto generate to the sign-in.
View 3 Replies View RelatedI have a list of equipment and bits that go with each kit and depending on other kits which they are installed with.
I'd like to automate the list.
Attached an example: I'd like excel to look for the first piece of equipment over their respective sheets and list the parts needed for the chosen equipment.
I am struggling to populate a listbox with data from a different worksheet.
If it is the current worksheet works fine.
The worksheet is called "Drawing Data" ..
I need to create a routine that searches through all the worksheets in the active workbook. The worksheets tab is a specific color based on the level of urgency that is needed to attend to the information in the sheet. I am usin a userform with Option buttons to select the color I need to find and a 2 column listbox to which I want to store te contents of cell c4 of each sheet and the name of the sheet.
I've written the attached code but get a Runtime erroe "91"
Object variable or with block variable not difined
Private Sub CommandButton4_Click()
Dim listB As Boolean
Dim lCount As Long, lCounted As Long
Dim lShtLast As Long
listB = True
If OptionButton1.Value = True Then tabcolor = 3 'Urgency color is RED
If OptionButton3.Value = True Then tabcolor = 6 'Urgency color is YELLOW
If OptionButton2.Value = True Then tabcolor = 4 'Urgency color is GREEN
If OptionButton6.Value = True Then tabcolor = 41 'Urgency color is BLUE
If OptionButton4.Value = True Then tabcolor = -4142 'Urgency color is NULL
If OptionButton5.Value = True Then tabcolor = 53 'Urgency color is BROWN
If OptionButton8.Value = True Then tabcolor = 19 'Urgency color is WHITE
If OptionButton7.Value = True Then tabcolor = 48 'Urgency color is GRAY
Is there a way in VBA to get a list of all worksheet names in a workbook?
View 2 Replies View RelatedI have a list of data for a frame in a building. I used these forums to come up with a sort that will take the data and put in on seperate sheets based on the floor of the building. I need to now go through each "floor sheet" find the maximum values and paste them onto a Report sheet. This has to be done within a macro stored in my Personal.xls folder as the file with data is coming from another program and is always changing. Also, the sheet names wont always be the same as the format might be different like third, or 3rd, or "third". Here is the code for the sort macro, I am sure some of you will recognize it:
Public Sub Unique_Record_Extract()
'extracts unique records from Column A and copies those records to a new worksheet
Dim My_Range As Range
Dim My_Cell As Variant
Dim sh_Original As Worksheet
Dim Cell_Sum As Long
Dim i As Long
'turn off interactive stuff to speed it up
Application.DisplayAlerts = False
Application.Calculation = xlManual
Application.ScreenUpdating = False
'Delete All Blank rows if cell in column A is blank
On Error Resume Next
Range("A:A").SpecialCells(xlBlanks).EntireRow.Delete
ActiveSheet.Name = "MainSheet"............................
I am trying to figure out how to create a drop down list on one page of a workbook with the list of items for the drop down list located on a separate worksheet within the same workbook.
I have a workbook with one spreadsheet with a list of items on it (sheet1/ingredients) and the rest of the worksheets would have the drop down list of items from sheet1/ingredients.
I have been trying to use the validation method with no luck - I just can't seem to get the source path right.
If possible I would also like the drop down list of ingredients to be self updating - the ability to continue to add items to the list and show up in the drop down lists.
When the value in the drop down window is selected I would really like / need to have the data populated in the rows to the right of the drop down window.
While asking is there any sort of auto complete so that one can start typing in the box and the ingredients that start with those letters show up to select from? (Such as if type EVA or EVAP --- then all words starting with EVA or EVAP appear instead of the entire list?)
I have a Workbook with a dropdownlist on it. The dropdown list has some names on it each name will be a refference to another sheet, ie when name Collins Jim is clicked on the drop down list the Jim Collins sheet will appear. I have attached a sample sheet to show you waht i mean.
View 4 Replies View RelatedI am trying to build my first worksheet macro. I have a dropdown list in a worksheet "Active Email" with "Yes" and 'No". When Yes is selected I want to cut that entire row and paste it in worksheet "Archived Emails". I feel I am close with the code but when I select the word from the dropdown, nothing happens.
Here is what I have so far.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
If UCase(Target.Value) = "Yes" Then
Target.EntireRow.Copy Destination:=Sheets("Archived Emails"). _
Range("A" & Rows.Count).End(xlUp).Offset(1)
Target.EntireRow.Delete
End If
End If
End Sub
Looking for a way to use VBA to get a list of the tab names from a closed workbook and put them in a column of the worksheet that has the macro. Is there a good way to do that?
View 1 Replies View RelatedI currently have a workbook that copies the first sheet and emails it to an individual on the first day of the week.
New requirements are for it to be sent to a list of people.
I am at an impasse on how to proceed due to lack of knowledge. The current code obtains the recipient from Sheet3 D4 and i would like to be able to list down this column for additional emails without restricting this to a set number of cells.
I have searched the forum and have been unable to find what i am after.
I have included a test file to show what i have so far.
We use Outlook 2k3
We use Excel 2k3
I have the attached workbook that contains a list of produce in the Produce sheet and I am wanting to have the Availability sheet show what produce is available each day sorted into Good, Limited and None as per the data on the Produce sheet. I should end up with a list of each type of produce listed under the relevant availability each day.
Sorry but I have limited knowledge of VB, I assume that the best way to do this is some code behind the Availability worksheet but I am not able to determine what this code should look like.
I have a workbook that has 20 or so sheets that all roll into a summary statement via vlookups/sumifs/and other formuals.
Currently have around 300 vendors that need to have statements PDF'd which I currently do one-by-one.
Input range: VendorCounts$B$2:$B$1500
Cell Link: $C$1
Print area: A3:P437
Would like to PDF from print area each of the vendors in the drop-down list and save file to a designated location with the file name being the vendor name in the drop down window.
is there a method to do this besides writing them out individually?
View 5 Replies View RelatedIs it possible to have a list in a summary sheet, containing the names of all other worksheets in the workbook, which will automatically update when a worksheet is added or deleted? I use Excel 2003.
I wanted to create a drop down list where after I make my selection, it will automatically take me to the cell range (same worksheet) assigned to each member in the drop down list.
(E.g. If I select "Accounting" from the drop down menu it will automatically take me to cell A94 - See attachement)
Furthermore, could it be possible that after my selection in the drop down menu, only the specific range (corresponding to my selection) will show up and the rest of rows will just hide?