Dependant Lists Using Text Values Not Listed In Worksheet

May 28, 2009

I have seen in the forums where lists are created by refering to column values in other worksheets.If you create a list by entering text values can you make other lists dependant to those values and can those lists also be made up of text lists?

All the examples I have seen here use lists that exist in some other worksheet

I use lists which use text values directly and not from a worksheet. I'm wondering how to make a secondary/sub list dependant on each value selected so say if text value "A" is selected in list 1 then this only allows the list for "A" to be available for list 2 in its corresponding cell (the next one along)

View 9 Replies


ADVERTISEMENT

Dynamic & Dependant Lists On UserForms

Mar 27, 2008

what i need to do is display a list of items, with a dropdown box next to each item. the dropdown boxes are identical. for each item in the list (e.g. each musician), the user should be able to select an item from the box ("percussion", "horn", "string").

my problem is that the number of items in the list is not set, so the list of name labels, dropdown boxes, etc needs to be generated dynamically. is it possible to generate a list of labels/dropdown boxes using a loop? (as i understand it, this would involve writing code to write code.)

View 6 Replies View Related

Data Validation List :: Dependant Lists

Aug 28, 2007

I have a question regarding the data validation lists. What i have is 4 different lists

Department
Assays
Instrument
Manufacturer

What I want to do is...once a user chooses a department, he gets a list of assays done in the chosen dept. and also a list of instrument used by the dept and a list of manufacturers

I used the examples shown here

http://www.contextures.com/xlDataVal13.html
http://www.contextures.com/xlDataVal02.html

and was able to succeed in creating dependent lists to some extent...I am attaching whatever I have able to accomplish so far, which is able to pull up the assay list based on the dept. ...but am confused as to how to get the Instrument and Manufacturer list at the same time .....please choose 'Chem 2" from the dept list to see what I have managed to do so far

View 11 Replies View Related

Data Validation Dropdowns (Dependant Lists)?

Oct 22, 2007

I am creating a spreadsheet i want it to be that i make a selection from a drop down and this will then narrow the options available in the next drop down. Say box one you select 'half day' and then the next drop down menu will automatically loose all the data for a 'full day' and only show half day information such as costs for food and room bookings ect. i have made the drop downs but im not sure what to do next.

View 3 Replies View Related

Conditional Formatting Based On Dependant Lists

Feb 16, 2009

Example.....

In the drop-down menu in col D I select the value “Egress”, then the drop-down list in col E is populated with the values of EgressCol. Now I select a value, lets say “Cash payment”. If at a later time I change the value of col D, lets say to “Ingress”, then “Cash payment” in col E doesn’t match the values available for IngressCol (the correspondent dynamic list for the Ingress value).

I need a conditional formatting formula that checks whether the value in col E is listed in the corresponding list of the value selected in col D. Request: I found this formula that works fine, but it gets Excel to work really slow.

=COUNTIF(INDIRECT(SUBSTITUTE($D2," ","")&"Col"),$E2)=0

View 3 Replies View Related

One Data Validation List Dependant On Four Dv Lists

Sep 28, 2007

I have on a sheet:

4 data validation lists that contain the exact same values.

would like to have:

One validation list box that is dependant on all 4 vd lists.

I posted a thread on dv and came to the conclusion that it is not possible to use INDIRECT() as it only allows for one cell reference and not four.

Is there another way to do this?

Glove Man, I tried your suggestion but couldn't get it work.

View 9 Replies View Related

Dependant Validation Lists With Dynamic Named Ranges

Jun 18, 2008

in a sheet I have two cells using data validation and dropdowns - the source for the first cell is a static named range - no problems. The source of the second cell is dependent on the value in the first cell and the sources are dynamic named ranges.
The dynamic ranges in cell#2 is named according to the value in cell#1 and I therefore have referenced the ranges using the INDIRECT function - but this only works with static ranges.

In the data validation source field for cell#2 I have the following formula:
=IF($A$1="",the_full_range,INDIRECT($A$1))

View 7 Replies View Related

Excel 2003 :: Alphabetizing Names Listed In Worksheet

Sep 10, 2013

I use Excel 2003 & I am trying to design a worksheet to keep track of signs I make & their order numbers. I want the names listed alphabetically. When I add a new name & I arrange the column alphabetically, the columns with the order numbers do not relocate along with the names column.

View 5 Replies View Related

Mirror Listed Values At Start Of Other Tables

Oct 20, 2006

I have a list of names on my first sheet, and I want to mirror these on a second sheet, but at the start of a repeating table containing data releating to each named person. eg. I want a Name in

'Sheet 1'!A4

to display in

'Sheet 2'!A5

, and then the name in

'Sheet 1'!A5

to display in

'Sheet 2'!A32

View 3 Replies View Related

Substitute X Values With Meat Types Listed First Column

Jun 7, 2013

How to get a formula that will substitute every X with the actual type of meat. For instance, under Monday column, my table should list Liver, Pork, Mutton and Lamb Stew instead of X.

Mon
Tues
Wed
Thurs
Friday

Liver

X
X

[Code] ........

View 2 Replies View Related

Unique Values Should Listed In Sheet 2 And 3 Every Time When Activated

Sep 16, 2012

I have below set of value in Sheet 1 (it has category & Name), whenever I open the Sheet 2 & Sheet 3, unique values should get automatically posted in D column..

Category
Names

Pet Animal
Dog

Pet Animal
Cat

Wild Animal
Elephant

[code]....

I have two requirements on this..

1. Unique names should listed in cell D of Sheet 2

2. Unique value of both category & name should listed in Cell D of Sheet 3

View 1 Replies View Related

Lookup Query - Occurrences: Create Another Worksheet With The Months Of The Years Listed Down Column

Oct 16, 2008

I have a table that lists the months of the year down from cell A2:A13, and days of the week along row from cell B1:H1. The data in between (cell B2:H13) is pulled through from elsewhere in the workbook and is in number format.

What I basically want to do is create another worksheet with the months of the years listed down column A, and in column B, for each month, I want the first day of the week where the value in the original table is more than zero, and in column C the second day of the week where the value is more than zero. I really hope that makes sense, was quite difficult to explain!

View 2 Replies View Related

User Form - Dependant Values

Oct 7, 2008

I've been making a userform to enter data into a list of clients for our school counsellor. I've been using date picker to select the date, which works fine, but is a bit fiddly for a novice user (which the counsellor is) to jump down years.

My half-solution has been to change the data to a very rough guess of their data (so it's no more than a year or so away) - by taking their school year (years 7 to 11) in one combo box, adding 5, multiplying by 365 and taking the that number of days from the current date.

Private Sub CmbYear_Change()
DTPicker1.Value = Date - ((5 + CmbYear.Value) * 365)
End Sub
And although it changes the content of the datepicker date, I get an error when I click my ADD button.


Run-time Error '13':
Type Mismatch

And here's the code for my ADD button.

Private Sub CmdAdd_Click()
ActiveWorkbook.Sheets("Clients").Activate

Range("A3").Select

Do

If IsEmpty(ActiveCell) = False Then

View 9 Replies View Related

Change A Worksheet Tab Colour Dependant On The Contents On A Range Of Cells?

Mar 24, 2014

I have a worksheey of data and i would like the tab to be a certain colour dependant on the the contents of a range of cells within that worksheet is that possible. i know i need to use VBA and it cna be done based ont he contents of one cell.

So for example i have a sheet of people i am awaiting payment from, based on the last column " bill paid" i want the tab to be Red if there are any blanks in this column/ range and go green when all the cells in the column/ range are filled with "y"

View 5 Replies View Related

Cell To Respond Either Or Dependant On Values Of 2 Cells

Mar 3, 2008

I am trying to get a cell to respond either or dependant on values of 2 cells.

If A1 = 13
B1 = 0
C1 = 0

I would like D1 to return A1, but

If A1 = 13
B1 = 1
C1 = 0

or

A1 = 13
B1 = 0
C1 = 1

I would like D1 to return the sum A1 + B1 + C1

View 9 Replies View Related

Make Text Dependant On Cell - Bold

Feb 18, 2010

i have some code where i need to make the text bold but not sure how to go about it
the formula is

View 3 Replies View Related

Change Cell Colour Dependant On Text

Jul 20, 2006

how hard would it be to chane the background colour of a cell when a certain word is entered into it? so someone would type 'james' and press enter then the cell would turn red....would only be in a set cell range..

View 9 Replies View Related

Propagating Text Boxes Dependant On Cell Relationship

Oct 8, 2006

I have been working for three straight days (and nights!) to accomplish something that is very simple in other languages, but I can't make it work in VB

Column X has, with spaces between 4 possible contents:
3Q
FM
1Q
NM

St1 St3
3Q today
tomorrow
-2
-3
-4
-5
yesterday
FM today
tomorrow
-2
-3
-4
-5
-6
yesterday
1Q today
tomorrrow
-2
-3
-4
-5
yesterday
NM today
tomorrrow
-2
-3
-4
-5
yesterday
3Q today
tomorrow
-2

and so on for 1500 rows.

I can write this in Lotus in about 2 minutes, but VB has me stumped. I hope someone can put me on the corrrect path.

View 8 Replies View Related

Macro That Saves Into Specific Folder Dependant On Cell Values

Oct 20, 2008

i have this macro that saves into specific folder dependant on cell values and it works spot on with the exception that my po numbers are like st010,st011,st012 etc etc but when it saves it doesnt have the st in front. ive tried numerous ways but to no avail

View 5 Replies View Related

Comparing 2 Lists And Counting Data From The First Worksheet To Fill Up A Second Worksheet

Aug 22, 2014

I've got a spreadsheet with 2 worksheets in it.

On the first one we've got:

Name of the agent | petition REf num | Task

Each petition can generate several tasks, one line per task.

John Doe | XXXX-YYYY | NCO
John Doe | | RIL
John Doe | XERT-WWWW | RMT
Jane Doe | QSZE-AQWC | RIL

On the second worksheet:

Complete list of agents | number of petitions | Status

John Doe | 2 | OK
Jane Doe | 1 | [BLANK]

I want to be able to fill in the second worksheet automatically. For each agent in my worksheet 2, I want to check if they appear in worksheet 1 and if so count the number of petitions related.

I don't know where to start

View 6 Replies View Related

Automatically Shade Cell Dependant Upon Text In Aother Cell

Jul 13, 2009

Im writing a spreadsheet that will act as a hotel room booking diary. There will be 52 sheets (representing each week of the year) and each sheet will therefore represent one week

Each sheet will look at operate the same. Running horizontally will be the days of the week and running vertically will be each of the bedrooms. There are 22 bedrooms in total and each room will have 12 cells allotted to it for each day - lets call this a 'block' and presume that the first block runs from C10 to c21. The final of these cells C21 will have a validation and the user can select 'PAID' 'TO PAY' or 'INVOICED'

If the user selects 'PAID' in C21 I want cells C10 through to C21 to shade GREEN
If the user selects 'TO PAY' in C21 I want cells C10 through to C21 to shade RED
If the user selects 'INVOICE' in C21 I want cells C10 through to C21 to shade YELLOW

View 3 Replies View Related

How To Copy And Paste A Worksheet And Then Remove Data (only Values Not Text)

May 27, 2014

I am looking to see if is possible to copy and paste a worksheet and then remove data( only values not Text) and also not removing formatting and formulas

View 1 Replies View Related

Distinguish Between Two Different Lists From The Same Worksheet In VBA

Jan 16, 2007

Can an excel List (2003 feature) have a Name? How to distinguish between two different lists from the same worksheet in VBA?

Is there somewhere a tutorial on List manipulation in VBA?

View 10 Replies View Related

Automatically Add Items To One Of Several Lists In Another Worksheet?

Jul 4, 2014

I have two worksheets. In the first one column holds the names of universities, and the next column holds employee names. For example:

London Metropolitan University | Chris Davis
London Metropolitan University | Sean Joesbury
London Metropolitan University | Stefano Carlino
London South Bank University | Simon Forsbrook
London South Bank University | Peter Lacko
University College London | Ben Burch
University College London | Oli Lan

In the second sheet, I'd like the universities to appear as headers to the columns, with all the employees of that uni to be listed underneath. I'd also like for these lists to be updated automatically as new rows are entered in the first sheet.

View 3 Replies View Related

Excel 2013 :: Copy Entire Row If Cell Contains Specific Text And Paste Values Into Another Worksheet

Jun 23, 2014

I am using the code below in Excel 2013.

Sub Test()
For Each Cell In Sheets(1).Range("J:J")
If Cell.Value = "131125" Then

[Code]....

This works great except that it pastes formulas. I would like to paste values only. I've tried
" PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False" and it gives me an error.

View 3 Replies View Related

Drop Down Lists: How To Make Source From Different Worksheet

May 13, 2009

When creating a drop-down list using Validation, is there any way to make the source a different worksheet in the workbook? Right now when I click on Source and select my list, it will not allow me to go to another worksheet.

If I manually enter a reference to cells in another worksheet, such as 'PCP'!$A$2:$A$250 it only shows that cited reference, not the actual list.

View 3 Replies View Related

IF - Return Values From Lists

Jun 21, 2013

I have 4 lists:

Job #
Job Title
Client
Job Location

I would like to select the Job # in Cell B2 (Job # list) and have Cells C2 filled in from Job Title List and D2 Filled in from Client List and E2 filled in from Job Location list.

View 9 Replies View Related

Sum Of Values In Lists Across Several Different Worksheets

Mar 2, 2010

I have several worksheets across the bottom of the workbook, lables September, October, November, December, January, Febuary... Each of these worksheets contains a table of identical formatting. (See below)....

View 9 Replies View Related

Compare Values Between 2 Lists

Nov 26, 2007

how to compare the value in one cell on sheet "Block #1" to a list of values on sheet "Data". Specifically I want to be able to look at the value in a cell (which will be a date) and then compare it to a list of company holidays on another sheet in the workbook and if the date is a company holiday it will display the word "Holiday" in another cell I specify on "Block #1" I am doing this to automate generation of a calendar which will have 13 worksheets one for each of 13 4-week blocks. I can get the calendars generated, but can't seem to be able to get it to compare the values of each date to the list of holidays.

View 3 Replies View Related

Associate 2 Column Values In 1 Worksheet Then Export Values To Similarly In Next Worksheet?

Sep 9, 2013

The Room ID values in Column A are associated with the Room values in Column B. I'm trying to move the values in Column A Room ID to Column G Room ID by having excel look up value in Column C Room or Area #, compare it to Room, associate that with Room ID and automatically fill in Column G Room ID. There are 1000s of these so it's not possible to do it by hand.

I attached a picture where i had 2 different workbooks. In reality, I'm working off of 2 worksheets within a workbook.Excel Question.jpg

View 7 Replies View Related







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