Plug-in Scores To A Results Sheet
Sep 30, 2009
I have two spreadsheets. One is a listing of football scores from the previous week. The other is the schedule for that week. I would like to be able to populate one from the other.
Even if someone could point me in the direction of which functions to use that would be helpful.
Based on the data in the results spreadsheet in columns M, N and P ... I need to look in the scores spreadsheet and compare to columns A, F, H and I. I would need to retrieve columns G and J for their scores.
I've been looking at SEARCH, LEFT AND VLOOKUP so far!
View 10 Replies
ADVERTISEMENT
Dec 7, 2009
I am trying to create a sheet for invoicing. I have a sheet I want to use as a database. I want to search the database from the "invoice" sheet and fill the fields once i have found what I am looking for.
attached is a sample database I will use. I dont want to use the whole row, only selected colums. for example short_item description, item_number, Brand, SRP, ect. I want to search on the "invoice" sheet, for example, item description,and fill out the rest of the necessary fields in that row with data from my "database".
View 11 Replies
View Related
Jan 25, 2007
I am trying to limit the amount of stuff I write to a sheet, and I figured I can just pass variables around inside of a routine with out having to write each step to a sheet like I am doing now.
Below is the formula showing Z2 as where parsed data is
CVersionFormula = Evaluate("Z2 * 10 ^ (4 - Int(Log(Z2)))")
and here it is, with me trying to plug in a variable rather than read it from the data sheet. The variables for ease are read from the data sheet. In this example I would read in Z1 and assign it "CVersion", then the formula then takes CVersion (a 4 digit number) and makes it a 5 digit number..
CurrVersionE = Evaluate("CVersion * 10 ^ (4 - Int(Log(CVersion)))")
But I don't think I have the variables in there right.
View 9 Replies
View Related
Apr 1, 2008
I need a formula to figure the 5 lowest of last 6 sores, since everyone doesn't play every week there would be blank spaces and it needs to just give me the average if they have less than 6 scores!
I have manipulated the formulas until I am blue in the face and cannot get it to work;
View 9 Replies
View Related
Oct 17, 2006
Need useful Excel Plugin/Addins.
View 4 Replies
View Related
Dec 16, 2013
I'm running an Excel program such that the user inputs a variable. The program opens an Access database which is linked to the Excel sheet where the variable is stored. Access then runs macros containing queries based on that variable. I've noticed that the link to Excel is slowing performance and I'd like to speed things up, some code that would allow me to plug the variable into Access for use in the query without having to link to the Excel workbook.
This is a piece of the code I use in Excel which runs the Access macro:
Code:
For iter = 1 To bottom_row - 1
If IsEmpty(MyMacro(iter)) = False Then
Set AccessApp = CreateObject("Access.Application")
With AccessApp
[Code].....
Everything works well as-is; it's just slower than I'd like. Due to other constraints I'd rather not go into here, I really am forced to use this kind of Excel/Access interaction. In other words, the user must use Excel to run an Access query.
View 5 Replies
View Related
Aug 17, 2009
There are scores in B1:B10 (10 scores). I want to take the top 8 scores. So I used this formula which works just fine.
=SUM(LARGE(B1:B10,1),LARGE(B1:B10,2),LARGE(B1:B10,3),LARGE(B1:B10,4),LARGE(B1:B10,5),LARGE(B1:B10,6) ,LARGE(B1:B10,7),LARGE(B1:B10,8))
But I'm trying to make the formula a bit more dynamic. Is there anyway to have a set of data in B1:B50 (50 scores will probably be the most) and take the top x scores? X will be set in a separate cell (lets just say A1)?
View 5 Replies
View Related
Feb 13, 2008
I have a raw score for each case. I need to map those raw scores to scale scores. For example, a raw score of 0 to 0.49 maps to a scale score of 120, a raw score of 0.5 to 0.99 maps to a scaled score of 110, etc. There are a total of 13 possible scaled scores (120, 110, 100, ... 20, 10, 0). Is there a way to do this in Excel2003? (Note, if anyone on the list is 'bilingual' (Excel and SPSS), I'm looking for the Excel equivalent of the SPSS command RECODE raw (lo thur .49=120), (.5 thru .99=110), etc. INTO scale.
View 2 Replies
View Related
Apr 21, 2014
I have an excel workbook which requires us to enter the log details of some sites and then a report is generated using these logs (report attached) . [URL] .......
I want that excel should automatically identify the site and calculate its outage with the following formula:
Outage %age= Total Outage(min)/100.80
and add it in the respective column while identifying the outage.
e.g. if outage is of power then the calculated %age outage should be pasted in the power outage column in front of the respected site.
e.g. in the sheet attached above the first row in the first sheet states that SKU2326 was down for 50 mins
Now %age outage will be
Outage %age= 50/100.80
Outage %age=0.496
Now as the outage is related to power so this outage should go into power column in sheet number 3 labelled as "Network Total Up time Week#17"
Now the next time SKU2326 becomes down it should be added with this 50 mins and the outage calculated as presented above.
View 1 Replies
View Related
Apr 6, 2008
i have two sheets, one to display results (Reults tab) & the other tab containing the data (Data tab)
what i am trying to do is some how create a search function and have a forumula which contains a LIKE function that looks up the data table
RANGE = Data!A2:K255
the search needs to lookup the primary column Data!B2:B255 ... if any results are found .. show them on the results tab.. and if multiple results are found, display those as well.. (in either instance, the whole row of information in respect to the results need to be dislayed and hopefully no duplicates are found .. eg, Data!A:K of a hit)
is there a formula that can achieve this? oh, the search is TEXT based and there should be no empty cells within the dataset
after some MASSIVE googling, i have stumbled accross this
B1 = Search box (txt field)
A6 (which will be a hidden column) contains =MATCH($B$1,Data!A2:A255,0). this formula provides the first instance of the result and provides the row number
A7 contains =MATCH($B$1,OFFSET(Data!$A$1,A6+1,0,8-(A6+1),1),0)+A6.
this is supposed to look for the next row number which contains a match and provide that row number
and througout my other columns, i have
B6=OFFSET(Data!$A$1,A6,1)
B7=OFFSET(Data!$A$1,A6,2)
B8=OFFSET(Data!$A$1,A6,3)
and so on
2 things i cannot recitify..
1, the match has to be EXACT ... unfortunately i cannot use exact .. needs to be LIKE .. eg, i cant use the search word "boat" as the range of data has "boats"
2, it comes up with multile .. irrelevent results.
View 10 Replies
View Related
Jan 14, 2009
I have created a registration workbook for this year's youth sports league. All of the information is entered into a User Form and separated onto it's appropriate sheet designated by the child's age. Next year, I would like to use this year's workbook to look up returning players.
Will it be possible to add a "lookup" button into my form, or create a lookup program, that once the registrar clicks on the correct player, the information is plugged into the User Form, the registrar adjusts the age and any necessary info, presses enter, and the information is copied into the appropriate category in the new workbook? I haven't worked with User Forms long enough to know if they can be filled in that way, but if this can be done, you are the people who would know.
View 3 Replies
View Related
Dec 1, 2007
I have a "Log Sheet" with dates in one column and times in another column.
Currently I have a formula which matches the months of the year and totals the time during that month below is this formula. (This formula id on the same sheet.
=SUMPRODUCT(--(MONTH($A$5:$A$1426)=1),--(YEAR($A$5:$A$1426)=2008),$F$5:$F$1426)
Now I am trying to clean up the sheet and I want to move all the results onto another sheet Lets call it sheet 2 and sheet 1
sheet one will have the months and times.
sheet 2 i want the formula in each cell, andto place the result in each respecting cell/
This is what I came up with but why is it not working?
=SUMPRODUCT(--(MONTH(Sheet1!A:A)=11),--(YEAR(Sheet1!A:A)=2007),Sheet1!F:F)
View 9 Replies
View Related
Apr 22, 2013
I have a Sheet named Main, Juz and another called wordforword.
Currently on Main sheet I have a vlookup that displays the results from wordforword. Instead of showing the result, I would like the user to be directed to the wordforword sheet result, with the click of a hyperlink.
I do prefer formulas over macro if possible.
View 1 Replies
View Related
Jun 27, 2014
I have a workbook with multiple sheets , final sheet has a data of all sheets , in the first sheet i want to select what results to be shown in that final sheet ,, specially the week and the LDM
View 12 Replies
View Related
Feb 25, 2009
how to return data to a second sheet if data selected via a filter.
i have attached spreadsheet i am working on and looking to make this as easy as possible
on sheet "data" cell f2 im looking to return all goals scored by hull
on sheet "data" cell f3 im looking to return all goals conceded by hull
on sheet "data" cell f5 im looking to return all goals scored by hull at home this i will need to do by selecting hull in "home team" filter hull
on sheet "data" cell f6 im looking to return all goals conceded by hull at home this i will need to do by selecting hull in "home team" filter hull
on sheet "data" cell f8 im looking to return hulls last 6 games as over and under which in this example will return the last 6 rows 27-32 and will read 4 under 2 over
on sheet "data" cell f9 im looking to return hulls last 6 games at home as over and under which in this example will return the last 6 rows 22/23/26/27/29/32 and will read 3 under 3 over
View 10 Replies
View Related
Mar 27, 2009
I am trying to find a function that will scan down a column to find a particular criteria. I would like all the cells that meet this criteria to have their entire row pulled through to a new sheet.
For Example, In the attached spreadsheet, I would like everyone in sheet 1 to have their details pulled through to sheet 2 if they are in class 1, sheet 3 if they are in class 2, and sheet 4 if they are in class 3.
View 2 Replies
View Related
Sep 3, 2007
I wonder if someone has a clever solution for the following, I have a spreadsheet where I have done subtotal on some of the figures, does anyone know how I can copy (and paste into a new sheet)only the rows where I have a subtotal?
View 8 Replies
View Related
Mar 19, 2008
I've got a bizarre problem here. I've been working on a piece of code and one line has started behaving erratically: Sheets("BLANK SCORE").Copy after:=Worksheets(Worksheets.Count)
I want to do is create a copy of the sheet called "BLANK SCORE" and copy it after the last sheet in the workbook. This code is part of a loop that runs a maximum of 10 times (potentially creating 10 copies) and I've had inconsistant results such as:
*) Code works and copied sheet is created (desired result)
*) Code is ignored and no copied sheet is created (shouldn't do that)
*) Sheet is copied to a new blank Workbook (...huh ?)
I've also tried changing:.........
View 2 Replies
View Related
Aug 28, 2013
I need to start a list in cell a8 on sheet1. I need it to find and list multiple results vertically. It will lookup what is in cell a1 on sheet1. The table of info is on sheet2 from a1 to b44. Column a on sheet2 has the values of what is in column a on sheet1 and column b is what I need returned to the cell with the formula.
View 3 Replies
View Related
Jul 1, 2013
I have a sheet that I need to routinely filter for a specific code then paste it into a different sheet in the same workbook. I would love to set up a simple macro that would do this for me, but I can't seem to figure it out.
In the results I would like the header row if at all possible, but I can always just make it part of the macro.
View 2 Replies
View Related
Feb 22, 2014
I am having trouble copying and pasting the results of a calculation on one sheet to another sheet. I believe my problem is that my destination cell is actually two cells merged so the destination is obviously not the same size as the source. Here is the section of code I am working on:
Code:
Sub CalculateDeviceFailureTimes()
'
' Used to manipulate the UEM data file to find the outage times of different devices in the Astro System
'
Dim Output As Integer
[Code] .......
Like I said the section after the initial 'If' works and 0:00:00 is entered into the cell D15 with no issue. My problem comes when I try to copy the result of the formula in 'G3' to 'D15'. Like I said I believe my problem is that 'G3' is a single cell and 'D15' is two cells merged..
View 3 Replies
View Related
Aug 29, 2007
I recorded a macro to perform an Advanced Filter. I then adapted the range to & LastRow. My question is, can this now be adapted to remove the Select so the sheets are not selected when this is run.
Sheets("CIT Results").Select
Sheets("Open Calls").Range("A1:I" & LastRow).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Open Calls").Range("N5:V8"), CopyToRange:=Range("Q50"), Unique:=False
Moderators, can you please edit the Thread title. It should be "Advance Filter From and To Non Active Sheet"
View 6 Replies
View Related
May 21, 2014
I need to filter a large database using multiple OR criterias.
Database is CUSTOMER NAMES in each row with CONSUMED PRODUCTS following in each cell.
I want to filter two or more products and list customer names in sheet2. Like listing customers using salt or pepper Filter criterias will be entered into cells in sheet2 with results shown below and I want search to be active and alive just like vlookup function.
Sheet1
CUSTOMER NAMES---CONSUMED PRODUCT---CONSUMED PRODUCT---CONSUMED PRODUCT
GEORGE--------------SUGAR-----------------SALT
MICHAEL-------------PEPPER-----------------CHILI
JACK-----------------BREAD------------------SALT-----------------COFFEE
View 1 Replies
View Related
Sep 12, 2013
I have a simple sheet consisting of about 900 rows and 4 columns. The cells in column D are filled with numbers. I converted column D to a table and sorted to show only numbers over 10,000. I can now see the results including the cell values for rows A,B,C, and D. I would like to delete the rest of the unused rows, or create a new sheet with my results. Right now, some row numbers are not visible, so the visible rows go from 4, 27, 45, 48, 52, etc. When I try to copy and paste what I see in my results into a new sheet, the entire original sheet still pastes.
My final goal is to just have a sheet with 4 columns, and the corresponding values as long as the values in column D are over 10000. I do not need a table, just a regular sheet.
View 3 Replies
View Related
Jun 13, 2014
I have a workbook with 50+ sheets. Within each sheet are rows of data in column A that I'd like to search for specific text.
I'd like to search each row from every sheet for specific words (e.g. "7 days" AND "Monday" AND "Tuesday" etc.) then copy the entire row containing all my search text in a new sheet on column A along with the name of the sheet it was found in in column B and the row number it came from in column C.
What I am trying to accomplish is to search through all the sheets and post results in new sheets for each search string.
View 14 Replies
View Related
Jul 4, 2014
I have a database file where users name can be located in 2 neighbor columns. Against each user name (in next 2 cells on the right) there is a evaluation (good or bad).That database will be growing and new additions will be added each month. That means one user can appear several times on a different row with different evaluation.
In the same file I have a separate sheet where I need after running a macros to have aggregated list of users (in one column) and against each user I would like to see how many how many good and bad score he have. It will be good if users are sorted in alphabetical order.
I am providing an attachment with example file.
View 2 Replies
View Related
Feb 7, 2014
I have a report that is run weekly that shows items that have been returned over the last 3 months. The report shows the original date of purchase and the return date, but not the number of days since the purchase and the return. I need to have any items that were returned over 15 days go to a new sheet and display just those rows of information.
View 8 Replies
View Related
May 24, 2014
I have a workbook that contains 3 sheets. What i am looking to do is:
1. Use the names in Sheet 1 (Column A) and find the names on Sheet 2(Column E).
2. If there is a match, put the whole row that contains the match on Sheet 3
View 14 Replies
View Related
Aug 6, 2008
I'm getting a runtime 1004 error "Cannot Shift Objects Off Sheet" right at the line when i am trying to collapse a subtotal (showlevels, rowlevel 2). I searched about this and i tested. I cannot find any comments, hidden comments, rows or columns. I cannot find any shapes (ran "Kill_Shapes" posted by Aaron Blood).
Sheets("Oxnard Planning 10 (all)").Activate
'SORT: Del Code (D), then Style (A)
Range("A1").Sort Key1:=Range("D1"), Order1:=xlAscending, _
Key2:=Range("A1"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
'Subtotal by STYLE
Range("A1").subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, 9, 10, 11), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
'Subtotal lines = Bold & Pink
ActiveSheet.Outline.ShowLevels RowLevels:=2 '<< ERROR:Cannot shift Objects off Sheet
With Range(Range("K65536").End(xlUp), "A1").SpecialCells(xlCellTypeVisible)
.Interior.ColorIndex = 38
.Font.Bold = True
End With
ActiveSheet.Outline.ShowLevels RowLevels:=3
View 2 Replies
View Related
May 20, 2014
I have a worksheet that contains 3 columns, A, B, C, that I need to run through auto-filter and copy the results from a cell, F2, into another sheet each time the filter criteria changes.
Although the worksheet will contain over 11,000 rows (the attached sample file is trimmed down to around 1000 rows),
Col A will only have 8 different possible criteria for autofilter: 1,2,3,4,5,6,9,10
Col B has around 70 criteria, and Col C has around 700 criteria.
The number of rows in the sheet and consequently the auto-filter criteria will likely change each time (but will usually hover around these quantities).
As an example, here is how I would envision this working for Col C:
1. Starting on the 1st Sheet (named "FW15"), I auto-filter Col C on criteria/value 1
2. I copy the resulting value from Cell F2 of sheet FW15 and paste it into the first empty cell of Col C in Sheet 2 (named "CopiedResults")
3. I return to my first sheet, FW15, turn off the enabled filter for criteria/value, and turn on the next autofilter Criteria/Value of 2
4. Repeat Step 2
.
.
.
Keep looping through Col C to make sure that all auto-filter values have been applied, and all resulting values contained in Cell F2 are copied over to the second sheet.
Likewise, I would need to run through the auto-filter criteria in Col A and Col B, and copy their resulting values (from cell F2) into Sheet2 Col A and Col B.
Attached workbook : autofiltercriteria3.xlsx
View 2 Replies
View Related
