Sheet11
EFGHIJK8480NVF-25747NVF25747
8581ATT-67546ATT67546
8682RTC-35615RTC35615RealRTC-35615
8783CSX-45715CSX45715RealCSX-45715
8884CSX-24915CSX24915RealCSX-24915
8985HTC-56947HTC56947
9086MCAS-85415MCAS85415RealMCAS-85415
9187BNSF-77716BNSF77716
9288MCAS-97316MCAS97316
9389CLRV-24760CLRV24760
9490CSX-24101CSX24101
I am trying to create a drop down list that when selected will display data in the 10 rows below and 4 columns, so a total of 40 cells. I would like the data to be based on the selection in the drop down list.
The data looks like this,
RequirementsQtyResourcesQty
Level 1 Sentinel10Level 1 Sentinel105
Level 2 Sentinel10Level 2 Sentinel167
Level 3 Sentinel10Level 3 Sentinel156
Level 1 Banga20Level 1 Banga401
Level 2 Banga20Level 2 Banga307
Level 4 Banga20Level 4 Banga0
i have 27 different combinations of the above list with up to 10 different rows. I have a label for each one, but i cannot get the rows below my drop down list to display the proper values base don the selection in the drop down list.
If I have a table as noted below with the following assumptions:
- this table will likely grow
- the 'Include' column data will change based on external criteria/formulas, so the 'Include' column will not be sorted.
- Macros aren't an option as this sheet needs to be macro free.
A
B
C
1
Item
Calories
Include
[Code]...
How do I build a formula that I can place in a data validation drop down to only include 'Item's that have Yes indicated in the 'Include' column?
I've been researching this and found answers if the 'Include' column was sorted via offset, but I haven't found any to sift through when unsorted. I feel like there is a simple answer to this that I am missing. Here is the sheet --> ExampleSheet.xlsx
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 am a small business owner with little experience in Excel and I have been trying to set up a worksheet that will organize my products and finances. I organize sets of various products and sell them in bulk to customers. I was trying to recreate this in Excel so I can figure out the total price for each of these sets and easily edit them.
What I've been trying to do is to take a list of the items and assign a price to each of them. This is on a sheet titled "Prices." Here's an example:
prices.jpg
On a separate sheet titled "Sets," I created lists of items included in each particular set. To easily add or change the products I used drop down menus.
sets.jpg
I would like for the sum of the price of all the items listed under each set to add up automatically in the "Total" row. For example, in cell B7 it should display the number 10. Here is a list of the following formulas I've tried:
[Code] ........
I'm creating a new spreadsheet for different fines we have as a football club.
I have a table frozen at the top which looks like this:
Fine 1 Fine 2 Fine 3
Player 1
Player 2
Player 3
Then also I have a list below this with the date and specific fines:
Date Player Fine Amount
(drop down (drop down Entered manually
list of players) list of fines)
I am wanting the amount of the fine to be entered into the table at the top of the spreadsheet automatically based on the 2 drop down list entries. I would also need the table to keep adding the fines together once new entries are made.
What I am trying to do would appear to be very simple. I am trying to build a quotation form for our sales guys to use. I would like to use a drop down list with the list of products in, which I have created using a list in a second sheet and then using a data validation drop down list.
What I would like to achieve is the sales person selects the product (of a list of only 14) from the list and excel to automatically fill in the two columns to the right with the model no. and also the price.
Would it be possible to use an IF statement as there are only 14 products to choose from? Is there a better way of doing this? I have created the lists of model numbers and prices in the second sheet alongside the product name which the drop down list sources from.
Semi-Related topic: http://www.mrexcel.com/forum/showthread.php?t=434301
what i was wondering is if there was a way to take a list of data (only look at the letters before the "-") and make a list of it..than i column "T" use a countif formula (i can do this part just forgot to include it)
so if "MCS-69257" was added to the list in cell "C8" than in "S6" it would say "MCS" and "T6" would say "1"
Sheet10
C2ABC-259153CXS-280374XCG-265065TAS-199816ABC-114197CXS-21045
Excel tables to the web >> Excel Jeanie HTML 4
Sheet10
ST2ABC23CXS14XCG15TAS1
Excel tables to the web >> Excel Jeanie HTML 4
I have a product list where each row of the worksheet has an image, product details and suggested lists/retails for each local currency.
Some products are not available in certain countries and each market doesn't care to see the other market info. (There are hundreds of items and dozens of countries.)
How can I create a drop down selector so that a user can choose their market, i.e. "Australia," and only the rows and columns relevant to them appear?
Can this be done without macros,? This sheet is distributed among 100+ people and needs to work for lowest common denominator.
I'm using Excel 2007. I would like to seek some advise on how i may update cells after selecting a month from a drop down list.
i have created a simplified version of what i intend to have. One the 1st tab 'Cash Budget 2009', i have filled up a table with numbers, sorted by months. On the 2nd tab 'Dec09', i would like to create an expense table, to be able to be selected by month. So i have created a drop down list based on the months that are created in the 1st tab.
Upon selection of the month from the drop down list, i would like the cells to display (fetch) the data from the 1st worksheet. It would also be helpful if the name of the 2nd tab can be updated to read as the month that is being selected.
I've created seven names in my data validation source list on Sheet 3 in the following range of cells, A2-A7. My data drop down validation list is located on Sheet 1, A2 in the same workbook. I would like to trigger a macro based on the name I select in this drop validation drop down list that will fill out an address, telephone number, and email address in the following cells next to my data validation drop down list on Sheet 1: B2, B3, and B4. I want it to be designed so that users will select a name and have that person's corresponding address, telephone number, and email address fill right next to their name on the same sheet.
View 6 Replies View RelatedI 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 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
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.
I'm trying to create a worksheet register. Insofar, with the help of this forum, I have been able to put together a code that creates a list of hyperlinks to all current worksheets
Sheets("Register").Select
Dim wks As Worksheet
Dim rngLinkCell As Range
Dim strSubAddress As String, strDisplayText As String
Worksheets("Register").Range("A3:A600").ClearContents
For Each wks In ActiveWorkbook.Worksheets
Set rngLinkCell = Worksheets("Register").Range("A600").End(xlUp)
If rngLinkCell <> "" Then Set rngLinkCell = rngLinkCell.Offset(1, 0)
strSubAddress = "'" & wks.Name & "'!A1"
strDisplayText = "" & wks.Name
Worksheets("Register").Hyperlinks.Add Anchor:=rngLinkCell, Address:="",
SubAddress:=strSubAddress, TextToDisplay:=strDisplayText
Next wks
In additon to this I would like an addition to the code to pick up data from a cell (G10) within all the worksheets and place it in Coloum B (starting from B3) of the register. And clear the coloum firstly to make way for any updates. I tried doing this myself by modifying a recorded macro but the data would just write to one cell over and over again
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.
I am trying to create a list of residents, unit types, and unit numbers. I have 3 problems with the following code.
1) The code ignores sheets that have no value in the specified cell. I need it to return a blank cell for those in order to keep the data in each column matched with the sheet it came from.
2) The code includes data from hidden sheets. I only want to list data from unhidden sheets. And more specifically, I want to omit data from unhidden sheets that have their tab colored black.
3) The code includes data from the sheet named "Totals" which I thought I was telling it to ignore.
I have forms filled out by many, and I need to compile the information in it to create a list in my summary.
Is that possible?
fortunatly, the information always resides in the same cell on every file.
In A1 to A10 I have numbers 1 through 10.
In B1 to B10 I have some of the numbers listed from column A, but not all, and in no particular order.
In column C, I would like to list only numbers from A that are not listed in B.
Based on a Beginning Date Value and an Ending Date Value - I would like to color a range of Cells - the month (dates) are in the header row.
For example: Beginning Date = 01/01/09
Ending Date = 06/01/09
I want the range of cells in the same row colored orange under Jan '09 to June '09.
I have a sheet that has 5 columns as shown below, xxx represents the values within the cell that I need in each sql file.
A |B | C| D| E
test1|xxx|xxx|xxx|xxx|
test2|xxx|xxx|xxx|xxx|
test3|xxx|xxx|xxx|xxx|
test4|xxx|xxx|xxx|xxx|
test5|xxx|xxx|xxx|xxx|
What I am trying to do is take the context of each cell starting with B2, and place it in an Individual sql file (or text file with .sql extension) and name it A2 (so test1 in this case).
I tried creating a double for loop, one for last column, one for last row, taking each content and placing in file but I am not sure how to take the column A as the name of the file and to have 1 file per cell.
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.
I have 6 worksheets in my file. In sheets 1-5, column A2:A26 list people's names. Some people's names appears on more than one sheet. Not all cells are populated with a value.
ex.
SHEET1
COLUMNA
Bill
[Code]....
My attempt was... =INDEX('Week1:Week5-!$A$2:$A$26,MATCH(0,COUNTIF($A$1:A1,"Week1:Week5"!$A$2:$A$26),0))
where the sheets were Week1-Week5 and the values on each sheet was A2:A26. But I think there's an issue with Excel being able to 3D reference for these types of functions.
[Sheet] .....
I have the above sheet, what i need to do is pull all records to a seperate sheet. The records need to be between the start and end dates and have no full reply. Please help. I am using excel verion 2003.
I am limiting choices in a certain cell to a list via data validation (using data validation, allow - list and pointing to the source). I want to limit the next set of choices in the next column (in this example in Column C) for the user based on the choice made in the adjoining cell. For example to keep it simple I will use the following: if the user selected "Fruit" in B1, then the options in C1 would only show "Oranges","Apples", "Pears". If however the user selected "Veg" it would only show "Cucumber", "Lettuce", "Spinach", "Radishes" in the drop down in column C. I can put a prefix in front of the column C data validation list if that is needed to narrow down e.g. FR-oranges, FR-apples etc.
A B C
1 Fruit Oranges
2 Veg Lettuce
3
Data Validation source for Column B (H2:H4)
Fruit
Veg
Nuts
Data Validation for Column C: (I2:I10)
Oranges
Apples
Pears
Cucumber
Lettuce
Spinach
Radishes
Almonds
Peanuts
I am trying to create a function that will evaluate a cell, lets call it B10, and depending on this numeric entry input data in cell ranges B14,B22.
What I am trying to do is create a list of locations for a packing slip that when I enter the corresponding location number (i.e. 200) that it will populate the shipping address in B14,B22.
Is this possible?
I'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 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 RelatedI 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 RelatedColumn C and D are should be picked from a dropdown list (data is in named range on sheet 3).
The choices that have t become available in a dropdown list in column E (values also in named rnage on sheet 3) should be based on the combination of column C and D.
The choices that have t become available in a dropdown list in column H (values also in named rnage on sheet 3) should be based on the value of column C.
I'm not sure the way it is set up now (as far as it is setup) is the way to go.
Here is a link to the file : [URL] ...........