Down The List On The Largest Table Returns No Data
Sep 22, 2009
I have created a monitoring spreadsheet for a public School system. It pulls data from 5 seperate excell files. however when a lookup value that is further down the list on the largest table it returns no data. other cells can lookup that same number and return correct information but from a different table.
The table that has issues is A1 - Y12430.. if the lookup value is low in the first few thosand rows it works.. but if thye value is in say row 9,000 it can not locate it..
View 9 Replies
ADVERTISEMENT
May 31, 2014
A web query I am trying to use to bring an online data table into my worksheet is broken. Now, instead of returning the data table nicely into my worksheet, it imports the code of the webpage, instead, and turns my worksheet into a mess.
The query used to work but there was recently an "upgrade" to the program that populates the web table and the query no longer works and just returns the code for the page, instead. I can see the html code for the table in all of the code it returns but I really need the table to import cleanly into excel.
I can't talk to the people who changed the web program.
View 3 Replies
View Related
Sep 8, 2009
Data Table including-
List of Identifying Code Numbers for customer invoices
Multiple repetitions of individual Identifying Code Numbers in list
Various data in table range including Various Values of invoices from different dates for each repetion of Identifying Code Number.
- Wish to find largest invoice for each Individual Identifying Code Number in the table without using a pivot table.
i have tried combining Max and Large functions with Vlookups etc.
View 9 Replies
View Related
Jan 7, 2013
a
b
c
d
1
monday
tuesday
wednesday
thurs
2
ted
bill
frank
mary
[Code] .......
We use excel for scheduling purposes and each and every day we fill out a floor chart with who is working that day. As it stands now we have to print out a template and hand write all names or type them in and print it out. What I would like to try and do is define each set of names and allow for easy access to auto fill these names. I feel it needs to be a simple process as not all of our managers experienced in excel or formulas. What I would like to try an accomplish is in sheet 1 cell A1 to be able to create a drop list or formula that that would let me return all the names for a given day.
For the purposes of this discussion we can just define each list as the day that is the header ( IE A2:A4 on sheet 2 would be defined as "monday". Also, I already have the names autofilled based off of what the our schedule has. I'm curious if I could create a drop down list with each day of the week as an option, and if they select Tuesday it will then return all the names defined as Tuesday in a column below.And the next day be able to return Wednesday names and so on.
I'm not stuck on it being a drop down list. Maybe if they could just type and =(defined name) in the box above and it would return all the names.
I have tried an =(defined name) and it does return the value but only for the first cell and it has to be in the same row for it to return the name. Which doesn't work because I would like the names that are being defined on a separate sheet.
View 1 Replies
View Related
Aug 15, 2007
I have a spreadsheet with items and their maintenance dates. Each item may be listed multiple times with various dates. I want to create a pivot table that will show the most recent and previous maintenance date. The most recent is easy by using the built in "max of" in the Pivot table Wizard.
how to use the "Large" function in a pivot table? or is there a better way to get the 2nd largest value? Auto Merged Post;Bummer! No answers yet. I thought there might be some experts out there that would have a really tricky way of doing this.
" In formulas you create for calculated fields and calculated items, you can use operators and expressions as you do in other worksheet formulas. You can use constants and refer to data from the report, but you cannot use cell references or defined names. You cannot use worksheet functions that require cell references or defined names as arguments, and you cannot use array functions." I think I may end up adding a column to the original spreadsheet to create the 2nd largest value and use it in the pivot table.
View 6 Replies
View Related
Feb 25, 2010
I have a worksheet that is full of names (column A) and dollar amounts (column B). Many of the names are repeats.
How do I pull the largest dollar amount for each unique name in the worksheet?
View 9 Replies
View Related
Jun 21, 2013
I am doing a macro that will access a specific sheet of my workbook, a specific table, and will sort the table according to the field "Nominal", from largest to smallest. I have tried with this but it doesn't work:
Sheets("F.C.SSA-View 2").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("F.C.SSA-View 2").ListObjects("F.C.SSA_View_2").Sort.
SortFields.Clear
ActiveWorkbook.Worksheets("F.C.SSA-View 2").ListObjects("F.C.SSA_View_2").Sort. _
[Code] ....
View 1 Replies
View Related
Feb 24, 2014
Any way to determine which is the best selling single of that week and print a 1 in the row associated with that CD.
View 1 Replies
View Related
Dec 29, 2009
I have lists of numbers in a columns and would like to elliminate high outlier values in each column. Each list is different so there's no way to just set a max value and say that any value above it is an outlier. What I'd like to do is to average the top 5 or so max values for example, and then say that any vale above this average is an outlier. So a formula such as =average (max1,max2,max3,max4,max5) would work if it's possible to derive max1, max2 and so on. Or maybe there's a different way of accomplishing this?
View 3 Replies
View Related
Jul 31, 2013
How can I get the largest number in this list by using a function?
View 4 Replies
View Related
Jul 20, 2006
I have a small problem with a list of event codes in the form XX###, (Xs are letters, # numbers), I need to be able to find the largest numerical component in the list, and am having problems finiding a neat way of doing it.
I could do it by using the RIGHT command to get the numerical bit in a seperate column, then convert it to number, then use LARGEST, but this seems somewhat cumbersome!
View 4 Replies
View Related
May 12, 2012
I have a table showing interest levels in training courses from a group of schools, eg:
English Maths Science
School1 3 4 2
School2 7 1 0
School3 3 2 5
I want to identify the column heading for the first, second and third most popular courses. ie for School1 the most popular course is Maths, second most popular is English and so on.
I have tried using the OFFSET function, which worked if I provided the cell location of the required value. I then looked at the ADDRESS function to provide the cell location: eg For School2 find the 2nd most popular course:
=ADDRESS(ROW(A3),COLUMN(data?)+MATCH(LARGE(B3:B5,2),B3:B5,0)-1)
But I have got stuck with what I should enter for COLUMN(data?) as I do not know in which column the second largest value is.
I am sure Excel has the required functionality.
View 12 Replies
View Related
Mar 19, 2009
I'm trying to create a drop down list which returns values based on what has been selected in the previous drop down list in the adjacent cell, e.g. if 'Apples' is selected in the previous cell then you should only be able to select from 'Gala, Granny Smith', or if 'Oranges' is selected you should only be able to select 'Seville, Blood Orange'. Is there a formula which would do this, or can I use a pivot table somehow? I'm totally stumped.
View 2 Replies
View Related
Feb 13, 2013
I need to create a dynamic list from a table of data.
I have performance data for 110 different pumps. Data points are generated every hour, and the table is updated with new data periodically. I want to automate the population of a list of 6 different pumps, and specify the date range populated. To put it another way: I want to place data from Pumps 1 through 6 for all of November into Columns A through F. Then I want to clear that data, and show data for Pumps 105-110 for last week in those same Columns.
One of the main goals of doing this -- other than quickly narrowing a field of data -- is being able to quickly chart this data on a scatter plot with a custom format. The pumps are grouped together by region, and individual pumps have specific purposes. So I need to quickly generate graphs with a series' color scheme or formatting that is consistent and logical between different pumping regions.
I haven't been able to make pivot table work because of the graphing issue, and also because of the way it handles data points and presents data. I would like to make this work with excel functions and maybe some filtering, but I'm not opposed to figuring out a VB script if you think that's what I should use.
View 1 Replies
View Related
Jan 29, 2014
In the attached spreadsheet:
1) RAW DATA tab has data for four fund managers. The number of managers will range from 0-200 in the final model.
2) A negative exposure number is a short, and a positive is a long
3) The "Return" is from Bloomberg downloaded data in the live model but I have made up numbers for the purposes of illustration.
4) The Summary tab shows what I would like my final output to be. Please note that (1) the number of managers, and the number of stocks per manager is dynamic (2) the number of longs and shorts is also dynamic (3) i want the impact total for longs (0.39%) to be calculated for me using the code (unless it is easier using another method)
The Summary sheet would have Manager 1, Manager 2 and so on but i have only included Manager 1 for purposes of illustration.
You might say it is easier to use a pivot table, but it is hard to get this format with longs and shorts separated. I would also like errors to be ignored when summing totals in the Summary sheet - these errors occur sometimes on Bloomberg when a company's name changes or they get taken over.
This sheet is due to run by itself when i am not around. (I can automate it myself once I know how to get my desired table).
OZTESTTP.xlsx
View 4 Replies
View Related
Nov 4, 2013
I have a data input sheet on a spreadsheet with a pick list where we can select an option, but I need another 'flat file friendly' option to appear on a second sheet where the data will be pulled from. For example
If on the front sheet Flat Roof Insulation is selected, it needs to return FRI into the other sheet, there are 45 options in the list.
View 1 Replies
View Related
Nov 7, 2008
I have data in a list of sales people and dates of their sales calls. What I want to do is transform the list into a table as shown in the attachment. Then I want to calculate the number of days between the first and last sales call. This part is easy using date formatting and min/max I guess. What I can't do is get the table arranged. I have used a pivot table to count the number of calls per sales person but pivot table wont just display the dates in a table format, it will only sum or count etc.
View 3 Replies
View Related
Jun 16, 2009
Entering a code in a cell, I'd like the cell next to it to return a list of possibles which the user can then select:
Line Code Material
123456 Apple
Orange
Strawberry
By entering the line code the user get the 3 Materials that has this Line code and they coose the one they want.
The list I was using to look up looks thus:
Line Code Material
123456 Apple
123456 Orange
123456 Strawberry
I've read a bit about combo boxes and lost myself complete, so please use small words for me.
View 9 Replies
View Related
Jan 4, 2014
I'm trying to create a automated calculator that i can customize and adjust. start and what functions should i be using. I've looked into Vlookup/Hlookup and it does not seem the function i need to make the workbook work. Basically what I need is when I enter a item on the A column excel would retrieve the list of Ingredients to the calculator worksheet and multiply it by the amount of variable entered. Then product will be printed and added together to the "sum row".
View 14 Replies
View Related
Sep 11, 2012
I'm trying unsuccessfully to write a function that looks for a column in a pivot table and returns the entire column as a text string. The function should take a pivot table and a text string as an argument. The text string is the name of one of the columns. The function needs to return the entire column (excluding the header and footer rows) as an array.
In the example I pasted below, the function should take the pivot table (PivotTable1) and a color, say "Red", as arguments, and then return the values in the Red column as a 48x1 single vector array, in the order shown, starting with 43 and ending with 1. I can only post a text version of the spreadsheet; I was not able to post the actual spreadsheet.
Average of Cost
Color
Date
Hour
[Code]....
View 3 Replies
View Related
May 6, 2009
I want to put a drop down list in Cell D3 with the list of names from column B. There are 14 different names, but when i try and create the list it gives everyname from Column B.
Also once the drop down list works i would like the table to be populated with the managers supplier, so if Dan was chosen in D5, Suppliers 1 to 9 would appear below D3.
View 7 Replies
View Related
May 30, 2013
I have a client list/table that gets new data each day. (ALL) - see file
I need to create separate lists/tables on new sheets from the main data sheet (ALL) base on certain conditions/criteria...
On the (ID) sheet I need all the clients that have ID numbers to display, on the (Reg no) sheet I need all the clients with Reg numbers to display... etc.
Like I said that the main data sheet grows each day and the lists/tables on the other sheets needs to automatically include all the new clients that's entered.
Is there a way to do this using formulas or do I need code to do this?
View 2 Replies
View Related
May 1, 2014
I'm making a macro that filters a data set and then inputs a value into all of the rows for a certain column. When no results show up for the filter I receive a runtime 1004 error because there are no cells to select.
Here is my code:
Sheets("External Buys").Select
Range("G5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Raw Data").Select
ActiveSheet.Range("$A$1:$AU$10432").AutoFilter Field:=39, Criteria1:= _
[Code] ......
View 1 Replies
View Related
Nov 17, 2009
I have a file with 2 sheets. The first sheet contains a list of unique invoice numbers. For each invoice, I need to look in the data table (the second sheet) and locate all the products that were sold under each invoice and list the product ID with the largest sale. For example, invoice 12345678 had sales for 5 different products as follows:......
In this example, I would want to list the largest sales amount and the Product ID for this invoice (invoice 12345678). I have attached a sample of the file and completed the first 8 records as an example.
View 4 Replies
View Related
Aug 4, 2008
I am trying to write a macro that returns specific results from a longer list based on an entry in a cell.
For example if I have “Colours” typed in cell A1 in Worksheet 1 and there is a list on Worksheet 2:
Colours Black
Colours Yellow
Colours Green
Animals Dog
Animals Cat
Animals Tiger
Then I would like the blow answers returned in cell B1 on worksheet 1
Black
Yellow
Green
View 9 Replies
View Related
Nov 10, 2009
INDEX($D$19:$D$22,MATCH((LARGE($E$19:$E$22,1)),$E$19:$E$22,0))
I use this formula to give me a "name" associated with the largest number in a list. But I can't get it to work when I use it on a win/loss number such as
"7-1". It won't read it.
View 5 Replies
View Related
Jul 10, 2009
I have a couple of drop down lists of data created from lists as well as fields next to it.
What I want it to do is take that data, which is name, start time, end time, days worked, queue (or work type), and fill the table next to it which is just a 30 minute by 30 minute schedule sheet with days of week 1 and week 2 on top.
I figure it would be easier to have a GO button to do this.
I will attach an example of what I mean, but please, if you guys find that there is an easier way to do this, just let me know, I feel this might involve some code/macros instead of excel functions
View 2 Replies
View Related
Jan 11, 2013
I am building an Excel file that will be used to track information and at the core of it all is a list of people from different offices where the number of people per office can change and/or a person leaves the company and is replaced by another. I'll simply:
Column A Column B
Office Employee
Hamilton Emp 1
Hamilton Emp 2
Hamilton Emp 3
Toronto Emp 4
Toronto Emp 5
Toronto Emp 6
Toronto Emp 7
Toronto Emp 8
Waterloo Emp 9
Waterloo Emp 10
This will be all on Worksheet 'Info'. I have a Worksheet for each Office and named them accordingly. On each worksheet I want to use Data Validation on a column, we will call it 'ChosenOne', set it as 'List' and have the Source pull all the employee names that belong to that office and use them as a selection
ex: Hamilton Worksheet, 'ChosenOne' would show Emp 1, Emp 2, and Emp 3 in the list.
If Emp 3 changed offices to Waterloo 6 months from now I would like to change A4 from Hamilton to Waterloo and the formula would not have to be changed and the next time someone selects 'ChosenOne' it would only show Emp 1 and Emp 2.
Of course this means on the Waterloo Worksheet, 'ChosenOne' would show Emp 3, Emp 9, Emp 10 now.
So basically I am trying to not specify a specific named range for each office and am hoping there is a way to poll information from a Table (or any other tool that can simplify this).
I would be ok with something like:
Column A Column B
Office Employee
Hamilton Emp 1, Emp2, Emp 3
Toronto Emp 4, Emp 5, Emp 6, Emp 7, Emp 8
Waterloo Emp 9, Emp 10
and just move Emp 3 from B2 to B4 but I don't know if a list can be created from multiple items in a single cell seperated by a , or ; or :.
View 4 Replies
View Related
May 15, 2009
I want to compare a hand entered data sheet to my system data. System data is exported in. Then that data is used to populate a pivot table. I use a lookup list that fills in matching data from my hand typed sheet. From there I want to use true/false to see if the data matches. However, the formula will not copy down. I am sure it has to do with the formula holding a value, but I can’t seem to correct it.
Recap
1.System data populates sheet
2.Pivot table pulls info I need.
3.Lookup list cross references hand entered data sheet and puts value next to pivot table.
4.I need to do a true false to see if data matches but can’t get it to work
Example worksheet: On the worksheet you can see that the true false statements are incorrect. The system data and the hand entered data match two times not once.
View 3 Replies
View Related
Aug 26, 2009
I have a long list of delegates attending functions on different dates and need help with 2 problems:
A) List has filters by date/venue etc but I will want the table to be visible even when list is filtered.
B) I am using the following formula to count "=SUMPRODUCT(--($E$3:$E$728="Thursday 3rd September 2009"),SUBTOTAL(3,OFFSET($E$3,ROW($E$3:$E728)-MIN(ROW($E$3:$E$728)),,1)))" and this works but if I filter to another date then 3rd September shows 0.
View 6 Replies
View Related
