I want to know how to link te two lists in my register so that when i select a particular route in one column the corresponding column automatically selects the transit time from the reference list.
View 2 RepliesI've create a very large list using the procedures described in MatchingList sample workbook. For the most part everything works great however, there are about 4 list where when I name the range to capture the assoicated data, the name does not stick. I.E., I add a name with the title in $F$1 as "Address 1" and specify the range as $F$2:$F$8. When I select "Address 1" from the first cell and then go to the next cell to select the options, nothing is there. When I go to any of the cells below $F$1 I do not see the range name in the Name Box. Can someone explain why some of the names work and others don't.
View 2 Replies View RelatedI have a spreadsheet where I keep all records of all the rock climbing I have done.
I am after a way of choosing a climbing area either by data validation or combo box (already set up as a named range) and then the climb names available in another validation list shows only those from that particular climbing area.
From what I have seen from various web sites it seems the climbing area names need to be in a unique list (is this correct). My sheet looks like the following:
Column A | Column B (climbing area) | Column C (climb name)
Date | Mt Arapiles | Agent Orange
Date | Mt Arapiles | Auto Da Fe
Date | Morialta | Asgard
Date | Morialta | Al Sirrat
Date | Buckaringa Gorge | Agent Orange
The name in the climbing area column will be repeated often.
The climb name should be unique to each climbing area, but the climb name my be repeated in another climbing area.
The climbing areas are anamed range on a separate worksheet.
The details of the climbs has all the details of the climb i.e. the Climb Name; Climbing Area (VLookup); Grade ; Distance ; Single or Multi Pitch ; Lengths of each Pitch.
I already have a working example of the spreadsheet (220KB) but it currently works backwards i.e. I use validation to find the climb name and then this uses the INDEX function to return the climbing area.
I would like to be able to choose the climbing area and then the next available choices would be limited to that climbing area.
I am trying to set up a spreadsheet with linked dynamic drop down lists, based on a table.Using the OFFSET function, I have managed to get this to work if the table is on the SAME sheet at the drop down lists. But I want to be able to put the drop down lists onto a different sheet, so that I can lock and hide the table.
I don't want to use the 'name based on the previous column' idea, as the table is large and will change weekly. the drop downs show all values, even duplicates, which I would like to get rid of.
On Sheet1 I'm attempting to auto populate specific cells in columns B,C & D with information found in Data Validation lists (found on Sheet2) based on the "value" chosen from a list in column A. For instance,
If A2=Pig Then B2=Slop, C2=Pen, D2=Food
Is this best accomplished through VBA or a basic Function?
I'm trying to continue on this post: Populate Cells After Choice From Drop-Down
I'm trying to expand on CNL's idea. What I need to do is create a spreadsheet that autopopulates two blocks based on the dropdown box that preceedes it. I've got it to partially work, but can't figure out the next step. The differnce between my sheet and CNL's sheet is my drop-down is an indirect data validation.
The easiest would be to have my data in one long list, but my goal is to make it easier on the user to find the specific procedure (I'm trying to build a budgeting spreadsheet for a medical office). So in box A15 there is a list of the different service area, which gives a list of choices in C15 based on the choice selected in A15. My goal is to have G15 and G16 list the cost and charge respectively.
M1 - O3 is the data for Radiology
Q1 - S4 is the data for Labs
U1 - W4 is the data for Office
I want to be able to use list as filters that will show me results for prices I need. I have no idea how to figure this out. I attached a sample file so you can see what I'm trying to do.
View 4 Replies View RelatedI am looking for the best way ( or choice of ways) to display the correlation between 2 lists of results.
I have a column of failure percentage rates for engineers that I want to compare against a second column of their compliance rate with a process. There are two levels also. The first set shows the good performers and their compliance, the second showing the poor performers.
My goal is to see whether their compliance with the process has any bearing on their failure rates. The full lists contain data on approximately 400 engineers in each category
(if the the numbers below are not in a proper format, I tried to download the excel converter thingy but the link failed)
faliure rate
Compliance
3.95%
91.40%
[Code]....
I'm trying to create one spreadsheet where I use formulas to come up with an "end total" on a daily basis. I want these "end total" values to be automatically recorded each day on a separate spreadsheet that keeps a historical log.
In example:
On Day 1 I will enter numbers into "Spreadsheet A" to come up with an "end total" for that day. This number will automatically be entered into "Spreadsheet B" on the first row.
On Day 2 I will write over the numbers from the previous day in "Spreadsheet A" to come up with a new "end total". This new "end total" will be automatically entered into "Spreadsheet B" on the second row.
I have two linked workbooks, one containing a large data table, and the other containing individual worksheets that extract the data from the large table via HLOOKUP equations in each cell.
Is there a way to have Excel hide the row of a worksheet if the resulting HLOOKUP result in the cell in that row is empty? Also, if there is data in the cell, can I get Excel to automatically adjust the row height based in the incoming data and not on the HLOOKUP equation?
I was just given a task at the office of creating a in depth excel spreadsheet. Here is what I need:
I have one drop down list that represents the state a person lives in.
I have another drop down list that represents the the type of property it is.
If you select CA in drop down 1 and Multi Family in drop down 2 you get the end result in (specific) cell.
Then if you select AZ in drop down 1 and SFR in drop down 2 you get different end result in named cell.
How can I get items within 2 different drop down list to call up the end result in a 3rd cell?
how best to do this (bare with me I'm not quite a pro at excel yet!). I'll attach my doc (JMP Excel Forum) so you know what I'm taking about. I've set up the data to work in a similar way to this, as you can see I've a table set up (Appliance Sheet) which feeds information to the Main Sheet which contains the drop down lists.
What I need it to do is autofill the wattage column (Main!F8 onwards) with the wattage for the device as described in Appliance!N3 or N4 for example. For the wattage column to equal 28 (equivalent to Appliance!N3) then the following would show in the dropdown selections Type>Computers SubCategory>Monitors Device>BENQ G2222HDL Status>On
Is there a way to do this with the set up I have?
I did have a previous version of the whole worksheet that each Device had its own table and I looked up the wattage using and If formula to lookup a info on the device and knowing where to look it up based on the subcategory
=IF(ISERROR(VLOOKUP(D7,IF('Main UI'!C7="Heating_Cooling",Appliances!$A$7:$B$14, IF('Main UI'!C7="Kitchen_Bathroom",Appliances!$A$16:$B$27,IF('Main UI'!C7="Monitors",Appliances!$A$52:$B$62,IF(C7="Computers",Appliances!$A$64:$B$68,........
And I got it to work but it was getting to cumbersome to add in new devices and update the formulas. If attached that doc (OLD) so you know what I'm talking about. The last two sheets are the same sheets that make up the new document (JMP Excel Forum)OLD.xlsm
I really prefer the new way I've got it set up. Its easier to add new information or options to the dropdown list
I'm working on a workbook to track staffing patterns. I have two userforms included in my workbook. The first userform (userForm2) initiates upon opening the workbook. It's intended to allow the user to enter a date range and an office location for the report. The second userform (userform1) initiates when a command button (Weekly Summary) is clicked. I've linked texts boxes in userForm2 to cells in a hidden worksheet; this is where I'm holding the dates and office location until userform1 is initiated. I also have labels in userform1 linked to the same cells in the hidden worksheet so that when userform1 is initiated the office and date range appear at the top of userform1.
Here's the problem, when I click command button "Weekly Summary" the office location shows up perfectly however, the labels I have linked to cells in the hidden worksheet that contain dates do not update (i.e. they show the dates that were previously in those specific cells. I have to close userform1 and re-open it to get the dates to update.
I need the user to be able to choose a date range and office location when they enter the workbook. Then, I need userform1 to show the date range and office location (without having to open it, close it and re-open it) that the user chose on opening the workbook.
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.
I have two seperate lists. One on Sheet 1 and One on sheet 2. What I need to do is take the list on sheet 2 and compare it to the list on sheet 1. If any of the items appear on sheet one, I need to delete those items form sheet 2.
So in the end the only items left on sheet 2 will be items that dont match any items on sheet 1
I've been breaking my head over this and i haven't managed to make this work. The attached excel file contains a list of students that participated in exchange programs on the past. What i want this to do is that when i choose the university the student went on exchange with, that the city and country cell fill in automatiocally with their respectuve info. I have a worksheet on excel that has the university name, on the next cell the city, and on the next cell the country.
View 3 Replies View RelatedI would like to Use two drop down list with two set of information to automatically update fields.
I have Job Titles (1-6) and Step (1-5). Each Job Title has a new pay rate and each step is an increase in pay. What I am trying to do is set up a drop down list where some one can drop down Job Title and step and have the rate automatically fill in.
I am able to get the Job title to auto fill the rate field, but am having trouble incorporating the step into the formula.
Here is what i currently have:
=VLOOKUP(H6,'Pay Rate'!$B$2:$G$12,3,FALSE)
H6 = Job tilte field on 'WorkSheet' (Sheet1)
'Pay Rate'!$B$2:$G$12 = Sheet2 and Step increases per Job Title
3 = the current column I was working with in order to make the rate appear
False = for exact info
I do not want to combine the two columns but keep them separate drop down list.
I have a worksheet in which I enter figures as whole intergers. Sometimes I want to increase all numbers by 1.5x, which I know how to do. Is there any way to get the results to read only a whole intergers, such as 3 x 1.5 = 4.5, but I'd like that number rounded to 5 (preferably not 5.0).
View 12 Replies View RelatedI am wondering if there is a way to automatically update my pick 3 lottery files by using excel. I use lottostrategies.com to obtain my results. If anyone knows how I might start to instruct excel to perform such a task, please respond. Or if I am going about this the wrong way in as far as thinking excel can perform such a task.
View 2 Replies View RelatedI am trying to find a way in which a used can enter text into a cell (effectively the search cell) and then that text is compared to text in 3 or so columns, if the text matchs any part of the cells in the those columns then the spreadsheet should filter out the rows that dont have a match and only leave rows that have a match.
Its like having a spreadsheet with a list of part an user can put in 55 or bolt in the cell at the top and the list would only then show all the rows that have 55 or bolt or whatever text in the table below?
I've adapted a spreadsheet to automatically update the latest results.
But my formula (D11), that I got from here, has now stopped working.
I suspect this is due to the formula I've used columns H and I. But I have no solution.
Also the very similar formula used in M15 to M44 has also stopped working, probably for the same reason.
I am a Microsoft Excel 2010 user and am trying to create a usage report for a website that I work on. I have been compiling the usage reports into one spreadsheet. The columns I use are Month, Device, Language, Title 1, and Title 2. I can filter each of these rows and it shows me the number of records found. Each row on this spread sheet refers to a time that someone selected something on the site. For example the row may say;
Month (filtered)
Device (Filtered)
Lan (filtered)
[Code].....
How do I automatically populate a table on a separate sheet with the number of records found for a certain combination of filters applied. So if I'm looking at monthly trending I want to know how many times the stress test was started in May, June, or July, but automatically using info from the multiple filters.
What is a formula I can use to compile the filtered data from above.
Jan
Feb
Mar
Aprl
May
Start
=formula?
Use filter for 2 columns of a CSV file, column A and Column B for certain words. Say I apply a text filter: contains "word1" or contains "word2" to Column A. Then I get the results. After that, I remove the filter to Column A then apply the same filter to Column B. The thing is, I don't get the results in Column B, instead, I get the cells in Column A wherein Column B is filtered PHOTOS BELOW:
The data that I get for this filter are:
And Word1 you
Word2 is blank
Apply filter to Column B, get the cells filtered in Column A:
The data that get for this filter are from Column A (which are cells filtered from Column B filter)
And Word1 you
Is the blah
Word is all
The results should in a single Column for both filter:
I have more than 100 files that needs to filtered this way, is there any way I can do this automatically?
Here is what the file looks like:
The results I hope would be possible:
Automatically without having to open all the files and have the results of all of it in 1 single Excel file?
----or----
If the above is not possible, open each file and automatically apply the filter into a column in just 1 click and without having to type the same filter for each column.
I found a code to combine all CSV files into 1 Excel file, but half of my files has more than 500k rows and the Excel's limit for rows is around 1million+.
I need to obtain a range of cells starting on cell B10 to the end of records (vertical)
I need to do two things with this:
1) Count the number of records from cell B10 to the last row populated with data
2) Format the cells in this range
FYI - "R" is always the last column.
I have two worksheets, sheet 1 and sheet 2. On sheet 1, I have a information on two columns (Column A and B) Column a has information up to row 10 (aaa, bbb, ccc, ddd, ...). On column B, I have 111, 222, 333 . I have same information on Sheet 2. However, sheet two has an additional column (Column C) with 10 rows on information z, y, x, w, v ...
What I need to do is to match column A and B in sheet 1 to column A and B in sheet 2 and if both columns matches on the same row, copy the value in sheet 2, column C and paste it on sheet 1 column C.
Important: Information on sheet 2, column B may be twisted (As they may not appear exactly the same as in sheet 1. Which means 111,222,333 can be 222,333,111 along the column). Therefore, need to match the entire range.
I have a listbox (form control) on an excel sheet, where the user can select (or deselect) multiple entries.
Is there a way to have vba spit out which options are selected? if I choose "single" for selection type, it will tell me which option i have selected. Is there something similar for multiple?
I've got a problem where I need to assign values to ranges between 0 and 3.
Eg.
RANGE VALUE
2-3 2.2
1-3 1.6
0-3 0.8
0-2 0.5
0-2 0.5
0-1 0.1
0-1 0.1
0-1 0.1
0-1 0.1
The values above are simply approximates done manually but I need it done automatically using a formula. The resulting values should be proportioned similarly to the ones above but they need to all sum up to 6 and be no more than 3 each.
I am going to create a large drop down list with data validation the list is to do with fitness the list will containt a sport with a numeral after it
for example : GOLF 4.5 then under that Basketball 12
what i am wanting to do is make a calculation based on what sport is selected in that cell for instance GOLF 4.5 is selected in the cell so i need to say valueinA43*D43/B9 can this be done?? Or if not. can i extract the value from the list to another cell without the text and then perform the calculation.........
I have a large number (couple of hundred) pdf's in a folder for electrical test certificates. The pdf's are in the format 54 Pike Drive.pdf etc. and have the date modified.
Ideally, as well as the address I would like the date modified as this is pretty close to the date of the certificate so will suffice.
Failing the date, just the addresses will do which need to go on separate rows in Excel.
I've tried copying and pasting the files but to no avail.
I use the following fomula to test and advise of an upcoming birthday:-
=IF(AND(MONTH(TODAY())-MONTH(B6)=0,DAY(B6)-DAY(TODAY()) 25.