Cross Check Names And Associated Values To Give A Total
which formula achieves the following:
i have a simple table design with the dates running across the row, below each row i write the name of the person on shift. in the 3rd row i mark the time they are late for work (if any).
below that in a seperate table i have a list of my staff next to that i would like there to be a running total of their lateness. I need a formular that will look for their names and then total any lateness that i have inserted. I have include a basic spreedsheet of what i want to achieve, the column in RED is where i wan the totals.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Cross Referencing Names On Different Worksheets
I have three different sheets, each with a column of names, titled "list1, list2, list3." I also have a fourth list of names on a sheet titled "masterlist." I am wondering if there is any way I could cross check the three lists against the master list to see if any names appear on the master list but do not appear on any of the three lists. Is there any way I can cross reference the master list with each of these lists?
View Replies!
View Related
Spreadsheet That Will Give A Total Number For Each
I have a table with 5 columns and approx. 85-90 rows. Column A has the Branch name in it e.g. Beavers or Bedfont (11 Branches in total) Column B has User Type - Adult, Child, Guest (Adult), Guest (Child), Catalogue Column C has Session Type - Booking, Drop-In Column D has Total Session Time (mins) - which gives a number in minutes of the total session time used Column E is not needed I currently get a calculator and add up e.g all of the adult Bookings for Beavers and enter them onto a Report Sheet, then all of the Adult Drop-Ins for Beavers etc. I want an Excel Spreadsheet that will give me a total number for each so I can do away with the calculator. I am thinking of creating a new sheet with a number of cells that have a formula similar to this =IF(AND(A2="Beavers",B2="Adult",C2="Booking"),E2,0) But I want it to see Adult, Guest (Adult) and Catalogue as the same thing / and I want it to pick up Child and Guest (Child) as the same thing.
View Replies!
View Related
Cross Check A List Of Alphanumeric Numbers?
I posted my question on another site and didn't get very many responses. I've got a list of alphanumeric numbers that I'm trying to cross check against a bingo grid. The bingo cards are in a PDF format, so here's a screenshot. There's a separate sheet for Red; White; & Blue.....
View Replies!
View Related
How Do I Count Shirt Sizes And Give A Total Of Each?
Sorry for the question. Normally I find answers to my excel questions by going through the help tab or by searching on Google. However, I don't even know what question to ask on this one!?! Basically I have created a spreadsheet with several columns, but I have one column that lists the shirt size (YS, YM, YL, AS, AM, AL, XL, 2X, 3X) of each person. Is there a formula that I can create that will tabulate the number of sizes (i.e. AS=2, AM=7, etc.)? In previous years I made a column for each size, and simply placed a "1" in the correct column, and had excel just add the 1's from each column. However, that takes more time and space. I was hoping to streamline it this time around.
View Replies!
View Related
VBA Code To Check Month And Give New Product
I hv following data in the excel. I hope some one can help me to create code to do the following :- 1) if have last purchase date , but no last issue date = New Product. 2) if no purchase date , also no last issue date = None stock issue. 3) if have last purchase date , also have last issue date = cal the date between and put the month value . For example :- if last purchase date = Oct 07 if last monment date = Nov 07 then the stock duration = 1
View Replies!
View Related
Cross Reference Values Amongst Columns
I have these data arranged in three columns as follows: ABC 91183 112198 ***3181 54201 ***5*** ***6*** ***7*** ***8*** (where *** stay for blank cells) I need to "report" somewhere and somehow within the spreasheet hopefully by means of some formulas the following data: 1. for column "C" the maximum value within first group of consecutive numbers that are exceeding 180: in this example equal to 201 2. for column "C" the total number of values within first group of consecutive numbers that are exceeding 180: in this example equal to 4 3. for column "B" the first number corresponding to the first group of consecutive numbers that are exceeding 180: in this example equal to 1 4. for column "A" the corresponding number to the maximum value within first group of consecutive numbers in column "C" that are exceeding 180: in this example equal to 5 and most of all I would like to generalise the problem so that to "report" the same data for all cases that are appearing in the above example;
View Replies!
View Related
Cross Referencing Cells To Input Values
I am attempting to cross reference sheets to match up IPs to building names. I have attached an example. Essentially, I am trying to take column E on the TEST sheet and match it to column A on the IPs sheet. Whatever the Building Name (column B, IPs sheet) is, I want that filled into Column G on the TEST sheet. This would be a very labor-intensive process if it cannot be done automatically.
View Replies!
View Related
Matching Names With Numbers (total)
In Cell A1 I have a name (John Doe). In cell A2 I want the formula which will be a number total. In Cells A4:A20 I have a list of names with John being in several of them. In Cells J4:J20 I have numbers. There is data in between the name and numbers that I don't need. So, I want A2 to search A4:A20 and everytime it sees John Doe to add the numbers that are 9 cells over (J4:J20). This is basically to show how many mistakes John has made (total). I tried =Sumproduct((A4:A20=A1), (J4:J20,1,0)). I know I am missing something or not on the right track.
View Replies!
View Related
Give Relative Values To Numbers In A Group
I have groups of data in a spreadsheet, I need to assign a value in column f for each piece of data in column e. The value needs to be a relative value for the rest of the group. For example if one group of data has 10 rows I need values 1 - 10 in column F, 10 being for the largest value in the respective row in column E and 1 being for the smallest value. Each group does not have the same number of rows. If need be I can add blank rows so the space between each group is equal, (if you can tell me an easy way to do this I would appreciate it as theres quite alot of data).
View Replies!
View Related
Finding Multiple Names Within A Range And Calculating Its Total Corresponding Average
In column A I have a list of 5 Auditors labelled Q1 - Q5, 5 Coolum’s across in column F I enter in their scores as a % e.g. 80%. ...So Q1 - 50%, Q2 - 60%. In column A37-A41 I have Q1-Q5 listed, in Column B37-B41 I need to calculate the average deviation per Auditor eg. If Q1 has 2 entries of 50% and 75% return average value in cell A37 which should be 62.50%. I am trying to calculate the average for each Auditor. find attached example.
View Replies!
View Related
Check Box Will Allow The Total In Textbox 5 To Be Placed In Another Cell
I have a userform that lets the user input a quanity. The check box will allow the total in textbox5 to be placed in another cell once the Accept buton is clicked. My problem is when the checkbox is checked, I cant get the total in textbox5 to cell E27. Im sure this is not hard, but giving me a fit. :x Private Sub Accept_Click() Call AllGood2 End Sub End SubPrivate Sub cancel_Click() Unload Me End Sub Private Sub CheckBox1_Change() If CheckBox1 = True Then CheckBox1 = TextBox5.Text End Sub Private Sub TextBox1_Change() Call totalTextBoxes End Sub Private Sub TextBox2_Change() Call totalTextBoxes End Sub
View Replies!
View Related
Check For Sheets With Duplicate Names
I have written some code which asks the using for a name, selcet's a sheet, copy's the sheet, rename's the copied sheet then hide's the original sheet and makes the copied sheet with new name active. My problem is that I need some sort of code to check for sheets with duplicate names and if true ask the user to rename the sheet or maybe delete the sheet.
View Replies!
View Related
Match Value Of 1 Cell To Multiple Cells And Give Multiple Values
Sheet 2 is the problem. I need to match cells e3:h3 whenver the dates change in in cells c5:c9 and then put there result of cells b5:b9 into the corresponding cells of e5:h9. Basically what I'm saying is that I want to keep a track of all previous pay amounts from each pay period. So when the next period changes the date it also copies the new pay amount to the corresponding date of the previous pay section. I have included a sample.
View Replies!
View Related
Total Two Different Values In One Cell
I am using the following but need to add an additional factor into it and not sure how. The following works... but I also need to change/add... if F1:F100 is equal to 'Cash' or 'Finance'... and I am not sure how to do two values in one column. =SUMPRODUCT(--('Deal Log'!F1:F100="New"),--('Deal Log'!H1:H100="Cash"),--('Deal Log'!J1:J100="Knight"))
View Replies!
View Related
Total Monthly Values For Individuals
I have monthly a list of 2,500 people with an allowance value for each individual shown. Problem I got is that sometimes there is more than one allowance entry for the individual in each month. I just need the total for the individual in that month. Each month is on a separate worksheet. I have been racking my head, but I can’t figure how I can look at the list of people (who have a unique number) and just total it.
View Replies!
View Related
Estimate Total Income Of Values
i wanted to ask that in excel i have different values, if i post on ere its so long actually i cant post it coz it will tke ages to do so; so wot i wanted to no that how to estimate total income of values? and how to estimate annual profit/loss?; i mean the formula to find these terms.
View Replies!
View Related
Rounding Values And Maintaining Total
I need to round a number of values to 2 decimal places. Problem is this sometimes results in the total changing as the values after the 2 decimal places make up the remainder. I need to maintain the total. See attached.. Has anyone done this before? Probably something very simple but for some reason it's not coming to me. Well not without vba anyway..
View Replies!
View Related
Calculating A Total, Based On Values In Other Cells
Using Excel 2002. Here's my problem. Column A contains the month (as text) Column C contains an employee name. Column O contains a reason for absence. Column K is the number of hours of absence. The employee's name may appear several times in the worksheet. What I want to do is count the number of hours per type of absence. E.g. If A=MAY and C=BOB and O=SICK then total hours from all instance of K = X. This will be used on a seperate worksheet where the name C will be referenced from a validation list.
View Replies!
View Related
Paste Names Values
I've have defined 4 names (included ranges) in a workbook. The name ranges are automatic updated by a macro. So the used rows are every time different Now i want to paste the values what's in the range of the 4 names on a other sheet. But how? To well know my question. I don't want to paste the 4 ranges, but the value the range has selected on that otger sheet.
View Replies!
View Related
Total Values In Cells To The Right Of Certain Information From A Range In 90 Sheets
I have a blank sheet and need to total values in cells to the right of certain information from a range in 90 sheets in other workbooks contained within the same folder. for example the range is O13:AX500 the first text to search would be "P1", that could be found several times within the range in each workbook with the sheet named "plant" The value to total would be found in the 2nd cell to its right, ie "P1" in cell O13 then value "£290" in Q13 ::: "P1" in cell X365 then value "£100" in Z365 and so on throughout all 90 workbooks. The total of the referenced values would then be placed in say B1.... I have around 500 reference codes to total throughout the 90 workbooks so maybe having a list of the text reference down column A may help the process. Is it possible and can anyone please help with the code to look at a piece of text in new sheet with cell A1 to A500 containing "P1 to P500" and then total all values in the cell 2 to the right in every workbook and place the total in new sheet cell B1 to B500
View Replies!
View Related
Sum/Total Values In Array Macro Code
I have created an array in Excel VBA 2003 and successfully loaded it with values. The array variable is called "Week". I am currently getting the sum of the numbers in the array by creating a dim variable that adds them statically one at a time: Dim finalvalue As Integer finalvalue = week(1) + week(2) + week(3) That works, except as time goes on there are more weeks and I need to dynamically add all the values together in one fell swoop.
View Replies!
View Related
Return The Total Sum Of Values Between Two Specific Dates
Sheet1 BCDEFGHIJ2Product10/08/200917/08/200924/08/200931/08/200907/09/200914/09/200921/09/200928/09/20093A228157989393994B1784371107922385C4483398261701298 Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 And I am after a formula that will return the total sum of values between two specific dates. So if my results table looks like the one below, the values the formula would return are shown in Cells E9:E11. Sheet1 BCDE8ProductStart DateEnd DateTotal9A24/08/200914/09/200934110B10/08/200907/09/200918511C31/08/200921/09/2009225 Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
View Replies!
View Related
Adding Values Against Unique Names
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 Replies!
View Related
Cell Values Used In Sheet Names
I am trying to allow for a cell selection to initiate a macro that would take the top cell in the current column and most left cell in the current row and placing those values into the names of the workbook and sheet that I want to move to. I am tyring to allow for my users to drill down into data via selecting the cell in question that is referenced from another workbook and sheet. I want to place the value of the cell = most left cell in current row - 1-1 to be placed in Active.sheet"1-1".select The work bood would pull the value from the most top cell in the current column and place that value in Activeworkbook.place here.select.
View Replies!
View Related
Array Formula To Lookup Two Values And Produce A Total Score
I have a table of data where each row is a person, and each column is a group that person might belong to. In the intersecting cell of a person row and group column is text showing what position they hold in that group (eg Director, Head, member etc). In many cases the cell is empty because the person is not involved with the group. There are then two lookup tables which have scores; one for the group name and one for the position type. The group name is always the column header. I have used these lookup tables as I would like the scores for each group or position to be easily adjustable. Probably best that I just attach an example. I tried to solve this using SUMPRODUCT. VLOOKUP on the two lookup tables would have been good but it seems it doesn't work with arrays the way I expected. My attempt (which doesn't work obviously!) is given.
View Replies!
View Related
Running Total Of Visible Values In A Filtered Table (2007)
I have a data table with all my data in it. I want to be able to filter the data, and have a column automatically calculate a running (or aggregate) sum of the filtered (visible) information in a particular column of the table. If you glance at the attached sample spreadsheet (in 2nd post!), you will quickly see what I mean. I am searching for a formula for column L that will caculate a running sum for column K. In column T, you can see the results that I'm looking to reproduce in column L. Each cell in column T simply adds the K column value for that row to the previous value in column T (the cell above). However -- to my knowledge, and as in this case -- the formula for column T can only be copied down the column AFTER the table has been filtered.
View Replies!
View Related
Hide/Not Show Same Values On Balance Sheet Running Total
I want to zero out or blank out my running balance in the columns where I do not have an amount entered. So instead of having my running balance total run all the way down the spreadsheet, I want it to be blank at the first cell where I have not entered in a new bill. I have attached an example of what I am trying to do.
View Replies!
View Related
List Names, InputBox, Check If Name Is On The List
I have an excel sheet with 30 names in column A and I'm asked to do the following: Write a procedure – a sub – that uses an input box to ask for a name. The procedure should then scan through the names in the list to search for the name and make a message box to state “X is not in the group” or “X is in the group”, where X is the name from the input box. HINT: You can make a variable “found” that starts with the value 0 and gets the value 1 if the name is found in the list.
View Replies!
View Related
Listbox Values Linked To Sheet Names
I have created a userform in excel with a listbox that has the names of 4 different sheets located within that workbook. I would like for whenever a name of the sheet is highlighted in the listbox, that sheet is opened. I have played around with the listbox.value and if then statements and can't seem to get it to work.
View Replies!
View Related
Assign Names With Specific Numerical Values
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 Replies!
View Related
Define Names As Adjacent Cell Values
I would Like to define each row in a spreadsheet to the value of the first cell in each row and I have no idea how to go about this. Code Customer _0888 Williams _0435 Smith So basiclally I would like to name the first row "_0888" being 2:2. But without doing each row manually via Insert --> Names --> Define..
View Replies!
View Related
Recognize Text Values As Variable Names
I want VBA to take an equation that has been entered into an worksheet cell as text and evaluate it. Here's a very simplified example that shows the concept: Cell A1 may contain the text: "A * B + C" I would like to do something on the order of this in VBA: Sub Test() Dim A As Integer, B As Integer, C As Integer A = 2 B = 4 C = 6 MsgBox Evaluate(Workbooks("Book1").Sheets("Sheet1").Range("A1").Value) End Sub Obviously, this doesn't work, but I want it to show a result of 14. The actual situation is much more complex, but it all comes down to being able to evaluate the text in a worksheet and recognize the variable names that are in it.
View Replies!
View Related
Highest Eleven Specific Values And Names Returned.
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 Replies!
View Related
Create A Formula Which Sums The Values Associated With Several Defined Names
I am looking to create a formula which sums the values associated with several defined names. For example, I have a workbook with the following defined names SalesPerson1Total, SalesPerson2Total, etc. and these amounts are all sourced from multiple tabs. The amount of defined names (i.e. 'SalesPersons') is variable, therefore, I want the formula to read Sum the values of all defined names which are named with the following convention 'SalesPerson(X)Total'.
View Replies!
View Related
Assign Group Names Based On Cell Values
I have two tables below. First table contains the list of domain names (given just for example, actual list may contain several more such Domain names) with the group names assigned to them. Second table shows the criteria for assigning them the group names: Sheet2ABCDEFG1Table 1Table 22Domain NameGroup NameDomain NameGroup NameDomain NameGroup Name3NCG-W3K-SAT14-0DOSSNCG-W3K-EEC-SUN16-3EECEXCEEC4SVL-WIN-SAT08-0LABSNCG-W3K-SUN16-0DOSSDCDC5SVL-WIN-SAT06-0LABSNCG-W3K-TUE01-0DOSSNCGDOSS6NCG-W3K-MON04-0DOSSNCG-WIN-EXC-SUN21-0EECDMZETIS7NCG-W3K-EXC-MON04-3EECNCG-WIN-EXC-SAT16-0EECSVLLABS8NCG-W3K-EXC-MON03-0EECNCG-W3K-X64-DC-SAT14-0DC9NCG-W3K-EXC-TUE01-0EECDMZ-NCG-W3K-X64-SUN12-0ETIS10NCG-W3K-EXC-SUN15-0EECDMZ-NCG-W2K-SUN12-0ETIS11NCG-W3K-X64-EXC-SUN16-0EECNCG-W2K-SUN20-0DOSS12DMZ-NCG-W3K-SUN11-0ETISNCG-W3K-MON03-3DOSS13NCG-W2K-DC-SUN11-3DCSVL-DMZ-WIN-SUN12-3LABS14NCG-W3K-X64-DC-SUN10-3DCSVL-DMZ-WIN-SAT10-0LABS15SVL-DMZ-W2K-SUN12-3LABSDMZ-NCG-W3K-SUN12-0ETIS16NCG-W3K-EXC2-MON03-3EECDMZ-NCG-W3K-SUN10-0ETIS17NCG-W2K-SAT17-0DOSSNCG-W8K-X64-DC-SUN10-4DC18NCG-W3K-X64-SUN10-0DOSSNCG-W3K-TUE01-3DOSS19NCG-W3K-TUE00-0DOSSNCG-W3K-DC-TUE02-0DC20NCG-W3K-X64-SAT15-0DOSSSVL-WIN-SAT11-0LABS21NCG-W3K-WED00-0DOSSSVL-WIN-SAT07-0LABS22NCG-W8K-X64-SUN19-0DOSSNCG-W2K-SUN17-0DOSS23NCG-W3K-EXC-SUN20-0EECNCG-W3K-SUN20-0DOSS24NCG-W3K-ULZ-SUN14-0DOSSNCG-W3K-TUE22-0DOSS25NCG-W3K-TUE03-0DOSSSVL-WIN-SAT07-3LABS26NCG-W3K-ULZ-TUE00-0DOSSNCG-W3K-MON05-3DOSS27NCG-W3K-SUN19-0DOSSNCG-W8K-SUN18-3DOSS28NCG-W3K-EEC-SUN19-0EECNCG-W8K-SUN22-0DOSS29NCG-W3K-EEC-SUN20-3EECNCG-W8K-X64-DC-SUN11-0DC30NCG-W3K-EEC-THU00-0EECNCG-W3K-SUN17-0DOSSExcel 2007 My concern is to automate this process using a VBA code. How can I search for the keyword given in Table 2 in the first column of Table 1 and assign the group names as it is assigned currently, considering the below conditions and criteria. 1) The correct group name should be assigned to the Domain name containing the keyword given as per criteria in Table 2 2) It is possible that the domain name will include more than one keywords given in Table 2. For example Cell number A7 contains both keywords NCG and EXC, in such cases the preferance should be given to EXC and we will assign the group name as EEC. And in Cell number A12 also contains both NCG and DMZ, again the preferance should be given to DMZ and we have to assign group name as ETIS. That means we will give second preference to keyword NCG. If domain name contains none of the keywords except NCG as per given criteria then only we will assign grup name DOSS.
View Replies!
View Related
|
TRACKER
