I have a table of golfers with their scores throughout the year. I would like to display the golfer with the highest score - this i can do but my problem is when more than one golfer has the same highest score. How can i dispay the two names. Ideally in one cell but i think i can work around that. If not there will be another post to follow.
A
B
C
[Code]....
I'm setting up a scoring system for my rounds of golf. I have four people competing, and i want to be able to put up a page where i can display the best golfer. I have the scores and i want a function that display f.ex "Thomas" if cell d28 is the lowest number of four cells.
I want to see if i can make a formula that works out how a player got a score based on a scoring system. See the attached file for more details. I want this to show that the player scored or didn't score, played a full match or was a substitute, was booked or wasn't booked and was sent off or wasn't sent off.
View 5 Replies View RelatedI'm Seeking in InRegards To Working Out How To Display Multiple Leaders Within The Same Cell Based Apon There Score In A Game Of Golf, Or If This Cant Be Done Perhaps A Message Saying "Multiple Leaders" So That It Is Known That Its Not Just The One Persons Name That Is Been Displayed. I Have Attatched The Document I Have Been Playing With While Trying To Work It Out Before Making A Big Sheet.
As You Will See "Daniel" And "Paul" Both Have The Same Score, And Below In The Summary It IS Currently Only Showing "Daniel" As The Scratch Leader, Is There A Way Of Getting It To Show Them Both As A Leader, EG: "Daniel, Paul", Or As Said efore Just Saying "Multiple Leaders" Thus Having To Manual Go Through Them ( The Fist Option Would Be Better As There Will Be Approx 85 Names In The Final Sheet)
I am trying to create a spreadsheet where when certain conditions are met, a certain score is given. For example, if the condition is >= 300, the score is 15. If the condition is >= 290, the score is 14. All the way down to a score of zero.
I tried...
=if(A2>= 300, "15", if(A2>= 290, "14", if(A2>= 280, "13"))) etc.
I would like to be able to display all of the sheets in a workbook as a list on the front sheet.
I have a front "Contents" Sheet (attached at the bottom of my post)
In the "Ticket No." column I wanted to have the sheet name, in the "Date" and "Description" column I wanted to display cell values from each sheet; which would be the date and description of that sheet/"Ticket". The "Resolved?" column I will be able to do after I have this part figured out.
The workbook is also attached :
Attached Image : contents.jpg‎
Attached Files : Troubleshooting Tickets.xlsx
Can I stop the displaying of the name of the named area when I am zoomed out of a worksheet? I can not see an option on the Tools > Options > View menu.
A workaround, found by accident, is to make the named area have more than one area.
I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.
What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.
The code I have so far is below:
Public Sub find_managers()
Dim managers1 As Range
Dim names1 As Range
Dim n1 As Variant
Dim n2 As Variant
In my mind it should check the names in the unique list against the imported list and add any missing names.
I've got a matrix combining actions on the left hand side (Col A) and owners on Cols B to F. I have drop down list for each action/owner combo: N/A, OK, KO, TBD.
Owner #1
Owner #2
Owner #3
Owner #4
Owner #5
Results
Action #1
OK
TBD
N/A
N/A
N/A
OK
[code]....
I would like to create a formula in Col G that does the following:If any of the values in columns B to F equals OK, then OKIf any of the values in columns B to F equals KO, then KO I've been tinkering with multiple IFs and quite rightly got bogged down as too many criteria to take into account...
I got a problem though with specificying a 2d array. I basically want to click the "command button" and display one collum with names and the one next to it with results. The only problem is trying to get vba to display this on the work sheet.
Dim students(5, 2) As Variant
students(1, 1) = "John"
students(1, 2) = 45
students(2, 1) = "Ryan"
students(2, 2) = 56
students(3, 1) = "Tim"
students(3, 2) = 78
students(4, 1) = "leah"
students(4, 2) = 89
students(5, 1) = "ray"
students(5, 2) = 23
I am able to do it for a one dimensional array but not the two dimensional one. Can anyone point me in the right direction?
I have and issue where I have name in one cell and need to separate them into 2 cells. Trouble is it is a combination of single names, husband and wife, and partners with different surnames. Sample of data is below:
Belinda Smith & Grant Jones
Janine
Ken & Marie Smith
Louie & Betty Johnson
Naomi Thomsoon & Craig Brown
Narele & Barry Day
Nicholas & Julie Smithson
S M Bole & Co
Jim Morris
Danielle & JohnSimpson
=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
Have a list of aprox 50,000 names. in that list names may repeat themselves multiple times. Is there a way in excel to extrapolate from that list of 50k to display only a single instance of each name in a separate Colum?
View 2 Replies View RelatedI have multiple columns with names (lets say columns A, B, and C). I want to place only the unique names in column E. Is there a formula to do this? Each column does not contain all the names and each has names that are not in any of the other columns.
View 4 Replies View RelatedI need to create multiple arrays arr_1(), array_2(), array_3() etc. Each array will be used to store different data.
My idea was to create a for loop like the one below, which doesn't work.
Code:
For i=1 to 180
String = i
then initialize the array
Dim arr_& String ()
Redim arr_& String(1 to 183)
Next i
I have want to search a cell to see if it contains one of multiple names from other cells.
I have a spreadsheet which contains -
Column A, which 100 rows containing different names.
Column B contains 400 rows of text
Now i want to check of each cell in column B to see if it contains any of the names from column A.
I understand how to return the address of a particular named range (that I already know the name of). For instance, the following works just fine for me:
MsgBox Range("BreedTestRangeName1").Address
In my current workbook, this returns a messagebox that reads: $C$13. However, cell C13 also has other range-names. Indeed, in my current program, it might have five or six more range names assigned to it. But I do not know how to return these names (if they exist), using only code. That is, I do not know how I can return those names, strictly within VBA. I have tried this
MsgBox Range("C13").Name.Name
But, of course, this only lists one range name; and it looks like it only lists the first one that was assigned to that cell. How can I return the others? If I can return them, I would prefer to avoid printing them anywhere onto a spreadsheet.
I cant find out how to get this to work
For e = 1 To 52
num = Sheets!Sol. Cells(e, 1) ' gets first number
nam = Sheets!Sol.Cells(num + 1, 12) ' gets first Image name
Sol.(“nam”) . Left = Sheets!Sol.Cells(e + 1, 13)
Next e
Sam Auto Merged Post;Just to clarify, i have a coloum which contains the names of all the images on a form and i want to perform the same function on each one, currently i have written all of them down but this is time consuming and i need to do similar things later in my program.
I have also tried listing "sol.image1.left" "sol.image2.left" etc in the relevant cells in coloum 20 and written this
For e = 1 To 52
num = Sheets!Sol.Cells(e, 1) ' gets first number
namleft = Sheets!Sol.Cells(num, 20) ' gets first Image name
namleft = Sheets!Sol.Cells(e + 1, 13)
Next e
but this just writes over the string 'namleft' with whatever is in Sheets!Sol.Cells(e + 1, 13)
I'm making a template that will title my charts, and I want to cycle through all the charts and apply names to them (the names for each chart will be stored in a particular cell...). I recorded a macro to see how to access a particular chart, so I clicked record, selected a chart, then selected the next chart. Excel tells me that what I did was
ActiveSheet.ChartObjects("Chart 1027").Activate
ActiveSheet.ChartObjects("Chart 1033").Activate
I want to give the charts meaningful, sequenced names so that I can cycle through them in VBA, so my question is: how do I access/change "chart 1027" and "chart1033?"
I have a table A1:A400, where each individual cell contains multiple names (e.g. A4 =Name1; Name2; Name3)
I am trying to match a list of names in row G1:G50, and return a value of 1 or True in another column if any of the 50 names appear in a cell of row A.
Attendees
Names to be found
Rep was there
Max; Joe; Mike; Karl; Jen;
Tod
True
Max; JB;
Mike
Max; Tod; Mike
True
I have been trying this as an array formula:
{=IF(ISERROR(SEARCH($G$2:$G$50,A2,1)),"", "True")}
But it only returns a true value for if it finds the first name from Column G.
Basically I have a list that user can select up to 6 values from. I have set them as individual strings.
Code:
Public IH1 As String
Public IH2 As String
Public IH3 As String
Public IH4 As String
Public IH5 As String
Public IH6 As String
What I want to be able to do is loop through them and do certain actions. How can I get a for statement to do that.
I tried something like that but it doesnt work. How to get it working I need to do tests on the variable strings and I want to be able to reference them and I'm not sure sure how
Code:
For i = 1 To 6
If "IH" & i = vbNullString Then
Else
MsgBox "IH" & i
End If
I have an email list that I email. Sometimes I get some that doesnt exist anymore. So my program sends email out and respnods to me that says these emails do not exist and gives me the emails. So I manually have to go and find them one at a time on my list to delete by CTRL F. Is there a way to submit all email names to find at once instead of doing one at a time.
View 9 Replies View RelatedI have a column that can have a single name or multiple names typed in each cell. I would like to use a vlookup table to match against the cells values. Exact matches are no problem when it is a single name, but I need a formula that matches up the name, but does not need to match the entire cell text (name1, name2, name3,...) and can count the number of cells that contained this text with in a range. In the example above, I have three names.
If those three names are listed in the vlookup table, I want to count each one so that I can sum up that company 1 appeared x number of times with in the column and is x % of all company names, company 2 appared x number of times and is x% of all companies, and so on. My formula to match exact text values looks like this: =IF(ISERROR(VLOOKUP(D4,$H$7:$J$48,3,0)),0,VLOOKUP(D4,$H$7:$J$48,3,0)) This works fine if the cell value is simply company 1, etc.
How to write the VBA which can multi-select the files and store the selected filepaths in the string.
View 4 Replies View RelatedI have data spanning many files which are named with the date on which they were created (so there are 31 files for August).
For example:
PL080107, PL080207, PL080307, PL080407...)
I'm trying to compile all of my data into one workbook, and have the macro to append each file to a list. Now I need the macro to either OPEN each file, or ACTIVATE each file so that the rest of the Macro can grab the necessary data.
I say Open or Activate, because I can MANUALLY Open a full month's worth of files if it's easier code. If not, I would like it to open and close each workbook on its own.
I have over 7 month's worth, so opening 31 files 7 times is better than opening over 200 files individually!
I have a thing about named ranges and cells, but was wondering if there is a way to create names for multiple cells using set criteria. I think the best was to explain this is with an example that I have attached. Ideally I would like to name the individual cells in the range C3:d4 by concatenating the right column and bottom row to give one unique name. However this doesn't combine them. I have listed the names that they should be and corresponding cell in C8:D11
View 2 Replies View Relateddisplaying the value of a reference once before filling down. I want it to display only once and not display anymore until a different value is included in the fill down. The rinse and repeat.
View 3 Replies View RelatedI am trying to display a value based on the date column A as it corresponds to the unique value(s) in column D.
As you can see from my example, Column D may have multiples of a single item.
I was trying to accomplish this via formulas but I am open to using code.
The Value in Column G will display 'NEW' when it is the MIN value from all of the date values in Column A and its corresponding unique group in Column D.or when there is only one unique value in Column G and Column A is = today() [ROW 15]
The Value in Column G will display 'Remove' when it is the MAX value from all of the date values in Column A and its corresponding unique group in Column D.or when there is only one unique value in Column G and Column A is today() or < today(). [ROW 16] (The value in Column A cannot be greater than current date)
How to write a code for displaying multiple data in sheet2 row wise.
I want to use IF condition with for loop.
I have a folder with 20 Excel files. I'm trying to create a master list of all the tab names. I can see all of the files opening, but it only copies some of the names.
VB:
Sub GetTabNames()
Dim wkBook1, wkBook2 As Workbook
Dim stFilePath1 As String
Dim FileList(1 To 18) As String
Dim iLoopSheet, iLoopProg As Integer
Application.ScreenUpdating = False
[Code] .....