Adding Data From Specific Names
Dec 17, 2008
I have a sheet that has the same employee names several times in different orders in the same column with data to the right of it. Example
Name.......Pieces...hrs
.....A........B........C
(1)John...1000......12
(2).........2000......20
(3)Jay.....2000......31
(4).........2500.....20
(5)John...2000.....50
(6).........5000.....60
(7)Bill......1200.....40
(8)..........3000.....60
I need the peices and hours total for each name on another sheet. So I would have John on my sheet and would need to to grab and add the info from B2 & B6 into one cell (since it is the same person). I can always drag the info over for hours once I find a way to do this for the pieces I would think. The problem is that I don't want to add in B1 & B2 for John because those numbers are not a part of the total.
So is there a way or formula for one cell to look at the entire sheet and everytime it sees the name john to add the information one column over and one cell down and then give a total?
I may be able to do some formatting and have all the info I need directly to the right. So I would have (A1) John, (B1) Data, (C1) Data. The issue would be a cell finding the name, taking the information directly to the right of it and adding it as many times as the name is found on the sheet.
View 5 Replies
ADVERTISEMENT
Dec 29, 2008
Is it possible, and does anyone know the formula to find someones name and add the data to the right of it? Example
A1 John B1 32:26:00 C1 500
A2 Bill B2 23:45:44 C2 452 D2 John E2 36:45:11 F2 520
I have many names and they are scattered throughout the sheet. I know how to add them if the are in a single column but not if they are scattered.
In my summary I would have the names ...
View 8 Replies
View Related
Dec 4, 2006
I have name some random names as name1, name2.... name5
to add these cells, the formula is not working
=SUMPRODUCT(INDIRECT("name"&{"1","2","3","4","5"}))
View 9 Replies
View Related
May 13, 2014
=IF(A5:A6="John","Present", "Off")
How to add few more names aside from John. What if I wish to add names like William or Leo on the search criteria?
For example, John or William or Leo
View 4 Replies
View Related
Nov 8, 2009
How do I add a comma to a list of names all in one column? The full name is in one cell.
View 3 Replies
View Related
Apr 2, 2009
I have a spreadsheet with a series of names in a column and respective dollar values in a separate column. I would like a formula to automatically total the value spent against a particular name (without having to enter the name as a value each time).
Example
John Doe 3.89
John Doe 1.27
John Doe 0.98
Amy Smith 1.56
Amy Smith 2.29
Amy Smith 1.11
Alex Brown 0.67
Alex Brown 4.56
Alex Brown 8.87
I would like a formula to automatically populate a column, spreasheet, etc as follows:
John Doe 6.14
Amith Smith 4.96
Alex Brown 14.10
View 4 Replies
View Related
Oct 31, 2013
I have an output in the form of multiple worksheets. I have found a solution to merge them all toghether into one workbook. But I would like the sheets to be named after the original file name.
Lets say my file is called 9252400.xlsx, i would then like the corresponding sheet in the workbook to be called 9252400
The code I am using for merging is:
Code:
Sub GetSheets()
Path = "C:Documents and SettingsDAJOMy DocumentsMD-CPHPI_Database-908315-3PlantInfo_Excel"
[Code].....
View 1 Replies
View Related
Apr 19, 2007
I have this macro (below) which produces the the date on a file name when I update it every month. However, I would like it to show a different version of the date and in a different location.
At present it produces the following (example):
Ice_Cream_Report_Month_07-02
What I would like is:
Ice_Cream_Report_Month_Feb07 (or even Feb-07 would be fine)
Macro:
Option Explicit
Private Sub ComboBox2_Change()
End Sub
Private Sub ComboBox3_Change()
End Sub
View 9 Replies
View Related
Jan 14, 2013
I have an excel sheet (version 2010) that has a few sections that you can add in names (via drop down box). I also have a spot where i want these names to populate - on a second sheet.
Ex
Truck #1:
1. _____
2. _____
3. _____
4. _____
5.______
Truck #2:
1. _____
2. _____
3. _____
4. _____
5.______
Truck #3
1. _____
2. _____
3. _____
4. _____
5.______
Truck #4
1. _____
2. _____
3. _____
4. _____
5.______
6.______
7.______
8.______
My problem is when I refer to the first worksheet from the second worksheet it shows blanks where there are no names. For example i want the second sheet [ LOC ] to show the names that appear under Truck #1, #2, #3, #4 - but truck #1 may not always have names under it?
Sample Excel file attached - Help.xlsx‎
View 6 Replies
View Related
Jun 18, 2013
I am currently using cell references to create a depedendent list with data validation. How to make cell references show up with some invalid characters?
For example, one of my cell references is Youngs_Market_Company. However, when pulling up the cell, I want it to be displayed as Young's Market Company.
View 2 Replies
View Related
Feb 6, 2013
I have a 97-2003 spreadsheet with approximately 22,000 lines of seperate companies. I need to know how to insert a blank line between the company names. I have been using the "Right Click - Insert" method but soon realized there must be 2000 different companies. The Company Name column is A.
View 7 Replies
View Related
May 7, 2012
Can I create data validation list of the names created in the name box or of the sheet tab names?
View 5 Replies
View Related
Oct 23, 2012
Excel 2007
I have a few dozen pictures created when a macro runs. They all have unique names. I'd like to add comments to cells, where the cell.value decides which picture to pull. All the examples I've found online show how to do this if you have pictures saved on your hard drive by referencing the file path "c://mydocs/...blahblah/"
Is there a way to reference the pictures I've created/named with my macro?
Here's the snippet of code that creates the pictures and names them:
Code:
For i = 2 To Application.CountA(Sheets("Allocation").Rows(1))
Sheets("Allocation").Activate
Set rInput = Sheets("Allocation").Range(Cells(1, i), Cells(10, i))
sPicName = "_" & Sheets("Allocation").Cells(1, i) & "_"
sSheet = Sheets("Allocation").Cells(3, i)
dDate = Sheets("Allocation").Cells(5, i)
[Code] ......
Here are some examples that are close to what I'm looking for.
VBA Popup Pictures - 1108 - Learn Excel from MrExcel Podcast - YouTube
VBA Express : Excel - Add pictures that float like comments.
View 4 Replies
View Related
Aug 27, 2006
I am developing a macro to select cells within a worksheet based on certain criteria and to make a list of their range names. My approach is to copy the range names to a column of cells using something like the following text:
Sheets("mySheet").Range("myRange").Name.Copy
but I get an error message saying "Object doesn't support this property or method". Why can't I copy a range name, and is there a way to work around this?
View 9 Replies
View Related
Mar 9, 2007
I have a directory that contain a large number of files. How do i preserve certain files with specific filenames while other files are deleted?
For example,
I have files that are named : "58-26150B", "58-26200", "58-26200B", "58-26500B", "58-26550" etc. Please refer to uploaded screenshot.
The files that i want to keep from the above are "58-26200", "58-26200B". The code should be able to loop through the entire directory & preserve only specific files while deleting the rest.
Private Sub CommandButton18_Click()
Dim nWave As Integer, i As Integer
Dim ConvergedFile As String, Pattern As String, KillFilePath As String, SimilarFile As String
nWave = Worksheets("nomogram").Cells(13, 4)
For i = 1 To nWave
ConvergedFile = CStr(Worksheets("nomogram").Cells(33, 3 + i)) 'Loop through list of wanted file names "58-26150", "60-25950" etc
Pattern = Mid(Trim(ConvergedFile), 1, 3) 'Update the filename prefixes i.e. "58-", "60-" etc
View 6 Replies
View Related
Jun 27, 2008
I have a code that goes through and deletes everything that doesn't contain a certain word, but I have several words ( Names actually) that I need to keep in my sheet, but all others can be deleted, along with their rows...the names are on in Column B...there are about 11 names currently and probably be adding a few more next month...This is a macro that is set for work to go through like I said a lot of Rows and delete all them but those that have the names of the workers I need to keep in the sheet...the names are scattered throughout, which is the reason for a need of a macro to do it for me cause it takes me forever to manually do it each day...The code for just one person I can't get to do multiple names so this is what I came up with based off what I know plus reading hundreds of pages on the net regarding similar style needs, but none exactly the same....
This is the Code that I'm trying to use but unfortunately I get Type Mismtch Error 13 ( I think it's error 13)...what am I doing wrong? (I only am working with one sheet that contains all the data...
Sub SortOutMyTeam()
Dim wst As Worksheet, wstNew As Worksheet
Dim rData As Range, r As Range, rTotal As Range
Dim avNames() As Variant
avNames = Array("Name1", "Name2", "Name3", "Name4", "Name5")
Set wst = ActiveSheet
Set rData = Range("B1:BL" & Cells(Rows.Count, 1).End(xlUp).Row)
Application. ScreenUpdating = False
wst.AutoFilterMode = False
With rData
For i = 0 To UBound(avCrit) Step 2
. AutoFilter field:=3, Criteria1:=avNames(i), Operator:=xlOr, Criteria2:=avNames(i + 1)
On Error Resume Next
Set r = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells
View 5 Replies
View Related
Feb 27, 2008
My company is soon to be rolling out a new payment system, and I'd like to be able to track commission for everything I sell. Briefly, we get paid via a percentage of the company's gross profit, and the way in which the company gets paid is tiered. (the cellphone business)
So, If a customer activates a plan thats below $39.99, we get paid X amount. If it's below $59.99 we get paid Y amount, and so on and so forth, there are multiple different tiers.
What I want to do is set up a list via data validation so that I can pick what plan they have (via the name of the plan) and have it return a numerical value (ie $39.99) and also have the ability to pick text messaging, etc as options so that the workbook will add the monthly rates (39.99 + 14.95) and then have it return value for the right priceplan tier.
If this is confusing, I apologize for not being more clear, but attached is an example. The top one is what I'm actually trying to code, but the bottom is completely filled in, so you can get a more clear sense of what I'm trying to do. Honestly I'm not even sure if it can be done.
View 2 Replies
View Related
Jun 6, 2008
the active sheet contains a number of defined named ranges. i would like to delete range names beginning with "Sales", yet the number of defined ranges changed from sheet to sheet = for example, Sheet1 may have 3 defined ranges (Sales1, Sales2, Sales 3), Sheet 2 may have 1 (Sales 1). Is it possible to included a wildcard search in the following code?
Sub DeleteRangeNames()
Dim RangeName As Name
For Each RangeName In Names
ActiveSheet.Names(RangeName.Name).Delete
Next RangeName
End Sub
View 3 Replies
View Related
Feb 14, 2007
I have a userform that I'm using to add data to a worksheet, with the following
Private Sub CommandButton1_Click()
Dim OutSH As Worksheet
Set OutSH = Sheets("Sheet1")
OutSH.Cells(nextrow2, 1).Value = Surname.Value
OutSH.cells(nextrow2,2).value = ID.value
OutSH.cells(nextrow2,3).value = Date.value
......
I need to ensure that duplicate entries are not made for the same person on the same date. The ID is unique to each person.
IF statement that can check for a duplicate and then come up with a dialouge box with some custom text, and then exiting the sub?
View 9 Replies
View Related
May 25, 2012
I need to add a specific prefix (in this case DR- ) to a whole column. The problem is I have some cell that already have the prefix while others don't. I also have some cell with value N/A and I don't want them to get the prefix either
PHP Code:
___C___
DR-1220Â
1222
 1233H
DR-1220Â
1222
 1233H
[Code] ......
What I need them to be is :
___C___
DR-1220
DR-1222
DR-1233H
DR-1220Â
DR-1222
DR-1233H
[Code] ....
The text need to be search able (no formula ).
View 6 Replies
View Related
Sep 19, 2013
I'm looking to easily drag the sum of certain cells in a different column BUT keeping a specific range, it's hard to explain so i'll show an example...
A1
A2
A3
A4
A5
A6
A7
A8
B1=SUM(A1:A4)
B2=SUM(A4:A7)
B3=SUM(A8:A11)
And so on...
Is there any way I can do this by dragging down the cell formula from B1 and it remembering the range of 4, so I don't have to manually select each range...?
View 1 Replies
View Related
Jan 21, 2009
I have 2 columns named "ASC" and "AE" which have total calculations of stores inventory data. To the right of the "ASC" and "AE" columns are store columns with (C1="store#"), (C2="state"), (C3="name"), and (C4:C14="inventory count") totals.
If at anytime a stores "name"="AE", I want the "inventory count" for that store to calculate within the the "AE" column.
Anytime a stores "name"="anything except AE", I want the "inventory count" for that store to calculate within the the "ASC" column.
A1:A3= "ASC"
A4 through A14= Inventory Total
B1:B3= "AE"
B4 through B14= Inventory Total
C1= Store#
C2= State
C3= Name
C4 through C14= Inventory count
D1= Store#
D2= State...
View 11 Replies
View Related
Jul 10, 2009
How can i make a best XI team from a list of weekly scores in my Fantasy Football league?
Players are as follows:-
One goalkeeper - Top scoring goalkeeper per week
Four defenders - Top four scorers per week
Three midfielders - Top three scorers per week
Three forwards - Top three scorers per week
I need it to find the top scoring players in the above categories and then display them in a specified area.
I also need this to happen for each individual week, NOT cumulative. literally, the highest scoring XI players, as listed above, for the present week.
View 6 Replies
View Related
May 2, 2014
I currently have a piece of code that opens all of the files in a folder that are called "*agent*", opens them and copies information. Now, these files come with numbers at the beginning which, are always the same. I only want to open certain files that begin with, for example, 801, 802, 803, 804, 805 and 806. How would I write this into my code? As you can see from the below code, it now looks for the files that all have "agent" in the name, but this is opening files that have that name but are not the right ones. Here is my current macro...
[Code] ....
I hope this isnt as simple as putting "MyFile = Dir(MyFolder & "*801*", "*802*")" etc.
View 2 Replies
View Related
Jun 12, 2008
I have a worksheet that will have tabs added as time goes by and a macro within the workbook currently selects all of the sheets and copies and pastes formulas within all of the sheets simultaneously. Right now, if a tab is added, the macro needs to be updated to select that specific tab name as well.
I would like to simply have the macro select all of the sheets starting with "IL" until the sheet before "reference", or all sheets except the first 3 and the last 2 if that is easier. Any ideas? And thank you for your help, you people really amaze me sometimes!
View 9 Replies
View Related
Mar 28, 2014
How would I go about finding the "Number of Shirts Ordered" values in the top right?
View 1 Replies
View Related
Apr 3, 2009
There are two columns in an excel sheet, one is date of birth and other is date of reteirment
looks somewhat like this:
D_O_B D_O_R
5-Mar-53 31-Mar-11
30-Jun-57 30-Jun-15
20-Jun-51 30-Jun-09
2-Feb-55 28-Feb-13
2-Jul-51
13-Oct-55
1-Sep-51
7-Jul-54
14-Mar-53
3-Aug-50 3 0-Sep-13
Some of the dates in D_O_R are missing. I need to fill in up all the dates in D_O_R column.
D_O_R is = 58years+D_O_B(NOTE the dates should come as last date of the month)
View 8 Replies
View Related
Jan 31, 2013
I have a table which looks like this:
Name 1 IDNumber Name 2 Name 3 Column 5
Tom20148 John Malmo
Tom20148 Will Malmo
Bob20206 Will Malmo
Tom20206 Will Paris
Bob20206 Rob Rotterdam
Bob20207 John Rotterdam
Ray20207 John Paris
Tom20208 John Malmo
Ray20208 Rob London
Ray20209 Rob Paris
Bob20209 Will Malmo
Is it possible to have excel go through this list and assign each row a number in column 5 based on the names and the IDNumber? Basically, I would want each entry that is identical in name 1, 2 and 3 to be assigned numbers 1, 2, 3, 4, 5 etc based on their IDNumber. So Tom/John/Malmo with the IDNumber 20148 would get the number 1 in column 5, while the next match (Tom/John/Malmo/20208) would get the number 2 in column 5. For each different match of Name 1,2 and 3, I would want the count in column 5 to start at 1. So Bob/Will/Malmo/ 20206 would get number 1, Bob/Will/Malmo/20209 number 2 etc.
View 4 Replies
View Related
May 21, 2008
I am trying to enter only the named ranges in the active worksheet into an Array.
The amount of named ranges can be from 4 to 7 per sheet.
I'm self taught at VB (This posts on this forum have taught me - thank you!) and have got the below code working on a Workbook - but not on a worksheet level.
ActiveSheet.Names.Count returns nothing, changing to ThisWorkBook.Names.Count returns the sum of all .Names in the workbook.
Sub aTest()
Dim sArray() As String
Dim sJoin As String
Dim y As Long
x = 1
y = ActiveSheet.Names.Count
z = ActiveSheet.Name
View 3 Replies
View Related
Feb 4, 2010
Hi, looking for help desperately in fine tuning a formula. I have a formula at the moment (which works) for searching through a list on a separate file and totalling up all values which relate to it, see below:
=sumif([filename.xls]1’!$B:$B,D10,’[filename.xls]1’!$H:$H)
The tab ‘1’ in the formula relates to the first of the month so this month there are 28 different tabs with similar information.
With C10 containing the date in this instance, does anybody know a way of making ‘1’ a variable so that entering ‘04/02/10’ would change it automatically into a 4? (Unfortunately for me changing the 1 to =c10 didn’t work).
View 14 Replies
View Related
