I have created a drop down menu wseveral items for the first time. The drop down is located on several worksheets. What I'm trying to do is find out if there is a way to do 2 things.
A) capture a total (numerical value) of the number of items selected from the list on each worksheet. ex. if on worksheet #3 15 cells contain data from the drop down cell A1 on worksheet #1 would = 15.
&
B) capture a total (numerical value) of the number of times a specific item was selected from several worksheets. ex. if ABC was selected from the drop down menu 8 times on worksheet #2 & 6 times on worksheet #3, cell A2 on worksheet #1 would = 14.
I have created two workbooks, one which is a master price list (MasterPriceList.xls) and another which is a pricing sheet template that will be used to calculate many different products (Pricing.xls)
I have used a VLOOKUP across the workbooks (thanks to those who helped me with that) but I cannot get a drop list to work across two worksheets. When I enter the source in the "refers to" box, I get a notice saying that you can't use data validation across two workbooks. However, this was the same error message I got when I was incorrectly inserting the reference source for the VLOOKUP function, so I don't believe it Also, there are several tutorials on the web that say this can be done. However, none of them seem to work for me.
I cannot open the Data Validation box and get to the other open worksheet, so I can highlight the area I want, with the range I want to drop down. Until I close the data validation box, I cannot get out of that sheet. I have used cell ranges as well as named ranges.
When I type in a name, I get only the text I entered in the source reference box appearing on the sheet, i.e., the drop down box will only show "=C:Documents And SettingsAllenMy Documents, etc" it doesn't seem to recognize it as a source.
I have a spreadsheet with a single worksheet. This worksheet contains 1200+ rows of equipment. Each row of equipment is made up of 4 columns: the equipment name, then the department (made up of sections), section (made up of teams) and team (a few workers) responsible for maintaining it.
I have been given the task of making it easy for the departments, sections and teams to identify which pieces of equipment are theirs. I have been specifically told to add a new worksheet to the spreadsheet that contains 3 drop-down menus; you guessed it, department, section and team. The idea is that someone chooses the department, section and team that they work for, then click worksheet 2 and their equipment has been filtered for them. (Some teams come under more than one section or department, depending on the task so I do need all three drop down lists). What I can't do is get a the drop down lists in worksheet 1 to filter the data in worksheet 2.
I have a workbook with 20 worksheets. All tabs are identical in format and layout. On B3, I have a drop down list (this may be irrelevant, but it's to select one month of the year) using simple data validation. Now, I initially had a formula in B3 equal to B3 of the first sheet, so that if I changed the month in the first sheet it will change on all sheets, it will do it the first time without touching the drop down lists on the subsequent sheets.
However, if I changed the date on any other sheet other than the first sheet, it will erase my formula and replace with the value I selected from the drop down list.
Can you guys please help me to device a way (code or formula; open to anything) where if I select, say September, on ANY sheet, it will change all the others to that selection (September) as well?
PS: B3 is actually a merged set of cell merging cells B3:D3. I know this is important because code and merged cell don't really mix.
I am new to VBA programming in Excel (currently 2003) so hopefully this isn't a "dumb" question.
I would like to re-arrange a group of data from a table of several rows and columns to a single column where the data from the first row is translated to a column then the second row is appended to the end of that data ....
I thought a user defined formula would be a good place to start but am having problems getting the address of the cell that the function resides in to use to calculate the offset from a fixed cell (to be entered into the function as a argument). In other words, how do I get the address of the call that a UDF is entered in into the UDF? activecell.address is returning the cell that was last clicked not the cell were the UDF resides.
Secondly is there a better way to achive the results that I am looking for other than through a function? The size of the table will vary from worksheet to worksheet. I would also like to add an argumnet that lets me exclude cells with certain content from the resultant list.
How to pull data with a VLOOKUP to a file located on a Server.
My big problem is not understanding the syntax of the Server location. I've provided it below (obviously with some character changes).
I have a file located on my Hard Drive. I need to perform a VLOOKUP from A2 on this spreadsheet. The File on the Server is called "LookupTest.xls". The Range of Data is from Sheet 1 and is from A2:C4 and I need to pull the data from column C depending on what is selected from A2 from the file on my HD. I understand how to use VLOOKUP, just not when I'm pointing to another file on a Server.
Server mapping from Windows Explorer:
rbbabc$ on 'RP17409 - ABC Database (AHSSRVVN678.tge.com)'
Again, that server mapping is fake, as I changed it, but this is how it looks on Windows Explorer.
I'm trying to keep a master file of Data on a spreadsheet found on a server so that one master file can be updated, and several other tools pull from that Master File on VLOOKUP, Validation Lists, etc.
I have 2 tables where I need to pull data from one table into the other.
Table 1 - pull data here A Site B Begin Date C End Date Rate
[Code]...
For example: site 77 in table 1, I need to pull whatever rate is shown for site 77 in table 2 for the date range specified on table 1. So the correct answer for the first row site 77 in column E would be .02. Notice there is a problem...mainly the dates do not match exactly. I just need the rate the applies for the majority of the time.
Another example. For table 1, site 174 from 2/11/11 to 2/22/12, the correct answer in column E (pulled from table 2) would be .02, even though Column D shows .00.
I have a macro that copies data from 1 sheet to another one. However, in the destination sheet I have no way of know which is the 1st empty row that my data can be paste into.
I have a sheet where I need to located set-up dates that are greater than 6 months. I dont know if this matters but the cells are not in consecutive rows but are all in column F. I have tried the serveral ways and nothing is working.
I have a table of data, every entry in the table is different. I need a way in a single cell to determine what column the data is in (and a way to get the row it's in would be nice too), some way to do VLOOKUP and HLOOKUP in reverse.
In the attached example I need a function that can take dog and return "1", "A" or even the header "Animal", if another similar function can return the row, that would be nice also but if I can get the column I can get the row. Example
I've tried a few things with creating an array formula to do matches across multiple columns and I can make a multi-cell array that will flag the right column with the right column number, but any attempt to sum/max/match this array formula returns 0 or #N/A
I want to be able to link spinbuttons to the cell it is located in automatically, so I can move it around or copy and then paste into other cells. I do not want to use one button to increment a selected cell.
I currently have a button on a worksheet that loads a userform. I have code written that's not in a in a separate module but in the user form itself. It uses the information entered on the user form screen. When I try to reference data that's on a sheet other than the one that the button is located on, I get a "select method of range class failed" error. I assume that this problem has to do with the button macro being located on a different sheet than the data. Is there an easy fix?
Is there way to take the values of a listbox selection located on a Worksheet and call that value when I initialize a Userform? Or, if not, is there a way to declare my listbox selection as a public variable that can be accessed from any module of my VBA project?
I am trying to use the same VBA code for different applications using variables.
Basically I want to have a variables sheet that has the different directories based on the macro selection. My variable sourceloc would change based on the variable name on the sheet.
sometimes I would want it to equal what was in a1 and sometime I would want what was in a2 a1 cell contains "f:filezilla files a2 cell contains "f:dmc files This is the code I started and it gives compile error when ChDir = sourceloc is reached
Public sourceloc
Sub filezillasettings() Dim sourceloc As String sourceloc = ActiveSheet.[a1] ' a1 cell contains the directory name and folder name =
Right now the security settings on my server are set so that new files can be added by anyone but only a few people can delete them. Everytime Excel opens a file on the server it creates a temp file that it cannot delete due to the security settings. This means that I have to go in to each subdirectory and manually select then delete each of these temp files.
way to identify an excel tempfile, or a method for deleting these files with VBA?
I have a spreadsheet containing 2 worksheets. First worksheet has my Students Details and the second spreadsheet has their Campus Locations.
What I need to create is a Formula that tells me "What CAMPUS was the student LOCATED at on the Course Completion Date?"
[URL]
what I'm trying to do is as follows:
John Completes his Course on the 25th July 2013, his Student ID is 450.
I need to go to the CAMPUS LOCATION spreadsheet and first search for Johns student ID (450) in column B. Because John is listed in B2,B3 and B4 excel needs to check date in Column E to see if his completion date was before the Date he started at Campus.
If it is then the Campus he was at will be the value of Column D on that line.
If it is not then it needs to search the next line until it satisfies the condition.
I am getting a #name? error in certain cells for a file that is located on our domain controller. This file will work only on certain computers. I did not make the file, but since it works on certain computers, I can only assume that it is not a function error. We are running Office 2003.
im currently working on a project that needs to consolidate the data per given date. but the thing is, all of them are located into one column. i was able to separate each category into separate columns but now, i cannot align the data on a per date basis.
On a first sheet, I have a list of cities where physicians travel along with an autofilter. On that same worksheet, I have a cell where users can input a zipcode.
On a second (hidden) sheet, a function calculates and displays in column F the distance in miles between the user-input zip code and all the cities in 4 states. The cities are listed in column D.
I need to filter all the cities on the first sheet (the ones where the docs travel) based on their distance from the user-input zipcode - namely all the cities located less than 150 miles from that zipcode.
I am wondering, in excel 2003 is it possible to randomly select two numbers from one column, say column b9:b45, that are random numbers, and selected by two different font colors. In other words: b9=1348, b10=1349, b11=1350, b12=1351, b13=1352, b14=1353.
Say we press a macro button, and in field B50 one radom black colored number is selected of a field of say 30 numbers that are all black in that column, and in b51, one red number is selected, in a field of 30 red numbers. We want to used this macro to select winners simulating a raffle drawing.
I have 4 sheets: 1 called main 1 called states 1 called counties 1 called people
On "main" I have 1 drop down box and 1 cell:
the drop down lists the states from "states" sheet the cell, which i would like to have as a drop down is supposed to be listing the counties in the state selected from the 1st drop down. right now it is setting the value to TRUE for testing purposes.
then when the county is selected, it will display the people in that county listed in "people". So far I can only get the 1st drop down to list the states. that's the easy part. i need to getting the counties to list in another drop down based on what state is selected.
