Tracker
HOME    TRACKER    Excel
 Advertisements:

# Convert Vertical & Horizontal Data To 2 Lists

## I was wondering is there a way to convert the format of one excel spreadsheet to a different format in another. I have attached a spreadsheet which illustrates what I'm trying to convert from and to. Perhaps VB Script? I have no idea how this could be resolved.

Sponsored Links:

Related Forum Messages:
Convert Data Horizontal To Vertical
i have a data in columns and would like to convert into rows

e.g.
11 A B C D E F G H
22 1 2 3 4 5 6 7 8
33 2 3 4 5 6 7 8 9
44 3 4 5 6 7 8 9 0

and result should be
A 22 1
A 33 2
A 44 3
B 22 2
B 33 3
B 44 4
C 22 3
C 33 4
C 44 5
and so on ..

Make Horizontal Data Go Vertical
a_____b____ c_____ d_____ e______ f______ g_____ h_____ i
1 - 12/1--| 385 -| 15000 -| --etc--|| --etc--|| --etc--|| --etc--|| --etc--|
2 - 12/5--| 256- | 20000 -|
3 - 12/6--| 356- | 11000 -|
4 - 12/5--| -89--| -1000 --|
5 - 12/9--| 500- | 9000 ---|

Where d1:d5 e1:e5 f1:f5 and g1:g5 h1:h5 i1:i5 all have similar data.

Is there a way to have the data from def and ghi fall in line underneath abc with the condition being that there has to be something in the cell?

Vertical To Horizontal Data Extrapolation
How am I able to extrapolate data from vertical to columns?

I.e.

From:

ColumnA ColumnB

Field 1 Result 1
Field 2 Result 2

To:

Column A Column B Column C Column D
Row 1: Field 1 Result 1 Field 2 Result 2

This is different than Transpose.

SUMIF With Both Vertical And Horizontal Data
I need a solution for the equivalent of a SUMIF combining both vertical and horizontal data. The vertical cells align to the horizontal ones, but they're in a different table.

My attempted formula is: =SUMIF(\$H\$22:\$H\$30,"TRUE",D7:L7)
*note that this is just an example set of data...my real data set is much larger (both rows and columns)

I need to be able to do this without transposing any of my data.

Things I've tried:
- Another option I tried was making D7:L7 a named range and using the transpose function (as an array) within the SUMIF formula above. I received an error.
- I tried using a bunch of IF statements added together (i.e. =IF(H22=TRUE,D7,0)+(H23=TRUE,E7,0)...); this actually works properly, but I get the "formula too long for cell" error when I put them all in (too many characters)

I'm using excel 2003 and windows XP professional.

Call Horizontal Data Into A Vertical Layout With Additions
I am trying to reference data that is currently horizonal in excel and would like to reference that data to get output horizontally with some additions. I have pasted a screenshot of my test spreadsheet. how to do it step by step as I am completely a newbie in excel. As displayed below my data is in Cells A1 thru E3. I want to get output in Column H for this data but with text additions. For eg: in A1 I have Rachel 1/1 however in H1 that should become "My name is Rachel 1/1"....

Find Data Based On Horizontal And Vertical Criteria
I have a spreadsheet that I am trying to create a formula for that will bring back the data found when you compare an X and Y axis. A sample is attached as the data is huge and I figured what ever you all created I could modify.

I need it to bring back the data found when I run my finger down the column till I hit the appropriate row.

Converting Vertical Data To Horizontal For Varying Data - Large Amount
I have a one column spreadsheet. The column contains this data:

1 Name
2 Address
3 City
4 State
5 Zip
6 Telephone
7 Fax
8 URL
9
10
11 Name
12 Address
13 City
14 State
15 Zip
16 Phone
17 URL
18
19 Name
20 Address ... and so on

Where there may be one or two blank rows between the individual records and where there may or may not be a Fax number (or row) in the record.

I am trying to convert this data to a horizontal column format - which works fine if I do a copy/paste special/transpose. However I have to do this for 1,800 records and cannot figure out how to do this reliably.

I gave the above illustration to simplify but, actually this is a two column spreadsheet with individual row labels for every record using the above terminology. In other words the above text is in the first column and the data is in the second. Just thought I'd mention in case there was a way to do some kind of if/then formula.

How To Create Macro To Move Multiple Horizontal Data To Vertical
I need to create a macro to move variable multiple horizontal data to vertical format with certain infomation on horizontal will be duplicated following that variables. It's looks like below where you can see variables data in column F, G, H and I are moved vertically and at the same time column A, B, C, D and E will be duplicated following the variables allocation. I've tried to use transpose but it too manual and now looking suitable macro to help on this function

Original DataAccountDim 3Dim 4AmountCurrencyV20228V20242V20211V202044006003300BXXX 9.4USD0.591.923.343.554006003400BXXX 88.17USD5.5118.0331.3233.314006003500BXXX 7.27USD0.451.492.582.75Process to automateAccountDim 2Dim 3Dim 4AmountCurrency400600V202283300BXXX 0.59USD400600V202283300BXXX 1.92USD400600V202283300BXXX 3.34USD400600V202423300BXXX 3.55USD400600V202423400BXXX 5.51USD400600V202423400BXXX 18.03USD400600V202113400BXXX 31.32USD400600V202113400BXXX 33.31USD400600V202113500BXXX 0.45USD400600V202043500BXXX 1.49USD400600V202043500BXXX 2.58USD400600V202043500BXXX 2.75USD

Search Horizontal And Vertical
What kind of formula do I need that looks for a date and find all the names that have the same character on that particular day

In the example below cell R4 kooks for date R3 in range A1:L15 and find all cells that have a "G" and displays all the corresponding names from C1:L1

I prefer non array-formulas

Changing Entries From Horizontal To Vertical
I have a list with entries in one column like this:

sourceText
empty cell
targettext

sourceText2
category2
targettext 2

and I want to change it like this:

sourceTextempty cell target text
sourceText2category2target text 2

Attached sample data. Sheet one as it is. Sheet 2 as I want them to be (I don't need any of the formatting in Sheet 2).

Lookup For Vertical And Horizontal Corresponding Values.
I have a problem that lookup vertical and horizontal corresponding values when there was duplicate values as it's only returning the first value found. What I want was to lookup the vertical and horizontal corresponding values on the left most & top most column based on the largest values column and also to return the duplicate values under the vertical and horizontal value column in ascending order if it's a duplicate values.

Transpose Horizontal Rows To Vertical Columns
Acct #11019145036954564872011019125101521103192103103904205851103192415102

The file is broken down by revenue codes on the first row of the account and then units on the second row. (450= Revenue Code and 1=Units associated with that revenue code) Then a new account number starts and it is revenue codes on the first first row and units on the second row.

I want the file to look like this:

Acct #Rev CodesUnits1101914501110191390311019168021101917203110191110411031939431103193921511031954510110319648

Change Vertical Rolling Hierarchy To Horizontal
The accounts listed on this report change on a monthly basis. I am attempting to create a macro or write a formula that can but used each month to fill in this list automatically. On Sheet 1 is a short example of what the report looks like when exported into excel. The numbers on the left are the numerical hierarchy. The long set of numbers are the account numbers and the others are the categories that each are located under.

Sheet 2 is what I need the end result to look like. One of the problems I am running into is how to handle the fact that the hierarchy re-uses numbers. The hierarchy always runs vertical, therefore "5" always falls under the "4" that is directly above but when there is another "4" it should be ignored and continue on up the chart until it finds the first "3" and place that in the cell to the right then find the first "2" and so on.

Checking If Inside Vertical/horizontal Boarders Exist
Part of my code formats a selected range, including boarders. I needed the vertical and horizontal boarders to be set to 'xlThin', but some times the range that is selected may only be one row high or one column wide, so this is throwing up and error. I am currently using the code below to do this.

Custom Toolbar Orientation. Horizontal & Vertical
How do you control the orientation of a floating toolbar? The code below puts the elements horizontally, while I'd like to make the toobar have a vertical format (one macro per line). I'd also be interested if there's a way to do sub-menus to organize the toolbar buttons better. how to revise the code below, and/or a pointer to where I can read about this. I tried searching google and this forum to no avail.

Option Explicit
Public Const ToolBarName As String = "My Toolbar"
Sub Auto_Open()
Call CreateMenubar
End Sub
Sub Auto_Close()
Call RemoveMenubar
End Sub
Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error Goto 0
End Sub
Sub CreateMenubar()......................

Update Horizontal List Based On Vertical List (ins Column)
1. I have a list of several items in sheet 1. Lets say 30-40 rows.

2. I do a frequently update of this list.

3. In sheet 2 I want to have a copy of this list in row 1 from column A to Column x (depends on how long the list in sheet 1 is.)

4. When I change the list in sheet 1, could be in first, middle or last row- mayby several rows. The list in sheet 2 should be updated

5. The update of sheet 2 should insert a new column on the right place, according to the list in sheet 1.

Sum If W/ Index Match On Vertical And Horizontal Match
I am looking to sum the values found at the intersection of multiple values based upon a vertical and horizontal lookup. The formula I am trying is as follows (which results in #Value):

{=IF(ISBLANK(G\$9),"",SUM(IF(INDEX('Journal'!\$A\$20:\$Z\$1020,MATCH("Prior",'Journal'!\$F\$20:\$F\$1020,0),MATCH(G\$9,'Journal'!\$H\$19:\$Z\$19,0)),"")))}

Show Contents Of Vertical Cells In Horizontal Cells
I am referring to the post made by NBVC at http://www.excelforum.com/excel-gene...xcel-help.html, Try: =IF(\$A1="",INDIRECT("A"&ROW()-ROW(\$A\$2)+COLUMN(B1)),"") in B2, copied across and down

I have a similar case but there are no empty rows between lists, what would be the formula to get the same results? (The transposed address should appear next to each first line of each block).

Earlier, I posted at http://www.excelforum.com/excel-gene...-one-cell.html and I got brilliant answers, I also would like to get benefit of the above mentioned formula but in cases when there are no empty rows between lists.

Horizontal And Vertical Chart Axis As Value Axis
What is the best way to have both horizontal and vertical axis as value axis?

Display Data In Horizontal Format
I need to show data across the page, currently the data is arranged in column's, I need it displayed in row's.
Each material has a number of components, it is the components which I want to show across the page, with the different headings.

I have attached the current format and the required format.

Note the spreadsheet I will be working on has thousands of materials and hundreds of components.task.xls

Mirroring Vertical Data Horizontally
I have one spreadsheet in which data was inserted vertically, and in order to move it to SQL, i would like to first mirror it horizontally in a 2nd spreadsheet (the reason i want to mirror it and not just copy the data is that the information in the first sheet changes dynamically).

So i figured out the basic function - (=sheet1!B0), but doing this on more than a 400 cells could be a problem. So my question is, how can i make, and lets take an example, cells A1 through I1 (in spreadsheet2) mirror A1 through A9 (in spreadsheet1), without editing functions cell by cell?

Move Data From Vertical Column To Line Up Horizontally
How do I move data that is in a vertical column to line up horizontally (in separate columns, but same row)? For example, take an address:

Name
Address
City/St
Zip

How do I get it to go from that format to this format?

Name Address City/St Zip

Formula To Lookup Data In A Vertical Format And Place In A Table
I have a RAW DATA work sheet that has data of electricity consumption for a given week but it is in a vrtical table.

I have many other work work sheets in the workbook that I require to look at the RAW data and the return the correct information in the specified cells

I need the store number that is in cell F1 of each sheet and the Date on each sheet that are on Row4 of each sheet to Look up and match the information in ROW1 for the store number and columnA for the dates.

then in columnB of RAW DATA I have time intervals of 30mins which need to match up with the time intervals on the sheets and display the readings from the RAW data on the sheets.

******** ******************** src="http://www.interq.or.jp/sun/puremis/...<CENTER><TABLE cellSpacing=0 cellPadding=0 align=center>Microsoft Excel - Energy Analysis WE15-03-09.xls___Running: 11.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA2A3A4A5A6A7A8=ABCDEFGHIJKLMNO1Reading DateReading Time8912116617118519682296710191119125612571292209/03/200900:0012.5926.74929.69668.728.6487.526.5616.2312.6416.3818.08317.02719.569309/03/200900:3011.8467.211.49610.1245.8726.821.817.9811.3216.711.96214.65619.243409/03/200901:0010.7368.11211.19811.286.27.415.2330.3412.0416.269.5527.26429.02509/03/200901:3010.78767.612810.68510.40725.6966.814.888.936.8416.618.53448.72645.4432609/03/200902:0011.0727.235213.01310.3235.9288.814.757.875.9218.059.38247.09445.3136709/03/200902:3011.2996.819210.26210.1765.70410.414.758.135.0916.489.0566.88325.1984809/03/200903:0011.8116.18248.952411.3695.88.314.697.774.9916.87.20964.71046.2496RAW DATA [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Data Lists
I would like to have a list that when one of the items is chosen, it brings up a second list and so on.

Therefore, list 4 will depend on what was chosen from List 3 which will depend on List 2 which will depend on List 1.

I'm not sure if macro's are required.

I have attached a file that lists out a Decision Tree (Decision Tree Tab).
I also have a Form tab which is where I have the decisions that need to be made.

So, in the Gender Row I can choose Male or Female. If I choose Male, then the Interests list should contain Cars, Sports or Colors.

Type will depend on which I chose and finally, Detail will depend on which Type I chose.

Matching Up 2 Lists Of Data
I have a list of data in cells A through C, and also have the same data in G through I.
A:C is 2009 data, G:I is 2008 data.

Some of my data is a line off, what i want to do is match numbers based on A and G and then match up there data.

Compare Lists Of Data
I need to search a column of numbers in Excel (A) and compare it to another column (B). I need to find all of the numbers that apprear in column A but not in column B. Can this be done?

Lists, How To Make Drop-down Lists Optional
Is there a way to make a drop-down list optional? When I create drop-down lists the user has to make a choice from that list.

Example: a list of doctors. I need the user to be able to select from that list if the patient saw one of those physicians, but if the patient saw another doctor not on the list, I need the user to be able to enter the name of that other doctor. Currently the user has to select from the list or not enter a doctor. I am using Excel 2003.

Matrix - Need Data Sorted Into Lists
I have a matrix of mixed numbers (going across many columns and rows) in Sheet1. They are a mix of positive and negative numbers, In Sheet2 I need 2 lists to be automatically displayed, taken from the data in Sheet1.

The first list (column) is for positive numbers only, the second is for negative numbers.

There is one filter: only numbers over a specified value (for the positive list) must be used from the data, and only numbers under a specified value (for the negative list) must be used from the data. The 2 filters can be stored in any cells in Sheet2 (the worksheet where the lists will be), lets say, for ease of use, A1 and B1.

But here's the tricky part: I need the location reference of each number in the matrix displayed next to the number in the list!!! Eg: in the matrix, I have names running down the left (A2 to A25) and the same names in the same order running across the top (B1 to Y1) - just like any basic matrix! If A6 is called "John" and L1 is called "Pete" and their value (at cell L6) is 10, then I need them to be displayed in the "positive list" amongst the others (sorted automatically into numerical order) with the words "John" in the cell to the right of the score, and "Pete" in the one after that to the right: so it will look like:

A2 B2 C2
10 John Pete

(remember that these 2 lists are on Sheet2, not on the matrix worsheet! A1 and B1 contain the 2 filters for the positive and negative lists).

Of course, this must only happen if the filter in A1 (the positive filter) is 10 or under. If it was anything above 10, then this one would not make the list.

As for duplicates in the matrix coming up twice in the lists, that's not a problem this wont matter! It's ok for the lists to have constant duplicates displayed eg: 10 John Pete will also have next to it 10 Pete John.

Editing Data And Comparing Lists
I'm currently working on sorting a column of data from a PDF file into separate columns to later compare to another sheet. I'm having a few problems getting the data into a workable format, and also don't know how to compare to the other file.

Ultimately, I want the data in columns that look like this:
Name, City, State, Zip, \$123.00, mm/dd/yy

Currently it looks like this:

A
1 Name
2 City State Zip
3 \$123.00 mm/dd/yy

The sheets I'm working with are thousands of rows long, with these three rows of data repeating for every person. I have two problems with them, however:

1. I ultimately need each of those pieces of information into a seperate cell, in separate columns. I've been transposing the data then doing "Text to Columns" to get it from three separate rows in Column A to one row in Columns A, B, C etc.

The problem with this is the names aren't always uniform. Sometimes there's Mr. or Mrs. in front of them, sometimes they have a middle initial, or an "esq." behind them, which completely messes up the "Text to Columns." Is there a better way to break them up into separate cells?

2. Secondly, not all of the names are individuals- some are businesses or charities. I want to remove all of these (and the two lines of data that go with each of them), so I only have individual people. Do you have any idea of how to make excel know the difference between, for example, "Mr. John Smith" and "Smith and Sons Realty"?

3. Lastly Once I have all of this data spread out into the different columns
(Last Name, First Name, City, State, Zip, Date, Amount), I need to compare it to another list. Basically, I've got one list of data configured in a similar way, and I want to identify any repeats between the two lists. I know that excel has a way to delete repeats, but what I need is a way to delete everything BUT repeats.

Dependent Lists With Duplicated Data
i need a way so they can be differentiated. This is how my validation are set out

and my details of cars { list}
http://img509.imageshack.us/my.php?image=oysc7.jpg

once the end user chooses the model i need it to display the information of that model like the Price for a day and NO of doors

Match Data From Two Almost Identical Lists
I'm working on two almost identical lists but because of their nature, some rows are repeated on one of the sheets and so on. I need help matching some cells onto the other sheet. I tried the VLOOKUP formula but it doesn't work since some rows are repeated... I need the match to link especifically a Name with it's correspondent Value, regardless of the position of the name in either of the sheets and whether it is repeated or not.

Data Validation :: Dependent Lists
See the attached example. I have created a dependent data validation. The list available in B2 is dependent on the item chosen in Cell A2.

What I would like to know is how do I expand this validation down columns A and B. I don't want to have to add the validation in each cell individually!

Disappearing Data Validation Lists
I have a very large spreadsheet that I work with on a daily basis. The main portion of the spreadsheet is broken down into weekly sections (for example, columns GF thru GM contain data sections for one week, which then repeats with the next set of columns, etc.). Below these weekly sections are detail and summary sections for all the data entered above. In the main weekly portion, each week contains a size drop-down list which calls to size information in the detail portion below. Recently, the data validation lists have started disappearing. By disappearing, I mean that the data validation for the cell no longer exists on a large portion of the list cells. I have tried recreating all of the data validations, saving the file, and re-opening it, and when I do, they've disappeared again. I have no idea what to do at this point. The functionality of the spreadsheet is crippled without the sizing drop-down lists (for various lookup reasons).

Dependent Data Validation Lists
I am trying to make two dependant lists in Excel. Using data validation lists, is it possible to, for example, select a country from one list, then select a city in that country from another list. For example, if I select France from one list, I only want the list of cities to include French cities, rather than the entire list of cities in the list.

Data Validation :: Dependent Lists ..
I have an Excel spreadsheet with two lists of data, the first is a list of groups and the second is a list of sub-groups, each group containing a number of sub-groups, each sub-group being unique and belonging to only one group.

Elsewhere on the sheet, I have used Data Validation (using 'list') in 2 columns to provide a drop-down selection in cells which the user must complete. The first column refers to the first list (groups), the second column refers to the second list (sub-groups).

However I would like to make the values which appear in the second drop-down list dependent on the value which has been selected in the first. That is: make the user choose a group in the first column and then make the choice of available sub-groups in column 2 restricted to those sub-groups which belong to the selected group.

Data Validations - Multipale Lists Depending On Each Other
I have search the site for a possible solution for my problem. And that is: how to create multiple lists using validation – depending on each other.

The scenario is. I have a data sheet that contains a list of alien plant species. The species have different methods of treatments (some do have the same). Each treatment might have one or more herbicide products that can be used and with each herbicide a certain application mixture.
I have used validation to create the pick list of the plants, the next pick list should be the treatment for the selected plant species and the 3rd list the product and then last list the mixture.
All of these lists should only list the information that is relevant to the selected plant specie, in the first list.

Data Validation Lists And Conditional Formatting
On sheet 1 I enter data such as:
Visit Date | Customer Name | Customer Number | Job Number | Product

On Sheet 2 i have a list of our 130 products which would be similar to '1130Tots Standard 24" ' I have made these as a list so we can choose from the list using data validation.

Now, out of the 130 products we have about 4 main different Catagories - Tots Direct, Tots RTS, Direct, RTS. - I could put what category they are in in a column next to the list on Sheet 2.

What i would like to happen is if e.g. a product is chosen from the drop down list, it could look in the cell in the column next to it and see what Catagory it is in and format the entire row on Sheet 1.

E.g. Direct changes colour to grey, Tots RTS to Pink, Tots Direct to Grey/Pink stripes, RTS stays standard no colour.

A bit of a development on a question i asked last week to cut out another stage. Currently people have to enter the product, then choose in the next column whether is is Direct, RTS, Tots Direct, Tots RTS then conditional formating will colour the rows. - Want to know if it is possible to do this all in 1 go by selecting the product from the list.

Using An IF Function To Choose Between Two Data Verification Lists
Title should read "Data Validation Lists"...oops

I'm trying to format a spreadsheet so that based on the value in Column A, Coulmn B displays one of two spearate dropdown menus.

I know the following formula will not work, but that is the effect I want in Column B.

Data Validation List :: Dependant Lists
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

2 Lists And Finding New And Canceled Data Through VBA
I have 2 sets lists which should be identical, but every day I get give one new file which I have to compare to the other one. I need to find new data and removed data in the new file.

It is a dynamic list, but I need to check every day that all the data that was there yesterday is also there today. If not then I need to be told which data is missing and which data is new (maybe thrrough a pop up box and a new sheet with a new and cnacles coloumn).

A Example of the data is below. On day1 everything matches. On day 2 Grapes is a new entry, so I need this flagged up.
On day 3, pears hsave disappeared, I need this flagged up.

What would be great is to have a messgae box pop up and say "2 new entiries and 3 deletions - Please refer top Changes tab". And then the new/deleted entries will be in a seperate sheet.

I have tried searching this on the site, but can't find anything (I can't beleive problem like this is not on the website) - If there is an article would you be able to point me to it.

Day1
orangeapple
pearorange
applepear
bananabanana

Day2
orangeapple
pearorange
applepear
bananabanana
grapes

Day3
orangeapple
pearorange
applebanana
bananagrapes
grapes

Conditional Data Validation Drop-down Lists
Sheet1 is my database and looks like the following:

HTML ABCDEFGHIJK
1RefNoSetSubsetStatus
2101lambsheepINP
3102catpetCOM
4103chickenbirdINP
5104milkcowINP
6105turkeybirdINP
7106honeybeeCAN
8107dogpetINP
9108boybabyINP

Sheet2 is for the user input, in which Columns H and I would have a data validation drop-down list and looks like the following: ...

Multiple Dependent Data Validation Lists
I've looked at the data validation tips and understand how to make a second dropdown dependent on the 1st, but how about a 3rd dependent on the prior 2 selections and so on.

I would like to have dropdowns based on a dataset of 4 columns (Location, Department, WorkGroup, Employee) that frequently gets updated. I would like to have the user select a location then a department then workgroup and employee. I would then fill in a worksheet with other pertinent information. I can get the 1st two dropdowns to work, but haven't found the key to being able to continue to drill down based on the 1st two options.

One Data Validation List Dependant On Four Dv Lists
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.

Data Validation Dropdowns (Dependant Lists)?
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.

Dual Data Validation Dropdown Lists
I'm trying to create a cell with multiple dropdown lists in E4 depending on a value from another drop down list in B4. Both E4 dropdown lists are named cell ranges RV_MECHANISM and VALVE_OPERATING_MECHANISM_TYPE. I know how to do this in Data Validation using an IF formula, however, that's not quite "bullet proof" or "idiot proof" enough.

I'm attempting to create a macro to load only the appropriate dropdown list in E4 as called for by B4.

I recorded the Data Validation dropdown lists using the macro recorder, then searched through all appropriate posts I could find at MrExcel and attempted to write the appropriate macro code as follows;

Sub RVorMECHTYPE()
With Selection.Validation
.Delete
.Add Type:=xlValidateList
If Range("\$B\$4").Value = "RV" Then
ValList.RV_MECHANISM
Else
ValList.VALVE_OPERATING_MECHANISM_TYPE
End With

Range("E4").Select

End If
End Sub

Compile error: End With without With

That's as close as I can get ... can one of you kind souls direct my macro coding paths and offer up the correct code for accomplishing this task.

Filter Lists Data Entry Box
I am trying to filter a list where I can enter a value in a criteria box and it will filter the list just with those entries that meet the criteria entered in the box.
E.G If I have a list containing the name Jones 3 times, and Brown 7 times, I want to type the word Jones in a criteria box and it will sort the list to only show the 3 Jones entries

Data Validation Lists: Tree Style
I have a data validation list in B31 "material type" that has 4 options (Adhesives, Metals, Backer Board, & Grout). Based on that selection F31 needs to display a list of "descriptions" that match that "material type". The list is in book "database.xls!sheet3". Material type list is Column A, Description is in Column E. I know that you can't link to an unopened workbook in data validation, & I've been trying to code it, but again the lack of expierence & knowledge has frustrated me again :P This code to get the info for the file to open

Private Function GetValue(path, file, sheet, ref)
Dim arg As String
If Right(path, 1) <> "" Then path = path & ""
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function....................

Preventing Pasting Of Data Over Cells With Validation Lists
I've got a table that has to be manipulated by multiple users. Within the table I have several column ranges with validation lists (in drop down boxes) in order to limit responses. However, I'm finding that most of the users are pasting data in from other sources that may not have been in the validation list, or may be in a different format, etc., and it essentially undermines the reason for using the validation.

I've tried protecting the sheets using various options and tried looking around online for a solution, but I'm not finding how to get around this.

Add & Remove Data Based On 2 Lists
I'm hoping to achieve the result in the image below.

Column A contains data, column B contains values to be added to column A and column C contains values to be removed from column A. Using formulas or macros, how can I present the result in column D?

Can Data Validation Lists Reference Another Work Book?
I have a cell that I want to use data validation on so I have a drop down list. Problem is the location of this list will be in another workbook.. Is this possible to go from one workbook to another using data validation?

Also, depending on the information that is selected from the drop down list I want a cell to the left to pick the corresponding data from the list in the other workbook. These forms will always be in the same folder.. Not sure if that information is necessary but just in case you need to know.

Copyright © 2005-08 www.BigResource.com, All rights reserved