I recently posted work of my attempts to create a league table, i feel i have probaly chosen the worst way to record the actual results of matches, causing the need for complicated formula which donkyoite has been very helpful in doing and i thank him, but i dont understand whats going on, was wondering if there was a simplier way?
View 10 RepliesI seek advice on using the value of NOW() as a record ID in an address book program. Question #1: Do Excel developers often use a record ID? Question #2: What record ID schemes are fequently employed besides date/time? I have decided to create an Excel address book as an exercise to increase my knowledge of VBA, and also as a useful application for work.
I realize that a record ID is not essential in Excel in the way that it is essential in Access, but I feel the need to have some unique ID associated with each address, so that I may have different worksheets, with data related to a given Contact, sort and manipulate it, if necessary, but have the record ID as a way to restore the relationship of rows to a given Contact, and also, as a handy way to examine the data in the date/time sequence in which it was entered. I have experimented with the following code, to assure myself that I can access the number returned by the NOW() function, manipulate it as a string, and format in various ways if necessary.
Dim n As Double
n = Now()
sn = Str(n)
p = InStr(sn, ".")
first = Left(sn, (p - 1))
l = Len(sn)
d = l - p
S = Mid(sn, (p + 1), d)....................
I have several worksheets (Labeled Sheet1,Sheet2,sheet3) What I need to do is to step through each row in sheet3, and do a search in sheet1, it the data was found, then return the row number. I then will need to copy data from sheet1 (rowfound columnA , through rowfound CoulmnBd to sheet3 current working row columnK
View 3 Replies View RelatedIn my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working":
1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List
2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List
3.) Report In Column D: Identify and return all unique values in "Column C" as a List.
Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"
I am creating an asset management sheet. For the formula I am trying to work out there uses 3 fields : ID, start date, and end date.
What I want to do is be able to show if the ID is duplicated within another record with an overlapping date. So an item is flagged if it is in the list within the same dates as another record. I tried a few countif formulas but with no success.. I may just be approaching the problem incorrectly though.
if h5 = "Supply Only" or "Supply Only With IWA" then I5 should be "S/O" however if h5 = "Supply Only With Survey" or "Supply Only With Survey With IWA" then I5 should be "S/o & S"
h5 could also be "Supply & Fit" or "Supply & Fit With Iwa" which should return "S/F"
but if h5 = "Supply & Fit With Survey" or "Supply & Fit With Survey & IWA" then I5 should be "S/F & S"
I want to say, if B1 = A then the value is 10, if B1 = B then the value is 20, if C then the value is 30 and D the value is 40.
If there are two conditions then I know it would be =if(b1=A,10,0) but this is obviously a bit more complex.
Formula that if the cell (C1) looks up C5, and the range in C5 is 1-10000, I want C1 to show 10, or if C5 has less than 1, then I want C5 to show 0.
View 9 Replies View RelatedI have an If statement I am trying to get to work but not sure of the flow. Each part works seperately but not together. What I need is if column E is blank leave blank, if cell DI2 is Yes then use columns DJ to DP for the lookup if DI2 is No than use columns O to U for the lookup. The first 2 lines are the individual formulas that work independently. The 3rd was my last attempt at combining them.
=IF($E21="","",VLOOKUP($E21,$O$5:$U$476,2,FALSE))
=IF($DI$2="YES",VLOOKUP($E21,$DJ$5:$DP$366,2,FALSE))
[Code]....
I have a master worksheet with due dates for various forms to be completed. These forms are on separate worksheets within the workbook.
I'd like to highlight the master date entry in red if the form hasn't been completed by the due date, but also highlight in green when it has been done.
I used three different dates. In which the outcomes were available. The first three sheets.
Is there a possiblity to use excel to calculate a future outcome of the next month(Sheet 4). The figures are in a way linear, because the previous months are in it.
So it's possible for the future outcome to go up or stay the same. It can't be less then previous months.
See attachments.
It's about a holiday (combinations) of arrangements. So it'll be influenced by the date (because of holliday plannings), finance etc etc. But the last factors/variables we will use and test in SPSS and other programs.
The question is more: Is there a program/formula in excel which will predict the outcome in the last sheet with maybe a factor of outliners (this coincendence of a better salary in some parts/or the date that people will book a holiday). And how the formula would be formulated in the last sheet?
I am trying to get more than one outcome from a logical function in a single cell. I am teaching a class and i want this particular cell to add up all the points from tests and quizzes and then give me an outcome of a grade (A,B,C,D, or F) Right now i can only get the cell to give me an outcome of A or B. I need the cell to be able to give me 5 possible outcomes (A,B,C,D,F).
Right now i have this formula in this particular cell......=IF(Q6>234,"A")......so if Q6 is greater than 234pts they get an A...if not they get a B. How can i also give ranges of total points to have the cell give C,D, or F?
In my sheet i got some negative and positive numbers -210(Negative) and 210(positive).
So when on F there is a Negative number on K must come (Yes).
And when on F there is a positive number on K must come (NO)
It must go on for all on F
Is there function for that in Excel what i got now.
I have a web query that I would like to loop to a specified range. The catch is there are two possible outcomes. There is an outcome (data) and an outcome that is useless (no such page is found, etc). I'm kind of stuck trying to get this thing to loop. I would like to reference a range (contains links for input), and input all the data from the range, and based on the outcome, input the useful webpage data into the same spreadsheet.
Here is where I'm starting out.
I need to compare the number value of 3 cells to 64 possible outcomes, of the three cells they can only have one of four possible values, How do I write the formula to do this. if cell a1 is 1,a2 is 1, a3 is 1 then thave it display the number 64, but I also need it to able to look at the same three cells, 64 different combinations, like 0,0,1 displays 21 etc.
View 9 Replies View RelatedI am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
I'm trying to find a macro that will run allowing the user to select a 'starting record number' and a 'finishing record number' when printing.
I have a spreadsheet that feeds from a master list in excel, from over 5000 records.
I need to print the s'sheet with any given indivdual record's information at any given time.
Individual prints are fine. However if I wanted to print from record number 1500 to record number 3000 it would take me all day.
Is there a way I can set up a macro so an option form pops up? allowing selection of "From record" and "to Record" ?
I seem to struggle with excel formulas when time is involved. I am trying to create a spreadsheet that reviews outcome measures (walking tests in this case) to outline firstly if someone has improved or not (Outcome in the blue columns) which seems to be working alright. This takes into account the 2 aspects of time taken to complete the test and the walking aid required.
What I would now like to be able to do is compare each individuals 'After' score with a normal range for that test. I have put a list below the table of the ranges I am looking for. I have tried to put time formats into a long IF formula to try and get the ranges covered for the TUAG test, but doesn't seem to be working.
The 10 metre walk test is a little trickier as this is looking at change in velocity between the before and after scores. I think I need to be able to work out the velocity for each test and then do a comparison, but again bit stuck with the time formatting.
Can anyone help, I'm trying to create some test data, eg 1000 rows in excel. In each cell a formula (?) returns some text such as Dog, Cat or Rabbit based on the probability Dog=0.5, Cat=0.3, Rabbit=0.2
I've come up with a messy solution of generating a random number between 1-100 and then using a lookup table where 1-50 = dog etc... it works but is long winded and difficult for other people to follow.
This code counts all cells with a value from column B1:B:10, C1:C10, D1:D10, E1:E10, F1:F10, G1:G10 and H1:H10. And then give the cells of the 3 lowest outcomes a blue backcolor. The problem is that the code also colors any duplicate outcomes. How can I let the code skip any following duplicate outcome(s)?
View 2 Replies View RelatedSo I have some stats I have been tracking for NHL hockey games. I want to find a formula that is predictive of future outcomes, based on whether or not a team has a statistical advantage. Obviously, there are so many other factors, and there is no golden formula to accurately predict the winner of ever game. I want to find maybe a 70% success rate here. I want to be able to plug in all of the stats, and essentially have it tell me who statistically should win the game.
I have 11 independent variables (Stat A, B, C, D, E, etc.). I have a running total of how often the winning team had the stat advantage for each of these, and I think that should factor into the formula. My dependent variable is whether the team wins or not. how to do that with multiple independent variables.
Im trying to record a macro that will enter a formula in a cell each time I run the macro
Drop the Lowest2:
=IF(ISERROR(AVERAGE((SUM(C5:L5)-MIN(C5:L5)-SMALL(C5:L5,2))/(COUNT(C5:L5)-2))*Scale!$B$2*10),(IF(ISERROR(AVERAGE(C5:L5)*Scale!$B$2*10),"",AVERAGE(C5:L5)*Scale!$B$2*10)),AVERA GE((SUM(C5:L5)-MIN(C5:L5)-SMALL(C5:L5,2))/(COUNT(C5:L5)-2))*Scale!$B$2*10)
Drop Lowest1:
=IF(ISERROR(AVERAGE((SUM(C5:L5)-SMALL(C5:L5,1))/(COUNT(C5:L5)-1))*Scale!$B$2*10),(IF(ISERROR(AVERAGE(C5:L5)*Scale!$B$2*10),"",AVERAGE(C5:L5)*Scale!$B$2*10)),AVERA GE((SUM(C5:L5)-SMALL(C5:L5,1))/(COUNT(C5:L5)-1))*Scale!$B$2*10)
I know I can write the formula in a simpler way, but I was asked to make it like this.
The problem is that when I enter the formula in the cell while recording. I got a message: "Unable to record" so this part can't be recorded
This problem seemed to revolve around "digital signing" with further macro changes done from a different machine (without the proper certificate). Excel warns of the problem and then "unsigns" the project.
I've got a problem with spreadsheets that I've been working on. Now, when I tell it to record a macro, I get the macro name, shortcut assignment dialog box and then an error "Unable to record." After "Ok"ing that dialog box, the normal recording macro dialog comes up. Also, lo and behold, there will not be a macro recorded.
I've got no protected cells or sheets and it doesn't matter what security level is set. It is a "signed" macro but I can take off the signature and still have the problem. I checked for "Help - About" for deactivated modules and there was none.
is it possible to have a cell return all matches from a list and have the list of matches reduce as you type, then be able to select one item from the list? this is a typical feature on internet sites, but can it be done in Excel?
View 3 Replies View RelatedI'm looking to work out an IF statement based on a series of dates entered to give 1 of 3 possable outcomes. Where:
A1: todays date (exmaple =NOW())
B1: due date (exmaple 10/02/09)
C1: completion date (example 12/02/09)
D1: status (overdue, outstanding or completed)
D1: =IF(A1>B1,"overdue",IF(A1<B1,"outstanding",IF(C1<=>A1 & B1,"completed")))
I know the last part is totally wrong (symbols arranged in that manner), but to clarify i would like the date entered in C1 to override the other statements in the fomula to make it read "completed". If no date is entered in C1 then the formula will return either "overdue" or "outstanding" depending on the other dates in A1 and B1.
Is it possible to add a row between every record, that has different data for 2 columns ( compared to the row below )
View 14 Replies View RelatedI am building a form that uses a combo box to lookup up a row in a table and return row data. To make things more complicated I want to return more than one record. For example: the table has a list of jersey. I search for a style jersey. I want to search the list and return all the colours for the chosen style. Sometimes there is only one colour or there can be up to 10. Have a look a the attached example.
View 2 Replies View RelatedHow to group different record and name the group. Attached file
View 14 Replies View Relatedl would like to record a macro that allows one finds a 'key word' in sheet 1 ,then the macro should copy the entire raw of the search results to worksheet 2 .The macro should enable the user to have as many searches as possible but pasting all the results on one worksheet.
View 2 Replies View RelatedI have designed a website for my uncles business and I have used numerous forms in vba. One of the things he wants me to do is display a list of some of the client details on a new work sheet and I have hit the wall..
I have about 6 worksheets,
The ones i need to access in this instance will be clients.xlsm and the temporary one i have created each time the form is run called temporary.xlsm
I need to search for a particualr client number in row A on the client list and return column number 2 and 3. Usually i would use the match or vlookup function but row A contains the client number numerious times.
However, (this is where I am stuck)
there are multiple client id's in row A. For example the client ID i am searching will exist say 3 - 6 times in row A and i always want to return the row number in a new cell on the temporary page each time it has found it.
I am searching for the client id in worksheet("clients").range("a:a")the client id is repeated and I want to return all the rows if there is a match, but match will only return one row...
would it be a for loop with a match function? -
when I find a match i want to be able to record some of those values on the same row it has found a match on the temporary worksheet