Creating Dropdown Which Changes Adjacent Cell Data?

May 16, 2013

I'm revamping a key inventory for my company and would like to be able to track the history of each key (name of person who it was issued to, date issued, date returned). I figure the best way to do this for the 90 keys I currently have in circulation is to create drop down lists for each key that has been used by more than one person in its history and upon selecting a given person from the drop down list of keys used more than once, the adjacent cells would populate with the corresponding data of date issued and date returned. Some keys have not been used more than once so not every key will have a drop down. Here is what my key inventory spreadsheet looks right now.

View 3 Replies


ADVERTISEMENT

Populate Data In Non-Adjacent Cells (From Dropdown)

Mar 27, 2013

How to populate data in non-adjacent cells from information that is stored on another sheet. Sheet 1 is essentially setup to be visually pleasing to the user, but the data stored behind it is on another sheet stored in a more logical manner. I would like to auto-populate the nicely formatted sheet with information from sheet 2 when an item from a drop-down list is selected. I have found quite a few ways to do this using offset, etc. but in my case the values will be pulled into cells that are spread around the design sheet in no set order.

View 3 Replies View Related

Comments In Dropdown Box Dependent On Number Selected In Adjacent Cell

Jul 8, 2013

I have been asked by my supervisor to make a performance review template a little bit more user friendly.

Basically what I have is a drop own list in cells in one column (various cells with the same drop down menu) that you select from either 0-10 in a drop down box.

I have another cell which I want to have a drop down box with text comments that you select from when. Only problem is the comments different dependent on the ranking score you give a person (the number selection in the previously mentioned cell from 0-10). So if you have a score between 9-10 in the cell then the comments box will give you certain comments to choose, 7-8 different comments and so on and so forth.

View 1 Replies View Related

Data Validation - Creating Subset Choices Based On Adjacent Column Choice

Oct 27, 2013

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

View 3 Replies View Related

Creating Dropdown Menu For Data Extraction

Nov 30, 2012

Creating a drop down menu that allows me to pull data from sheet2.

I have all of my data on sheet2 and my objective is to have a small portion of the data displayed on sheet1 once the user selects the specific "study" from a drop down menu.

how to create a drop down menu that will have a list of all the different studies, and then once the specific study is selected, how do I make it display the pertaining information in the master sheet.

Follow up....more information

The data I’m working with is a master list of different drug studies. All of the data is broken down by the specific study itself, so all I need to do is figure out a way to let the user access the data in a quick and easy way. I figured this could be down with a drop down menu that has a list of each study, and the user could select the study they want to review. Once selected, the data pertaining to the study would then populate the fields on the master silde(sheet1).

View 9 Replies View Related

Data Validation - Creating Dropdown Menu With Values

Feb 2, 2013

I have a list of values (say a, b, c, d, e) and creating a drop down menu with these values in say A1. Say I chose 'c'. Now in cell A2 I want to be able to choose from the same list without being able to choose 'c' again. In cell A3 I want to be able to choose from the same list without being able to choose the values lalready chosen in A1 and A2. and so on.

View 2 Replies View Related

Creating Dropdown List That Returns Defined Numerous Columns Of Data

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

Creating A Dropdown List Using Matching Data From 2 Columns To Populate The List

Jun 11, 2013

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

View 7 Replies View Related

Populate Adjacent Cells From Single Dropdown

Aug 10, 2009

I have the need to create a spreadsheet that will populate multiple fields in the spreadsheet based on a drop down selection, i.e.:

In A1 I will have a drop down with 300 items. When an item is selected the next four cells will populate with predetermined data. Example:

For each person I have information that applies only to that person like so:
Bob Red Hair Blue Eyes 6' tall California
Jan Blond Green 5' tall Florida
Ian Brown Brown 5'5" Texas


If someone chooses bob from the drop down, I need the next four cells to display the information relating to bob.

View 9 Replies View Related

Conditional Formatting Of Adjacent Cells From Dropdown List?

Jan 9, 2013

In one column I have a drop down list of a dozen different initials with conditional formatting on those cells. I would also like to conditionally format an adjacent text string column based on those initials, but don't want to set up a dozen rules on each cell, one by one. If I set up the top cell with the correct rules I cannot fill the formatting in because the formula is not updated on a row by row basis, but en bloc for the range.

View 3 Replies View Related

Automatically Updating Adjacent Cells Based On Dropdown List Selection Even If Changed

Apr 12, 2013

I have a workbook that has two worksheets. One worksheet is an input worksheet. A user will select a date from a drop-down list and type in the events that occurred on that date in 8 adjacent cells. The user selects a button that advances date and clears form. On sheet 2, whatever was typed into sheet one is saved via VLOOKUP formula. However, if I try to change something retroactively and select a previous date from the drop-down list, it clears everything in sheet 2 that was typed for any selected day.

View 1 Replies View Related

Macro - If Adjacent Cell Contains Any Data Then Copy Data From Another Cell?

Jul 15, 2014

I wondering if it is possible for a macro to recognise whether or not there is text in an adjacent cell, and if there is, then to copy data from another adjacent cell.

For example, because there is data in C2, data from A2 has been inputted into B2. And any blank cells are left alone.

a
b
c
d
e

ref1
(cell A2 data here)
DATA
DATA
DATA

ref1

View 5 Replies View Related

Macro To Copy Data From One Cell To Another If Adjacent Cell Meets Certain Requirements?

Feb 19, 2014

Basically I have three columns in a work Sheet F, G, & H. F is empty, G contains text and column H has both text and numbers.

I want to be able to automatically copy the value from Cell H to Cell F if cell G contains the word cost.

I would also like to delete all rows where Column G & H contain two dashes -

View 7 Replies View Related

Excel 2010 :: Locating One Cell Data In Text Of Adjacent Cell?

Jul 1, 2013

I am using Excel 2010.

In my worksheet I have 'Column A' and 'Column B', In 'Column A' are product I.D. numbers. In 'Column B' is a text description of the product, whose I.D. number is in 'Column A,' and should also contain the I.D. number from 'Column A' somewhere in the midst of the descriptive text. However, some of these in 'Column B' do not.

I need to create a function that looks for the value in 'Column A' and determines whether or not it is present in the text of 'Column B'. Therefore, spitting the answer out in 'Column C' so that I can copy it down for 100,000 cells.

View 5 Replies View Related

Getting Data To Display From An Adjacent Cell

Sep 7, 2009

I am trying to analyse a whole heap of data generated by an image analysis package. The data is exported out in to excel, I then need to be able ot rearange it a bit and export it out to another program.

I have attached a smaller version of the data as an example. The example data set only contains 3 image planes with ~100 entries for each. A full data set contains 96 planes and ~2000 entries each.

So what i want to do.

I want to setup a workbook i can dump this data into and have it spread everything out over 96 different sheets (1 for each plane).

I can get it to do it for the first lot of data using a IF command like =IF('Object Data'!B:B=1,'Object Data'!D3,)

But that only really works for the first plane, as the starting cell for plane 2 is always going to be different.

So what i need is a formula that will say: look at the plane column, if it equals a certain value print out the value for the other cells in that row.

Is that possible?

View 11 Replies View Related

Creating Smart Dropdown List

Apr 6, 2011

I have a drop down list in excel. What do I need to do so that it will automatically take me to the closest/best label on the list when I type just a few first letters?

View 14 Replies View Related

Creating Dependent Dropdown Lists?

Sep 23, 2011

I am trying to get a listbox I created in from DataValidation to be dependent on another list.

For example, when I select Course 1 (a list item) from the Course List drop-down, I want the cost to auotmatically populate in the Cost drop-down (the other list).

How do I get this to happen? Here is the code I created in VB, and I am not sure if this is the right course of action:

Sub Automated()
Sheet1.Cells(2, 1) = "MATLAB"
If (Sheet1.Cells(2, 1) = "MATLAB") Then
Sheet1.Cells(2, 3) = "31"
End If
Sheet1.Cells(2, 1) = "INCA"
If (Sheet1.Cells(2, 1) = "INCA") Then
Sheet1.Cells(2, 3) = "41"
End If

[code]....

I don't want to use the user form, however from VB. I want to use the regular drop-down. The code seems to work, but, each list item in the drop-down is not pulling the data I want it to. I need to activate the drop-down as a click event somehow. It's just recognizing the cell A2 as a whole and not the individual list items. I want to each list item to be their own object, and to automatically populate the cell C2 with their cost when they are selected.

View 3 Replies View Related

Creating Custom Dropdown In Toolbar

Jun 6, 2007

I work with excel docs that have alot of sheets. I managed to create a vba form that has a listbox populated with the sheet names, that when clicked goes to the respective sheet. The problem is that when the form is open i cannot focus on the excel sheet - the form needs to be closed first.

1 ) Is it possible to make an excel vba form dockable (so it could be displayed on the right where help would be)

Or

2) Would it be possible to create a drop down in the toolbar that would have the sheet names.

I think the latter is a cleaner solution (if possible).

View 9 Replies View Related

Using The .find Method And Getting Adjacent Cell Data

May 15, 2009

Until now if I needed to find a cell's data (and its adjacent data) I've been using code in the format of (for the purpose of this example, you can assume all data in coulmn 1 is unique. Functioning as aprimary key using incremeting integers):

View 3 Replies View Related

VBA Macro To Move Data To Adjacent Cell

Mar 26, 2013

writing a Macro which will move data from a cell to an adjacent cell.

E.g Column B has only data in certain cells, I would like the Macro to work down the column and every time it encounters data in Column B to move it into the adjacent cell into Column A.

View 4 Replies View Related

Creating Dependent Dropdown List NOT Using INDIRECT

Jun 20, 2014

I need to create a data validation drop-down that is dependant on what has been selected in another drop-down. However, all the solutions I've come across online so far suggest using the INDIRECT function, which is not appropriate in my case. This is because the data upon which the two drop-downs are based on is itself dynamic. My specific situation is this:

In one sheet, I have a list of university names in one column, and in the next column a list of faculties. The same university can appear multiple times in the sheet (once for each different faculty at the university). This information will be added to regularly, with new universities and new faculties added often.

In another sheet, I want a drop-down in one column that takes its validation from the list of universities. I then want a second drop-down that is only populated with a list of faculties at that university. The INDIRECT solution seems to only work if I can define a static range of cells from which to draw this data. How do I do it in my situation above?

View 3 Replies View Related

Getting Data In Adjacent Cell To Calculate Data

Dec 17, 2012

I want the user the be able to pick a number. This number corresponds to a table value. The formula needs to take the value in the adjacent cell from the picked number and run a calculation on it.

For example, if the user picks 7, it will select the adjacent value to 7 in a table for use in a calculation in another cell.

I attached an example : example_001.xlsx‎

View 3 Replies View Related

Dropdown Lists Data Went Selected Won't Show In Next Dropdown Only Remaining Data

Mar 11, 2013

I am trying to pull data from more than one drop down but don't the same data to show if already use, example as follow:

First drop down Contains:- Pants
Shirts
Hats
Shoes
Dresses

if I chose Hats it should not show up in the second drop down

second drop down Contains:- Pants
Shirts
Shoes
Dresses
Is this done in data validation or combo Box? if so how?

View 2 Replies View Related

Find Values On One Tab And Replace Data From Adjacent Cell

Apr 3, 2014

I have one sheet in my workbook called 'mapping' which has a list of codes on it, the length of which will vary on a periodic basis.

I have another fairly large sheet on the workbook called 'data' (around 2000 rows) that will also vary in size. I want to do a search on the data tab for each code that is contained on column 4 of the mapping tab and if the code is found, enters the corresponding value from column 1 of the mapping tab to the cell 6 columns to the left of where the code was found on the data tab (cols H and B in this case).

The issue I have is the codes maybe contained more than once on the data tab so I need the find/replace command to search the whole of the data tab and perform the task each time.

View 2 Replies View Related

Prohibit Data Entry Based On Adjacent Cell

Mar 14, 2013

I have the following formula in cell H2:

=AND(EXACT(G2,UPPER(G2)),(LEFT(G2)>="A")*(LEFT(G2)<="Z")*(MID(G2,2,1)>="0")*(MID(G2,2,1)<="9")
*(MID(G2,3,1)>="A")*(MID(G2,3,1)<="Z")*(MID(G2,4,1)>="0")*(MID(G2,4,1)<="9")*(MID(G2,5,1)>="A")
*(MID(G2,5,1)<="Z")*(MID(G2,6,1)>="0")*(MID(G2,6,1)<="9")*(LEN(G2)=6))

I would like to prohibit data entry in G2 if H2 reads "false" and allow data entry in G2 if H2 reads "true" .

View 2 Replies View Related

Fill In Blank Cells If Adjacent Cell Has Value Then Do For Next Set Of Data

Feb 19, 2014

I'm trying every conceivable angle I can think of in vba and coming up empty. What I have is a spreadsheet similar to this:

Column A Column B

1. 1234567
2. 2345678
3. 3456789
4. 4567890 A0001
5.
6. 9876543
7. 9876543
8. 9876543
9. 1234578
10. 3456789 A0002

What I would like to do is to fill in B1:B3 with A0001 and B6:B9 with A0002. The number of rows in each set of data will vary, but the identifier (A000X) will always be located in the row in column B adjacent to the last entry for that set of data in column A and there will always be empty cells between data sets. The number of data sets will also vary.

View 5 Replies View Related

Extract First Data From One Column When Adjacent Cell Is Empty?

May 15, 2014

I have two columns,
Name
Marks

Amit
45

Ankit
54

Aditya

Arun

Ayush
43

Amar

Akas
32

Akshay
21

I want to extract first data from column Name where in the data corresponding to it in Column Marks is blank, and if that is filled, i want the next data to be shown automatically

View 9 Replies View Related

Creating SUM In Table Based On 2 Selections From Dropdown List

Aug 19, 2014

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.

View 1 Replies View Related

Creating Dropdown Validation Menus With Reduced Ranges?

Apr 1, 2009

i have a table of tools and their properties which i am using to provide validation lists for a number of other worksheets that record when those tools were last calibrated/tested and when they need to be calibrated/tested next.

many tools require more than one form of testing (electrical and/or calibration), but not all tools require both.

can i create some kind of drop-down menu the looks at the tool master list, but only gives the option of those that require the particular type of testing i'm recording on that particular list? how can i create a drop-down list that is more limited than the range i have selected for it to display?

View 10 Replies View Related

Creating Dropdown Menus With Extensive Changing Content

Jun 11, 2013

I have a problem (I have intermediate knowledge of Excel): lets say that I have two model years for Cadillac: 2013 and 2014, and have 3 car models for year 2013 (ATS, XTS, and CRX) and two car models for year 2014 (ELR and CTS Sedan). I want to create a dropdown menu under "Model Year" that will show me 2013 and 2014. When I click on either one, a dropdown list for all car models for that year can appear in the next column under "Models". Then, for each car model, there are different characteristics that are in other columns, such as horsepower, mileage, etc...I want all of the content to stay on one line (row), but change when I click on a different model.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved