Currently I have a spreadsheet with the following columns.
A - company name
B - Company expense date
C - company expense amount
There are many different companies, and each company has multiple rows for different expenses.
How would I create a drop down box, where I can select the company name and have excel create a list of all the expense amounts for that company?
I have a list of names and an associating number with those names. I'd like to create a dynamic top 10 list, but my problem is that I have duplicate values, here's a sample:
[Code].....
I tried to use LARGE and INDEX to create a top ten list like this:
=LARGE(Names,ROW(1:10))
=INDEX(Names,MATCH(T2,Count,0),1)
But, when the list comes out, it shows only the first entry with a duplicate result:
[Code] ....
How I can get it to grab the second "Name" with the same value as the first?
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.
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
I have a dynamic table which is linked to a couple of charts. The table must remain dynamic.
I needed to add two new columns to the table, "Focus Area" and "Category". I need a drop down list in the "Category" column to be dependent on the item selected from a drop down menu in "Focus Area".
I can get the first row of the dynamic table to do this.... however; subsequent rows all lock the drop down list in the "Category" column to the same choices regardless of what is chosen in the "Focus Area" list.
Is there a way to make dynamic drop downs within a dynamic table?
I am trying to generate a good looking, dynamic report tab that runs off another tab that contains ugly raw data for website traffic. This should then drive a couple of charts based on the parameters I specify.
The Raw Data
This is daily data for keywords that we advertise on through Google. Associated with each keyword are various fields:Clicks
Cost
CPC (Cost divided by clicks)
Quotes
Sales
CPQ (cost divided by quotes)
CPS (cost divided by sales)
CTQ (quotes divided by clicks)
QTS (sales divided by quotes
CTS (sales divided by clicks)
Avg Pos
There are maybe 30 keywords - each with daily information for the above fields.
The layout of the raw data is:
Column A: Date
Column B: Keywords
Column C: Clicks
and so on.......
The Report
This is the nice looking report where we can pull in the required bits and bobs in a nice format. Because some of the above fields are calculations I've not been able to achieve what I need using a normal pivot report.
What I would like to be able to do:
Populate a summary report using the above raw data but be able to specify certain things such as:The date range I want to see data for
The specific keyword(s) I want to see data for
For example v- we are feeding the daily information into the raw report each morning. One day I might want to see summary stats for a particular data rang on maybe 3 of the keywords so I'd somehow like to select appropriate date ranges and keywords from drop down lists and then I see the summary stats for the appropriate date range broken down by each keyword.
I'd also like a total underneath this report to add up or calculate the totals and averages for the data based on the parameters I specify.
Finally, given all of the above, I would like to run a dynamic chart based on the raw data but taking into account the filters I specify on the slick report sheet.
So....if I set the date range as From: 01/03/09 to 12/03/09 I can see daily clicks, costs etc for the particular keywords I specify in the drop-down menu or what-have-you
I have a simple range, say, A1:G1. I want to have another cell, H1, reference whatever rightmost cell in the range contains any data. All the cells in the range will be using text.
So if B1 contains data, I want H1 to show what's in B1, but if data is inputted into F1, I now want H1 to show what's in F1.
My application has a information that is constantly displayed to operators. In one of the non-active sheets, is a log of what is happening and I need to export this log in a (prefereably) .html format to display on an Apache web server.
The monitored sheet always have to be visible, and I do not want any dialog boxes popping up, so the Sheets("log") has to be exported under this criteria... Only need the first 1010 rows exported to the html file.
Would all of that be possible?
I need to do stats for each month within a set of data. Ie take the min for all data in the month of Jan only and repeat for all other months etc. Is there some way I can set up a Min function to only consider the data belonging to jan for example and have it change and only consider Feb, then march.. etc Seems like it would be easy to just do this manually but there is a large timeframe over which data was collected so would take forever.
Have attached sample of data. rainfall.xlsx‎
building dynamic charts and filters, so What I'm hoping to accomplish to build a bar chart that looks like the following:
________________________________________________
Filter: State | Filter: Region | Filter: Segment |
Revenues |||||||||||||||| + (% of Total Rev)
COGS ||||||||||| + (% of Total COGS)
Margin |||| + (% of Total Margin)
-------------------------------------------------------
My data is built in the following format on a different tab than the proposed chart:
State: | Region: | Segment: | Account: | Amount: |
MN | Midwest | Major | Revenues | $$$
MN | Midwest | Major | COGS | $$$
MN | Midwest | Major | Margin | $$$
MN | Midwest | Major | % of Total Revenues | %%%
MN | Midwest | Major | % of Total COGS | %%%
MN | Midwest | Major | % of Total Margin | %%%
[Code] ........
It seems like it'll be easy to use a pivotchart, but at the same time, there's a lot of formatting to do, which makes it tricky, especially if I have many states to deal with and the multiple combinations.
I am trying to create a dynamic Excel line chart.
All is well, except for my x-axis (which is a text axis). It currently looks like this: Dynamic axis.jpg
I am using the Offset formula. The formula for my x-axis is =OFFSET(Worksheet1!$D$4,0,-3,COUNT(Worksheet1!$D$4:$D$36)). This is to show the axis if cells in Column D are filled up appropriately.
However, in the cells containing the axis name itself, this formula is used =IF(D9=0,NA(),'Worksheet2'!A6). I've tried removing the formula from these cell, but that didn't solve the problem.
I am trying to solve in VBA how to create pivot tables in new sheets using objects instead of relying on the sheetcounter, which errors out.
I found an old Tip on Mr. Excel: Excel Create and name a new worksheet with VBA
But I can't figure out how to apply the object to the rest of the script. Here is what I have so far:
Sub Macro2()
'
' Macro2 Macro
'
Dim WS As Worksheet
Set WS = Sheets.Add
'
[Code]....
I'm creating a spreadsheet to keep track of my costs of production in an online game. Within the game there are a range of spawned resources that appear for only a short time before being unobtainable these resources have specific types that is shared between multiple spawns of the resource but each resource spawn has a unique name.
My first worksheet lists all the resources and their various qualities and the later worksheets are meant to allow me to choose from a list resources matching the requirements of the item I'm looking to craft. The example i have shown in the second picture requires Tatooinian Fiberplast and Lokian Wild Wheat to craft so in the Chosen Resource column I would like to have a drop down list allowing me to select the named resource type i would like to use - for Tatooinian Fiberplast the only thing on the list should be Omnitwixi and for the Wild Wheat it should show Fizi and Krad
[URL]....
[URL]....
I am aware there are people with more pressing problems than computer games and as such
I would like to be able to compare the "standards" in Column A with the Assessment status in Column B and the Assessment period in Column C in order to generate a compact list in another workbook (ideally) or tab (if not). A list might typically contain all the standards that have been M (mastered) during a given Assessment period. I realize that filters would achieve this to a large extent but I was hoping to automatically populate another worksheet or tab.
View 3 Replies View RelatedWhat I have done is entered code to auto generate the date in column O whenever data is entered or altered in column A. Here is that code:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then _
Target.Offset(0, 14).Value = Now
End If
End Sub
What I am trying to do now, is create a column that will take the information from O and do a sort of COUNTIF function that will count how many items of data were entered on a certain date by the day. For a clearer example, I want it to tell me how many items were entered/altered on 7/23. But I also want it to continuously calculate it for each date after that. Preferably automatically, but if a macro is needed I can create an update button.
Once it can achieve that I would like to create a dynamic graph that will automatically (or via macro button) update to show the last 5 days. It should display the date and how many items were entered that day.
I am using Office 2007.
I am looking for a Macro that would randomly select 20 items from my inventory list for performing spot checks. Column F of Active Coil Log tab is where the data would need to pull from, however I would like all data in the row to go along with it. I've explored the RAND functions, but they don't seem to be the right fit .
View 2 Replies View RelatedI've attached a spreadsheet showing the effect I'm trying to achieve. A list (which changes weekly) contains employee details and length of service. Based on the length of service I need to build a new list grouping the employees based on the service length.
list-test.xlsx‎
I thought this would be a simple task, but i cant seem to figure out how to do this.
I have a list with names and its a fixed number of rows, say A1:A200. In the next column, B1:B200, there is either TRUE or FALSE. I want a make a new list with all names with "TRUE" on the same row, without any blanks. How can I do this?
I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.
View 1 Replies View Relatedhow to draw the top 5 numbers for each type of element into a list, excluding duplicates, without using array formulae?
Example.xlsx
I am trying to create a list from an array. Said array contains formulas that return numbers from input contained in an other table. I would like the list to list the numbers in ascending numerical order.
View 5 Replies View RelatedI have got a worksheet wherein Column A contains the Customer Code, & Column B contains the Customer name. I want to create new worksheets based on the Customer Codes in Column A i.e for each Customer Code, new worksheet should be created wherein I would later on enter some formatting & data in the worksheets. When I select Delete worksheets, all the work sheets other than the 1st worksheet should be deleted.
View 2 Replies View RelatedI want it just to give me a list of every instance a certain word is used in a column, just like on the autofilter. But each value should in the cell beneath.
Basically I have a list of clients, now when using the autofilter I can select to see the cases from just that client.
But what I want is it to list all the clients relevant to certain months in a column.
I have been having alot of trouble and have read alot. I can't seem to figure out the problem. I am currently working on an excel sheet that will select a name from the first drop down list. Based on that name five other list will be generated just on the first selection.
View 3 Replies View RelatedFormula/function to put a set of values into a bucket, depending upon the value.
I've attached a spreadsheet with sample data for your review.
I'm looking to put the Ranking # into one of several Ranking Buckets based on the criteria below:
1-399
400-449
0, 450-499
500-549
550-599
600-649
650-699
700+
I've tried using a LOOKUP function, but I think I'm missing something with it.
=LOOKUP(C2,{1-399,449,499,549,599,649,650,700},{"1-399","400-449","450-499","500-549","550-599","600-649","650-699","700+"})
Excel Bucket.xlsx
I have a spreadsheet set up in the following way;
Excel Sheet Set Up.JPG
What I'm trying to replicate in column A is similar to the live bullet point numbering that can be found in Word documents where if a line is deleted the section numbering (think bullet points in Word) automatically fall into line and update i.e what was 1.4 automatically becomes 1.3 and so on.
So far I've tried the simple formula of = cell above +0.1 which works fine until i have to remove lines.
Is it possible to change the Pop Up box formatting after creating a List.?
View 7 Replies View RelatedI am trying to set the below table up as a validation list. I am having issues with putting it in as list as it creates a new line every time a “,” (comma) occurs. I know I can use the custom and pull the list from a table, but I can’t use this option (the reason I can’t use the table, if interested, is because the single sheet with the validation contains about ~35,000 line items that are being split up into ~200 different sheets. Each sheet is then sent as a separate email…the problem with using the table is that it would on a different spreadsheet to begin with. The email only sends one sheet. As such, it would no longer be able to reference the table).
A - Less than $25,000
B - Between $25,000 and $49,999
C - Between $50,000 and $99,999
D - Between $100,000 and $249,999
E - Between $250,000 and $499,999
F - Between $500,000 and $999,999
G - Over $1,000,000
Any formula to recognize and copy text from any sheet and create a list of the text items on separate sheet. Attached is a sample file I use. The text items in sheet 20 column a,b,c are items I want copied to a different sheet (list). i would like those items in the 3 columns to be copied and create a single list so I can have a count(Don't want duplicates). From there I would add another formula to give me a count.
Baby Inventory Room 2 - 2012.xlsx
I have a large list of in rows with multiple column headings: ie. Name, date, amount. I need to generate a separate list whereby the rows that correspond to a given criteria are extracted and listed sequentially.
I have attached a simple example to demonsrate what i mean.
in the attached spreadsheet, i have a column of colours, and a column of objects- RED book, BLUE hat, etc.
I'd like the criteria that i type in the green cell (a choice of colour), to generate a list of all the items that match that colour, and be displayed in the orange cells. So, if the green cell says BLUE, the list should pick out the BLUE dog, BLUE hat etc.