Drop Down Is Located On Several Worksheets

Jan 2, 2007

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.

View 10 Replies


ADVERTISEMENT

Linking Drop Down Lists Across Worksheets

Jan 20, 2009

Using Office 2003.

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.

View 10 Replies View Related

Filter Table From Another Worksheets Drop-Down

Jan 22, 2009

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.

View 4 Replies View Related

Updating All Validation Drop Down Lists On All Worksheets Simultaneously

Nov 26, 2009

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.

View 9 Replies View Related

Checking In Which Range A Value Is Located?

May 18, 2014

I want to have C9 populated with a budget type from H1, I1 or J1. How do I create the formula that checks which budget type the value in F9 is?

I am using excel very infrequently.

Screen Shot 2014-05-18 at 12.52.26.jpg

View 6 Replies View Related

Returning Where Function Is Located?

Aug 18, 2009

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 ....

ie:
( 1 ) ( 4 ) ( 7 )
( 2 ) ( 5 ) ( 8 )
( 3 ) ( 6 ) ( 9 )

to

( 1 )
( 4 )
( 7 )
( 2 )
( 5 )
( 8 )
( 3 )....

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.

View 6 Replies View Related

Open WB Located In An Unknown Path - VBA

Jun 17, 2009

is there a VBA way to determine an unknown path in which a KNOWN WB is located !?

Assume a WB named: 1X1.xls Located at C:TEMP

[In case the user uses more than one partition and/or more than one HD - it might alse be located at: D:TEMP

The known open command for a known file AND Path is:

View 14 Replies View Related

VLOOKUP To File Located On Server

Jan 30, 2013

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.

View 4 Replies View Related

Returning Value Located Between Two Date Ranges

Feb 5, 2014

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.

View 1 Replies View Related

Located The 1st Blank Cell In A Column?

Jan 19, 2007

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.

View 9 Replies View Related

Located Set-up Dates That Are Greater Than 6 Months

Aug 10, 2009

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.

View 9 Replies View Related

Determining Which Column Data Is Located In

Apr 2, 2007

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

View 5 Replies View Related

Link SpinButton To The Cell It Is Located In Automatically?

Oct 17, 2012

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.

View 1 Replies View Related

Select Located Cell After Loop For Pasting?

Mar 20, 2014

How do I select the empty cell (once located after using the below loop) so I can paste into it?

[Code] .......

View 10 Replies View Related

Button Corresponding With Macro Located On Different Sheet Than Data

Aug 11, 2009

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?

View 4 Replies View Related

Extract Number From Alphanumeric String Located At Different Row

Jan 20, 2008

What is the formula to be used to extract number from a alphanumeric string located at different row?

example:
test123test128test131
test124test129test132
test125test130test133
test126test131test134
test127test132test135
test128test133test136

result: ....

View 11 Replies View Related

Code To Print File Located In C Drive

Apr 14, 2013

I need code to print a file located in my C drive.

View 6 Replies View Related

Run Macro In File That Is Located On Server While Laptop Is Off?

Jul 25, 2014

I am looking to automate our daily sales report.

I am aware of the windows task scheduler.

However, I'd like for this report to run and be sent out very early in the morning (5AM) before I'm even at work.

My computer is usually off because it's with me as I'm commuting into the office.

These files are saved out on our network drive.

Is there a way I can "open" the file and run the macro I have that builds my sales reports with my computer being off?

View 2 Replies View Related

Take The Values Of A Listbox Selection Located On A Worksheet

Jan 5, 2008

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?

View 9 Replies View Related

Change To Directory Path Located In Cell

Oct 16, 2008

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 =

End Sub

View 7 Replies View Related

Delete Temp Files Located On FileSever Via VBA

Aug 22, 2006

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?

View 9 Replies View Related

Displaying What CAMPUS A Student Was LOCATED At On The Course Completion Date?

Jul 29, 2014

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.

View 5 Replies View Related

Getting A #name? Error In Certain Cells For A File That Is Located On Our Domain Controller

Nov 4, 2009

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.

View 2 Replies View Related

Separating Categories Located In One Column Then Combining It In A New Table

Mar 20, 2009

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.

View 10 Replies View Related

Filter Data Based On Values Located On Another Worksheet

Dec 28, 2011

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.

View 4 Replies View Related

Calculate If Time Value In Cell Is Between Times Located In Two Other Cells

Feb 7, 2008

I'm trying to calculate whether a time value located in a cell is between the times in two other cells. Here is what I tried:

=IF((AND($A62>=C$55,$A62

View 9 Replies View Related

Random Selection Of A Data Located In A Column, By Text Color

Feb 5, 2009

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.

View 4 Replies View Related

Automatically Create Hyperlinks In Cells From Mail Addresses Located In Another Column

Nov 2, 2009

i'm trying to automatically create hyperlinks in some cells from mail addresses located in another column. this is what came out:

View 2 Replies View Related

What Its Called When You Have Drop Down Menus That Correspond With Other Drop Down Me

Nov 22, 2008

what its called when you have drop down menus that correspond with other drop down menus.

View 2 Replies View Related

Populate Drop-Down Based On Another Drop-Down Choice

Oct 25, 2006

Here is what I'm trying to do:

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.

View 2 Replies View Related







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