Dropdown List Created From 2 Columns Of Info?

Mar 24, 2014

I have a list of names in column B, I can easily put this into a list

I also have a different list of names in Column C

These two list are feeding from sharepoint and are updated regularly

What I want to do is create a drop down list that will list all the names in Column B and Column C, while keeping the initial lists in there separate columns

I have been trying to find a formula to lookup both columns and return all the names into another column (E) using the formula below

=IFERROR(INDEX($B$2:$C$600,MOD(ROWS(E$2:E2)-1,ROWS($B$2:$C$600))+1,INT((ROWS(E$2:E2)-1)/ROWS($B$2:$C$600))+1),"")

the plan been to just put column E into my drop down list, but this formula is returning alot of blanks between Column B + C in the list (there might be 100 names in column B and only 30 in column C)

To try remove the blanks I tried another formula pulling the info into Column F,

=IFERROR(INDEX($E$2:$E$5000,AGGREGATE(15,6,(ROW($E$2:$E$5000)-ROW($E$2)+1)/($E$2:$E$5000<>""),ROWS(F$2:F2))),"")

But while this did remove the blanks (about 500 blanks) it left me with about 70 "0" errors.

View 3 Replies


ADVERTISEMENT

Dropdown List In Spreadsheet Which Combine Info From Two Columns

Oct 24, 2012

I'm trying to have a drop down list in a spreadsheet which combines the info from two columns.

For example:

A B
1 John 29
2 Sandy 40, etc

And the drop down list turns into

John 29
Sandy 40

Is this possible?

View 1 Replies View Related

Data Validation - When New Row Created / Dropdown List Automatically Creates

Apr 7, 2014

I'm trying to create a spreadsheet that has a data validation in it. I have a drop down list that has to options to select; Yes and no. Every time I create a new row to add more information I have to go through the process of creating a new data validation for for each cell in this coloumn.

Is there anyway of creating a way that every time I insert a new row, a data validation list box will appear in the coloumn that I want it in?

Sample Workbook: datavalhelp.xls

View 3 Replies View Related

Macro To Copy Dropdown List Created In Cell O2 And Then Paste In Same Sheet

Feb 12, 2014

I am trying to write a simple macro to copy a drop down list I created in cell O2 and then paste in the same sheet. I need it to paste starting in cell A2 and then move to A4 and so on for every even row cell through A9778. I would also like it to keep a border around the cell. I can't find direction on how to tell it every even cell or defining x and then doing x + 1...

View 3 Replies View Related

Excel 2007 :: How To Combine Info From Columns Into 1 List

Oct 4, 2011

is there a way to combine the info from several columns into 1 list?

All of my columns have information in rows 1-20, but I want it all to automatically combine into a list in column B on a new sheet.

This is Excel 2007

View 9 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

List Values From Multiple Columns In A Dropdown List

Dec 17, 2012

how to list values from multiple columns in a dropdown list based on lookup value of 1st column as below.

This is how the table looks like.

Product MOLD1 MOLD2 MOLD3
4" AB1 AB2 AB3
6" ZA2 zd4 -

This is how the dropdown list should look like for Product 4"

ab1
ab2
ab3

View 6 Replies View Related

How To Fill Vertical Columns With Info From Horizontal Info

Aug 22, 2014

I have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g

The info in these columns at present has been manually entered but I am sure it could be automated.

OOL Roster Final 18-31Aug14.xlsx

View 1 Replies View Related

Dropdown List From Multiple Columns

May 28, 2013

I am trying to make a single drop-down list which contains values from two columns.

I should give you the example immediately:

NAME | SURNAME (these are two columns)
Jon | Alfa
Karl | Beta
Lilly | Wolf

And the drop-down list should look like this:

DROP-DOWN
Jon Alfa
Karl Beta
Lilly Wolf

I have already tried to solve my problem with data validation but with no success.

View 2 Replies View Related

Adding Multiple Columns For Dropdown List

Mar 5, 2013

I'm trying to set up a billing form for my work. I'm trying to include drop down list for each sections (personnel, equipment, and supplies), then all sum together as a bill. I'm not sure if this is doable in excel.

View 9 Replies View Related

Making Dropdown List In Two Consecutive Columns

Jun 26, 2013

I have a table where I need to make a drop down list in two consecutive columns (G and H). The list in column H will depend on the Option chosen in Column H.

Column G
Compensation

Payroll

Column H
COMPENSATION

Savings Box

Update Information General PAYROLL

Payment Inquiry Update Information Severance Pay

As shown above If you select Compensation in G you will have those three options in column H and the same for Payroll.

View 2 Replies View Related

Calling Multiple Columns / Rows From Dropdown List

Feb 4, 2013

I have a drop down list, using data validation, calling from a range name on a different sheet($1). Works great. I see my list of choices.

What I want to do next is for another named range on another sheet($2) to be called when I select one of the options from the drop down.

So say the drop down is in I71, when I pick an option from that list it will populate (B72, B73:G73, B74:G74...etc...) from a named range I have already defined on ($2).

Where would I put the (if I71=Range1, then paste this info into the (B72, B73:G73, B74:G74...etc...) on $1 ?

View 3 Replies View Related

Hide/unhide Columns Based On Dropdown List Answer

Sep 26, 2008

I am a newbie to Excel. I really appreciate if someone could help me here and this is very urgent as I have a project going on.

I have a cell say B3, which is a dropdown list I created using Data Validation. It only lists down Yes or No.

What I need is when you click on Yes in B3, i need columns C to I to unhide. If answer is No, column C to I should be hidden. By default, the columns will be hidden.

Can you please help? I tried many times but not successful. Appreciate if someone will be able to walk me through details. If you want to see the sample spreadsheet, I can sent it to you by email. I tried Data> Outline group but they are not happy with this.

View 9 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

Hide / Unhide Columns In Multiple Worksheets Based On Item Chosen In Dropdown List?

Feb 26, 2014

I am hoping to create a drop down list of months in one sheet, and when I select a certain month, columns in about 10 other worksheets in the same workbook will either hide or unhide columns...

The spreadsheet is laid out with columns (C-N) for each month in the year, for actuals, then columns for budget and budget variance (O-P), then YTD Actual, YTD Budget and YTD Variance. When I select September, for example, I want October-December to hide, and leave Jan-Sep unhidden, while keeping the budget, YTD and variance columns.

Is there a VBA code that can achieve this?

View 14 Replies View Related

Created Validation Drop-down List - Can I Make The Row Now Change To The Colour That Relates To The Selection From The List

Jan 13, 2009

I am trying to create a fairly simple spreadsheet with about 8 columns and about 400 rows. One of the columns features a drop-down list with about 8 or 9 different options. Dependant on which option is selected, i would like the entire row to change colour with that option.

For example:
FAILED - whole row changes red
SUCCESSFUL - row has no fill
Tested - row changes to orange

etc.

Is this possible within Excel 2003?

View 9 Replies View Related

Combine Rows With Similar Info In Dropdown Menu Or Selection For User

Nov 29, 2012

I have a spreadsheet that has a long list of charges... some of those charges have multiple codes attached to them... when this happens I am wanting to group them together and let the user pick which one they want to view at one time.

Here is a very small example of what exactly I am referring to... with charge #14 I am wanting it to combine and let the user pick between 2565 and 2566. The problem is that the values can range and will not always be 2565 & 2566 and there is 10,000 + records similar to this.

test.xlsx

View 1 Replies View Related

Random Name From A List That I Have Created

Apr 5, 2008

How do I get a random name from a list that I have created? The random feature only deals with numbers. I want to be able to push F9 or click on something and have a cell randomly pull a name from the list.

View 9 Replies View Related

List Folders Created By Procedure

Feb 22, 2010

I have the following procedure that creates sub folders for items in a named range (Room) in the parent folder (Pictures). The procedure works as expected; a sub folder is created for each item in the named range. What I'd like to do is give the User feedback as to what sub folders were created. This is the code I'm using:

View 3 Replies View Related

Macro To Automatically Add Totals To The Bottom Of A List Created With Advanced Filter

Mar 5, 2008

if it is possible to write/record a macro that will automatically add a summary/total row immediately below the data that is created from an advanced filter. (XL00)

View 12 Replies View Related

Counting Info In Columns?

Jul 25, 2012

I have two columns that represent constituents from a stock indices from two different years. I would like to find out what stocks appear in both years.

View 1 Replies View Related

Auto Input Info From A List

Feb 10, 2009

I am having difficulty with this formula. In the attached spreadsheet. I want to just input the item number in column A, manually. From there I want it to take the information from the hidden list and fill in the rest of the fields. Example. I input in A4-2664, I want it to search the list for 2664 and take the corrisponding information. It would take the information from M5, O5, and Q5 and place that in C4, E4, G4. I place spaces in the hidden list to make the format the same.

View 2 Replies View Related

Dropdown List From Comma Delimited List In Single Cell

May 9, 2014

Is there a way to create a drop down list from a comma delimited list in a single cell? For example, col A is Name & Col B is the delimited list - Blue,Red,Green (list can be different for each name). Would like a drop down list in col C that allows you to pick one of the values from Col B.

View 3 Replies View Related

Getting Conditional Formatted Dropdown List Items To Keep Their Formatting In List

Feb 12, 2014

I found code online that I can put on my sheet to get my formatting properties to stay the same for the items in my dropdown list located on another page. However the code does not work for conditional formatted cells...which is what I need. This is the code that I have that will carry over regular formatted cells. Just not Conditional formatted cells.

View 1 Replies View Related

How To Get Unique List From A Column To Data Validation Dropdown List

Oct 28, 2011

I'm looking for a way to get a unique list from a column to a data validation drop down list. Any fancy formula or vba script to create a UDF which. Does this?

View 5 Replies View Related

Detecting Duplicate Cells Using Info From The List

Feb 5, 2014

I have a sheet containing lots of information (lists of flight information to and from all countries). So entries are duplicated in the entire sheet. For example LITHUANIA, USA, CHINA... appears lots of times. I only need to highlight particular countries.

I would just use conditional formatting, but maybe there is a quicker way to do that?

So there is a list of 50 countries in one sheet and in another sheet I got all the info. I need those 50 countries from the list to be highlighted in the info sheet (sheet has many columns and rows).

View 6 Replies View Related

Need To Pull Out Specific Info From Mixed List

Apr 3, 2014

I pulled a report into excel that lists staff details and workgroups that they have access to. There are nearly 8000 staff who can have anywhere from 0 to 120 workgroups.

The workgroups are listed with the staff details in the following format:

SurnameForenamePost Title183860314040|188778743040|261226948048|584865373040|088365861041
SurnameForenamePost Title695416612049|751836367043|430463930049|461208099048|488798547045|723225723045|183860314040|472108996043
SurnameForenamePost Title177448555041|315136549047|400323457046

With the whole list appearing in one cell and not in numerical order.

I need to pull out a list for each workgroup, with all the staff who have access.

Even if there was a way I could separate out the workgroups and get them to line up.

View 2 Replies View Related

Limit Drop Down List And Linking To Other Info

Feb 1, 2006

I have set up a spreadsheet that requires info to be inputted into cells. I have created drop down lists in the cells to only allow certain information to be inputted.

Allow user to use a drop down list and pick the appropriate entry, then have another drop down list in the adjacent cell that only contains items that correspond to what the first drop down entry was (i.e. company name in first drop down box, adjacent cell drop down list only contains parts made for that company). Then I would like the next column to contain the pricing of the item that was chosen in the previous cell's drop down box. Ideally, this cell would be populated automatically depending on the part chosen for the company. I am trying to create a log of parts shipped and total amount shipped.

View 13 Replies View Related

Getting Info From A List And Input Then Transferring Data

Mar 24, 2007

a user inputs the drawing number and revision in a box on the first sheet in the workbook. Once that is done, the user then selects the type of drawing from the drop-down menu, once "Submit" is clicked it takes that info and puts the information in the next available cell for the other worksheets (if that worksheet calls out for that certain part).

I've been having trouble trying to figure out how to be able to accomplish this task.

If you have any ideas, that'd be great.

I've attached a basic worksheet I drew up to give you a better idea of what I'm looking to do.

Forgot to mention that there are totals involved! The "destination" worksheets have set totals for each drawing type, so what also has to be taken into consideration is that the specific drawing needs to have the correct total beside it. I created a "Totals" worksheet to show what drawings are what totals for each cell. Realizing this may always change or be updated, I created a seperate worksheet to pull all the numbers off of. I thought this might be easier rather than going in and changing the coding each time.

View 9 Replies View Related

Text To Columns Fields Info Array

Nov 23, 2009

I wanted to replace Array(Array(1,1),Array… in TextToColumns.

http://www.ozgrid.com/forum/showthread.php?t=140770
http://www.excelforum.com/excel-prog...o-columns.html

View 9 Replies View Related







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