Linking Dropdown Box Within Formula
Dec 5, 2013I'm trying to work out how to allow a formula to be changed by pointing part of the code at a drop down list containing worksheet tab names from another workbook.
View 2 RepliesI'm trying to work out how to allow a formula to be changed by pointing part of the code at a drop down list containing worksheet tab names from another workbook.
View 2 RepliesI've created a name range and i'm now trying to link this to a series of pics
For example
Sheet 1 I have in A1 the drop down box (to be about 50 long)
Sheet 2 I have all pictures named as per the list in sheet 1 e.g
Named dropdown list
Apple
Banana
Pear
Corresponding pictures named
Apple
Banana
Pear
What I am trying to achieve is when "apple" is picked from the dropdown list that the "apple appears say in C1 on sheet 1
PS. Forumula based such as IF or Index etc ican't use VBA or macros yet !!!!!
I need to link this summary sheet projects to this plant and equipment list sheet to show each project numbers separate weekly cost hire on the summary sheet.
View 3 Replies View RelatedI have two identical drop down lists (months i.e. Jan, Feb, Mar ...) in two different sheets but in the same workbook. Is there any way where the user changes the month in sheet 1 & the other drop down list in sheet 2 changes automatically to the same month selected by the user in the sheet 1 or vice-versa?
View 1 Replies View RelatedI have an array of data that I want to be able to filter based on the selection in a dropdown box. For instance, If I select district manager, I want to show the data of only the people who are successors for district manager. For my own reasons I want to use this method rather than just applying the standard filters to the column headings.
I have attached a sample workbook : DummyData.xlsx
I got the attached dashboard example from a source online.
I was wondering how they linked their pivot chart with a drop down (data validation). This is the pivot chart on the bottom right that is labeled "Brick Styles".
Dashboard_Your_Scorecard_Complete_2010-1.xlsx
I've had a search on here, but can't quite find what i'm looking for. I have a cover sheet with a drop down list which has customers in it. under it I have the customers address, phone etc. What I am trying to do is when I click on a particular customer, I want the address and phone numbers to all fill in automatically. I have tried a few things, but to no avail.
View 12 Replies View RelatedI am trying to attempt to create a payroll spreadsheet with certain aspects and with multiple worksheets. so what im trying to accomplish is this
1/ on my main worksheet would have the payroll template there would be 2 columns (1) would be "routes" (2) would be the dollar value of that route.
2/ i want to be able to pick from a drop down list in colume (1) which will have various routes such as A,
B, C, D, E etc
3/ once i have selected a route in colum (1) i want to have the dollar value that is associated to that route to show up in column (2)
ex/ route A worth $1
route B worth $2
create a dropdown list but thats about it, i have not been able to link any values together.
So I have a list of names, and I need to keep track of whose paperwork I have. There's a column that says Yes/No.
I would like to link everything to another sheet in the file. So for every name that says "No," I would like that name to appear in the next sheet. And when someone updates the first page to put in a "yes," I want it automatically taken off the second sheet.
So.
I have Sheet 1:....
I receive daily reports from the people that work for me. The files are
saved in a known location and a known naming structure (initials - date.xls)
In a summary report I CONCATENATE their initials and the date along with a
path but cannot get the INDIRECT command to recognize the text line.
This is what i have tried in a forumla:
=IF(ISERROR(IF(Search1="","No Place Found",INDEX(LocationsANDLocations2,MATCH("*"&Search1&"*",LocationsANDLocations2,0)))),"No Place Found",IF(Search1="","No Place Found",INDEX(LocationsANDLocations2,MATCH("*"&Search1&"*",LocationsANDLocations2,0))))
But its not finding anything on the second table, being LocationsTable2.
LocationTable is about 65500 rows, and next to it another row of 65500.
What is the best method of linking the them, so it can search for matched text in each cell, so it looks in LocationsTable then LocationsTable2 and so on.
I am currently working with a hidden sheetname "hide_template" which contains formula linking to another sheetname "rawdata" of same workbook.
In this "rawdata", the contents is updated from time to time.
The problem is when I open this workbook and update the entries in "rawdata", the formulas/results in "hide_template" is not updated and invalid (ie. #N/A, #VALUE..)
This is the overview of process that Im working with:
===> open workbook ===> paste new entries on "rawdata" ==> run a macro to copy range in the "hidden_template" to paste to new sheet of same workbook
I already have a working macro to copy range from the "hidden_template" which works as follows:
===> set to visible the "hidden_template" ===> copy range from "hidden_template" ===> paste to new sheet ===> set to hidden the "hidden_template"
In worksheet 1 I have a list of employee numbers (column A) and and names (Column B)and in worksheet 2 I have a list of employee numbers (column A) and their payroll contracts (Column B). 1 employee can have multiple contracts).
What I'm trying to achieve in workshet 1 is to have a dropdown list ( Column C) which returns just the contracts that relate to the employee number in column A.
I am using Excel 2010 .I have set up Data validation for a dropdown box so I can select from a list of items. In the old versions of Excel the actual drop down arrow used to appear in each cell. In the version I have, the drop down arrow only appears when you select the actual cell. When I did the validation I checked the " In-Cell Dropdown", but it still doesnt put the arrow in the cell. Is this functionality available in Excel 2010 ?
My second issue is a formula.
The last name is in a list of items and users have to select Yes or No to theitems on the list. I am wanting to create another spreadsheet that automatically populates based on their responses.
In short, I want to be able to set up a rule or formula that states if the answer in column A is "y" then I need the information in column B to be displayed.
The ultimate aim is to get a automatic sub set, (in another tab), of the orginal information based on users responses.
I have two lists ("fulllist" & "code" - A1:A20 is a data validated dropdown list of "fulllist". I want B2:B20 to return a list item offset one column to the item selected in A1:20
So, if A:1 drop down equals "asphalt paving" I want B:2 to insert a code from a list on cell right of asphalt paving.
Something like =IF(B31<>"fulllist",=OFFSET("fulllist",0,1,,),"") This returns an error. I have a feeling I need to use match. I can't seem to find an answer to this and I bet it is fairly simple.
At the moment if a pupil has done foundation listening then I write their numerical result in, for example C3 or if they have done higher listening I write it in F3 (and it works out the UMS and then the grade), but I wondered if there would be any way to just put a dropdown menu in a column for each name, or change it so that I can type in H or F that will change which data is used? Because the UMS and grades are different depending on whether the pupil has done higher or foundation I am unsure how to format it
View 8 Replies View RelatedI have a dropdown list (C1 on the dashboard sheet). When January is chosen in that list I want the value in D15 on the dashboard sheet to match the value in B13 on the Target sheet...and then the same for D16 on the dashboard sheet and B13 on the Actual Sheet. When February is chosen the values should match for the month and so on.
Essentially I want the drop down list selection to populate the right numbers based on the month chosen.
TDCG Dashboard.xlsx
I want to perform a calculation that is based upon user selection from a drop down list. In a simple for instance it would be something like:
Cell A1 has a drop down list containing 'Apples' & 'Oranges'
Cell A2 asks for quantity
Cell A3 needs to have a formula that calculates the price based on the user selection from A1 with each option having different prices (prices set and do not require manual input)
Would an 'If' statement cover this and, if so, what would it be?
Sheet 1 has a table of prices. There are 7 columns labelled 2 thru 8 and two rows, labelled 1 and 2. Each combo has a separate price.
Sheet 2 has a weight specification (1 or 2 referring to the rows in sheet 1) and a drop down menu with options 2 thru 8.
When I choose a number from the drop down menu, let's say 5, I want to populate another cell with the value from sheet 1 of column 5 on row "whatever the weight spec is", let's say 2.
Im creating a spreadsheet at work, which has a drop down box with the options of open and closed in it.
I also have a column for length of time of the claim, what i need to do, is now find a way of getting a average length of a claim, but only to see the closed option from the drop down box.
for example,
row 1
open date - 02/06, closed date - 01/07, dropdown box - closed, life - 29
row 2
open date - 10/06, closed date - n/a, dropdown box - open, life - -39400
as you can see from the example above, i cant simply do a average of the whole column, as would read the open ones as well. so is there a way of doing the formula, but telling it to only see the open, or closed ones?
If I have a table with, For example:
dates in the first column, and an A,B,C,D,E, or F in the next.
Is there a way that you can have a cell that will tell me how many A's, C's, and F's (summed) are in the current selection, when someone uses the dropdown to narrow it down to one date?
I have a table in cells A1:B11. I have two drop down menus in cells G3 and H3. When I select red in G3 I need Bob, Sam and Sarah appear in the drop down menu in cell H3. Instead, with my current formula, only Sarah appears in the drop down in cell H3. When I select green in G3 I need Tommy, Bob, Sandy, Carl and Phil appear in the drop down menu in Cell H3. Instead with my current formula only Phil appears in the drop down in cell H3.
I linked cell H3 to the defined name Names which equals
OFFSET(INDEX(Sheet4!$B:$B, MATCH(Sheet4!$G$3, Sheet4!$A:$A,0),0),COUNTIF(Sheet4!$A:$A, Sheet4!$G$3)-1,0).
What should I add to this formula to make it work correctly?
ABCDEFGH
1RedBobRed2RedSamGreen3RedSarahBlueRedSarah4GreenTommy5GreenBob
6GreenSandy7GreenCarl8GreenPhil9BlueCarl10BlueSam11BluePhil
Is there a formula to calculate the median of a dropdown (Alt+DFF) selection?
E.g.:
a
1
b
2
c
3
a
4
b
5
c
6
a
7
b
8
c
9
If I select a the answer should be 4, c should be 6.
I have a spreadsheet where there is an option to either select a pre-priced item from a drop down list or type in manual entries. If the data is entered manually, I have included a formula to obtain cost data. For example, column A has the number of labor hours and column B multiplies Column A by $500. However, if a person chooses to select a pre-priced item from the drop down menu, can the pre-entered data from the VLookup list overwrite the formula? I do want it to overwrite if it is possible for it to do so.
View 2 Replies View RelatedI have an excel that has dropdown column from form control, in F4 there is a formula of vlookup that take the range from A2 to B20 in consideration but the reference cell that is given to vlookup formula to pick corresponding value is E4 which is also the reference cell of dropdown box and therefore its not showing any name rather than a value, problem with the formula is that when we select any name from dropdown it does not respond to any value in F4 despite changing values in E4.
I am using these setting for the first time.
Image & excel file is attached
drop down.png
I have created a drop down list for items we quote on. each item has a different formula depending on the variables on sheet one Sheet1. I have listed the formulas in the column next to the drop down list for each item, but our now need the main quote to put in the formula in another cell when the item is chosen from the drop down list.
View 2 Replies View RelatedNeed formula to calculate the average %age in the attached spreadsheet. I would like to enter a score between 1 and 4, but with 1 = 10%, 2 = 25%, 3 = 80% and 4 = 100%. The score in the cell must still show as between 1 and 4 but the total must be an average of the relevant %ages. i.e. if scores are recorded as 1, 2, 3, 4, then the total average % will be (10%+25%+80%+100%)/4 = 53.75%. I'm not sure whether this should be in the Validation or in the Total cell.
View 2 Replies View RelatedI am creating a excel calculator to quick and easy calculate CBM (cubic metres) for a total number of cartons and pallets loaded into a container. The value of 1 carton or 1 pallet will be different depending on which type of packaging/pallet it is.
I have created drop down menues in order to choose the correct packaging / pallet. But I am stuck on on to create the formula.
If I for example choose "LANDFROZEN 7KG CARTONS" from the drop down menu, I need the formula to collect the value in cell E18
If I for exampel choose "LANDFROZEN 10 KG CARTONS" from the drop dwon menu, I need the formula to collect the value in cell E25 .... and so on.
I have tried to use several functions, but none is working the way I want them to :-)
Basically on a summary page, on sheet 1 say I have a formula which picks up the unit cost (I have this as an array across 5 years)
What I then want to do is multiply this by a volume driver. I have a list of these drivers on a seperate page, so for convenience lets say DRIVER_01,DRIVER_02 etc. These are also 5 years arrays.
So what I want to do it be able to select which driver I want to use for volume on the summary, from a drop down box I have set up, and then the formula pick up that named range, and not just recognise it as a text cell.
I need the ability to change the driver in the future, hence the drop down box. I can also get the unit array part to work fine, I'm just struggling to get this driver array to work..............
I have a list of Names that has numbers accumulated over months. I am looking for a formula to use in a search tool that uses a drop down list to identify the name I am looking for and then the formula will show me the increase that happened in certain months.
See the attachment the result is coming from Denominators tab, I want the answer displayed in the Display tab, you will see a drop down list at C3 in Display, I am trying to show the answer in cell D9-I9.
how many the denominator has increased in that month. so for D9 it will come from denominator tab column c minus b, E9 will be d minus c