Change Location In Dropdown So Information Is Not Lost?
Aug 27, 2013
I created a drop down in a file that contains a list of locations. The people that run these locations have to input data (mileage) in other areas of the worksheet. Is it possible to have the data in the cells change as someone changes the location in the drop down so the information is not lost or overwritten each time?
I am really trying to avoid making a workbook with several tabs.
The cells that are colored blue are the cells that the people will have to put input into. I would like to be able to keep the data from all the cells that are blue each time they change the location. The rest of them are already set to change with the location change via a formula. Those are all based on data that I pulled and was able to provide.
View 2 Replies
ADVERTISEMENT
Nov 21, 2012
I have a text box in a user form that when the information is added and the command button is clicked I would like that information to go to this workbook, which is in a different location.
H:Burney TableMaterial That Needs AddedMaterial to be added.xls
I need the in formation to stay in Column A starting in Row 2.
The next time info is entered into the text box and the command button is clicked i need the information to go to the next empty row in the workbook
View 3 Replies
View Related
Sep 7, 2007
I have a table of information with location codes as the column headers. Each location has from 1 to 6 codes associated with it.
I want to create a new table that displays the information by location instead of code, i.e. adding all of the codes for a location into one column for that location.
I'm hoping the example will make this clearer.
On the main page, I am trying to add in the wa column all of the columns in the raw page that have a code associated with wa as the header.
To make this more complex, I can't use vba on this one.
the only thing I've got so far is a very long, very complex formula that adds together numbers generated from index/matching each entry in the second table.
something like this.
(edit changing 1:1 to $1:$1)
=IF(VLOOKUP(B$1,lookup1,2,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,2,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,3,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,3,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,4,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,4,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,5,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,5,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,6,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,6,FALSE),raw!$1:$1,0)))
It works, but its horribly ugly, and if the number of locations goes higher (we could be looking at going to ten location codes for one of our locations) then I'll actually exeed the 1000 characters per formula limit!
View 9 Replies
View Related
Dec 28, 2012
I am trying to create a drop down list that shows the description of different codes. i.e.
1000 - Straight time
1010 - Vacation Time
1020 - Sick Time; etc.
In the spreadsheet, I only want the number to show (1000, 1010, or 1020).
View 2 Replies
View Related
Oct 25, 2012
Wasn't sure what the easy way to do this is. I'm trying to have a drop down menu where choosing a certain item triggers specific information to fill multiple cells. For instance, choosing Work Contacts in a drop down menu brings up four different people's names in cells B43-46 and their phone number in C43-46 and email in D43-46. Choosing another item for instance, Personal Contacts, would reveal a different set of four names and information.
View 2 Replies
View Related
May 21, 2014
I'm wondering how I could display information based on the user input through dropdown selection. I know how to make a dropdown-list already.
Example: A1 has a dropdownlist with Apple and Banana in it. If I select banana then I want B1 to display "8" and if I select Banana I want B1 to display "4".
View 5 Replies
View Related
Aug 7, 2012
I have a row b1, b2, b3, b4, b5 etc. I need to change it to c1,,c2,c3,c4,c5 etc. Is there a fast easy way to do it. I know about $ but I'm past that.
View 6 Replies
View Related
Dec 14, 2006
which part of this macro I need to edit to make it open a folder I want?
Sub OpenFolderRequest()
YesNo = MsgBox("Would you like to open the folder to see" _
& vbCr & "which files are currently there?", vbYesNo + vbQuestion, "Open Folder?")
Select Case YesNo
Case vbYes
myval = Shell("c:winntexplorer.exe c:winnt", 1)
Case vbNo
End Select
End Sub
I have tried changing the (c:winntexplorer.exe c:winnt) to the location name of the folder I want to open (S:Stock ControlSTOCK CONTROLOrder Confirmation)but it doesn’t seem to work.
View 9 Replies
View Related
Jul 5, 2014
I have a project I'm working on that requires me to display information from various sheets on a dashboard. I have created a data validation dropdown list with part numbers. When one of these part numbers is selected from the drop down list I need the rest of the fields to populate with the appropriate information from various sheets in the workbook. How do I tell the dashboard to display this information per part number?
see the attached workbook.
Mini-Project Wally Mart (B212-10).xls
View 5 Replies
View Related
Feb 5, 2014
I have this data inputted into excel sheet: [URL] .......
This data is in tab called Shops, that has 8 columns of information. In another sheet called control, I have the shops names from row A in a list box. What I am trying to do is when whatever shop is selected in the list box then output the shop information from that row in 8 different cells in another sheet. I have a list of about 200 shops that I need to input.
So if we pick shop, Abc inc from the list box how can I get excel to look up the information in Abc inc in the shops tab of information and then display them on other cells in different sheets.
I have the same issue with making a list box of people names. I made the list box but I want to make it so it will display there phone number and e-mail address in 2 other cells once you pick there names. I had some luck using the if command on smaller list boxes but I can't figure out how to get multiple cells of information to come out correctly
View 1 Replies
View Related
Feb 9, 2012
If I make a selection using a drop down, can I have it auto populate another cell with information associated only to the made selection? For instance, if I select January from drop down in A1, I want it to auto populate B1 with 100.
View 2 Replies
View Related
Jan 29, 2013
I 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.
View 4 Replies
View Related
Jul 11, 2014
I want to change value if i change products from drop down list.....
For more information please find attached file: Book.xlsx‎
View 8 Replies
View Related
Oct 29, 2008
When I use the GetOpenFilename() method, it seems to default to the particular users My Documents. Is there a way to force it to default elsewhere like a share drive, or the users desktop?
In the same line of thought, if I have a file named TestFile.xls. and I want to try to open it from the users desktop if they have it, how can I do this, since their desktop location is different than mine?
View 12 Replies
View Related
Nov 11, 2013
I'm trying to create a database where when 3 dependent drop down list selections are combined, information is displayed.
I've tried VLOOKUP and INDEX/MATCH but to no avail.
Attached is the spreadsheet and what I am trying to accomplish. On the first sheet ("budget form") is a table named "Event Costs - room hire". I have created three drop down lists that interact based on what you select. Some rooms offer different services and so are dependent on the selection.
However, I want the final box to display the price depending on what is selected in the previous 3 lists. The amounts and a table I have created is in the "Product Database" sheet and the table is labelled, "Room Hire Control C".
View 5 Replies
View Related
May 22, 2012
I have a drop down (look up list) labeled Equipment that retrieves information from another sheet in my workbook.
I have a second field labeled Equipment Sub Type where I need a drop down list based on the information retrieved in the Equipment Field.
Each item in the Equipment List Drop Down has a corresponding list in my lookup sheet of my workbook.
View 3 Replies
View Related
Sep 16, 2013
The comment boxes keep changing shape and location in my spreadsheet. Some are completely smashed together, while others are extraodinarily wide. I had them all to the right of the cell they applied to; but now they are all over the place.
View 1 Replies
View Related
Sep 28, 2007
I have a file that has 114 rows of data. One for each office I'm working with. I need to insert 6 rows in between each of those rows and type text into some of the fields. I recorded a macro (since I don't know how to write one), but it uses a static location for the rows and data, as opposed to a relative location. So, what happens is, I get the same data entered over and over again. Also, even if I get this to work right with a relative location, I still would have to run it 114 times. I'd like to create one macro that would do the entire document. Here's what I've recorded: .....
View 9 Replies
View Related
Feb 24, 2008
When someone right clicks on a cell and chooses "Hyperlink" current folder is being displayed as a default.
is there a way to change this for a particular workbook through VBA or API calls ?
I would like to always have "c:" as the default folder no matter where the workbook is stored.
View 9 Replies
View Related
Sep 5, 2009
I have a line in my VBA code that i need to change the location of Sheet1 to another workbooks. Instead of Match at Sheet1 at the same workbook I need to match with sheet1 at another workbook called ("Master.xls). The Original Line:
View 3 Replies
View Related
Feb 7, 2007
I am trying to achieve can not be based on a range of rows or cells it must as this data is imported from a forecasting application and the location of the targets may change.
Perhaps an offset to the current selection can be used some how, but have a look-see if you know where Im coming from.
'I have used this to find a target in a data range.
Dim r1 As range
Public Sub FindDataIn Range(r As Range, target As Variant)
Set r1 = r.Find(target)
If r1 Is Nothing then
Msg Box target & " was not found"
Else
r1.Select
End If
End Sub
'Then I inserted and Named this procedure to find the data on various assumptions or targets - SUCH AS THE VALUE "14306".
FindDataInRange ActiveSheet.Range (A1:A226), "14306"
Selection.EntireRow.Select
Selection.Copy
Selection.Insert Shift:=x1Down
Selection.EntireRow.ClearContents
Then I write this again for another Target such as 14307 and it repeats
The result is that it finds the target cell I get an empty row above the two rows that contain that target. (LET ME EXPLAIN WHAT I MEAN BY TWO ROWS)
The thing is there are two rows containing 14306 in that range and the range is sorted ascending so that they are positioned one under the other. Each row has different forecast totals beside this number because one is an export SKU and one is a Domestic.
What I want to do is combine the two rows as one with one row of forecast totals for the number rather than two.
Like this:
14306big Widget Domestic 26 89 (This is combined as a new row)
Instead of this:
14306big Widget Export 12 14 (These 2 rows are then deleted)
14306big Widget Domestic 14 75
Also the Forecast totals run across 12 columns (one for each month) and then there is a column for year totals of each row that needs to remain the same.
Most important this here is this cannot be based on a range it must as this data is imported from a forecasting application and the location of the targets may change.
View 9 Replies
View Related
Aug 23, 2013
My problem is I cannot copy from a Form Control drop down box, and I need to do this for ~10 drop down boxes in about 200 xls files. The files follow a template so the drop down button Inputs are uniform. Here is some more detail:
I’m building an Access Database so that our data (currently in many, many Excel Spreadsheets) can be efficiently queried. The old system was that when asset data was collected, the data would be entered into an Excel spreadsheet, using a Template. So for example:
Project A1234
Name
Main Street Pipe
High Street Pipe
Ref No.
12345
12346
Installation date
22/08/13
19/07/12
X Coordinate
55667
99212
Y Coordinate
77889
12364
We have hundreds of these files, but I managed to get all the files in one folder and Paste and Transpose the data from the relevant Cell Ranges in each file (using a VB sub) into ONE summary sheet. Success; or so I thought.
Unfortunately for one generation of the template (people changed them slightly every year), the template had people enter information into Drop Down Menus. Not a problem when they’re associated with a cell, but these were free floating Form Controls.. so you can’t actually click and copy from them, or call them in VB as you would a specific cell.. afaik.
So my issue is how can I extract the data from those Form Buttons? I’ll try and provide as much info as I can:
Firstly I tried to find some sort of identifier for each Form Button. The dropdowns are called Combo Boxes, (Developer tab > Insert > Combo Box (Form Control) ), and when you select them (by right clicking) and hit the View Code button in Developer Tab > Controls the following shows up in VB.
Sub DropDown44_Change()
End Sub
I’m guessing that the number is related to the button so that’s one useful thing, an identifier of sorts. There’s no actual code there though really from what I can see.. My problem is I have no idea how to extract from DropDown44!
The range of values in the drop down comes from a separate sheet where there’s just a list of the values. There aren’t any cells linked to the drop downs though. If I enter a cell in (Right Click) Format Control > Cell link: then it prints the number (e.g. 4) of the value in the range. So if the drop down options are “Monday”, “Tuesday”, “Wednesday”, “Thursday” and “Thursday” is selected, the linked cell says: “4”.
They didn’t link a cell, then I could call that cell in a VB sub and the job would be done. Unfortunately they didn’t, so I tried Recording a Macro and selecting the drop down form control.. That didn’t really work, except if I right clicked it, I got some more info from the macro VB:
ActiveSheet.Shapes.Range(Array("Drop Down 19")).Select
So is my drop down an Array? My understanding of an Array is that it’s a list of arguments.. all I get there is the name of the button. I am not experienced in VB so this is as far as I’ve got. Perhaps I can get a sub to select the button using the above line, but I need the info from it, i.e. which value is currently selected in the Drop Down.
(Using Excel 2010 but the .xls files are from ~2005)
View 1 Replies
View Related
Mar 11, 2014
I have the following code written but I'm wondering if it's possible to modify this to change the red line to update to the path that the workbook is saved in? Meaning that User1Folder1 would change but [Workbook1.xlsm]Sheet1'E1 would always be the same.
[Code] .......
View 2 Replies
View Related
Sep 28, 2008
I use an image analysis program ImagePro which can call Excel within it's macro language (which seems to be visual basic). I looked up on this site how the change the drive (ChDrive command). But still when the Excel section (after With oExcel) executes the default file location in the browser is in My Documents on the C: drive.
Prior to this code Excel has been launched by this ImagePro macro, and a file Cumberland Template has been opened from the C: drive. Now I would like to do a Save As, but have Excel start at the I: drive location.
View 7 Replies
View Related
May 30, 2008
I have a workbook with approximately 30 worksheets. This workbook already has a massive macro that I've written. I'd like to write a code that will take a designated worksheet, check to see if there is data in cell A2, if so, save it as a new workbook.
The name of the workbook should be predetermined, for example "SIA April(Previous Month) 2008(Current Year) P-Card Import Template.xls"
The months and years will need to change.
The file to save it in will also change monthly- for example- G:PCard DirectoryCloses2008 ClosesApril(previous month) 2008(current year)
View 9 Replies
View Related
Oct 6, 2009
I am trying to develop a spreadsheet that will calculate a cost based on a matrix. I am attaching a sample of the calculation created so far. The end result is in cell M13 and is highlighted in yellow. I kind of layed the formula out in a few different cells, so hopefully it would be easy to follow.
simplify this process with maybe another formula that I might not be aware of, or maybe show me how to get this done in VB code. I think VB code would be the correct way to go just not sure.
View 6 Replies
View Related
Jul 28, 2009
I have attached an example file.. Basically I want the text in a range to change to red if one cell="Description".
Also, I want a cell's text to turn blue if it's corresponding data in the row says "Click on Title to Follow Link"
The example file explains it a lot better..
Thanks for the help.. Sorry if this may seem like a duplicate post but I was not able to get any of the examples I searched for to work that others have posted.. Also, maybe conditional formatting would be an option here but I could not get it to work as it will only meet the first condition that comes across thats true...
View 14 Replies
View Related
Mar 9, 2012
I have a graph which displays information from a vlookup.. The vlookup can bring either numbers which are in percentages format or ordinary number format.
The question I have is is it possible for the graph to automatically change format depending upon the information who is looked up..
If so how can this be done..
View 1 Replies
View Related
Apr 28, 2008
if a macro or private macro is necessary, I would need basic instruction how to use those as well (I mentioned private macro as I understand they automatically run whenever worksheet info is changed, which I would like in this example.)
I'm simply trying to change fonts in cells based on the inputs of other cells. Obviously, using conditional formatting doesn't allow for this.
For my example, assume the word "MrExcel" is in cells D2, F2, H2, J2, L2, N2, P2 and R2. If I type different font names (Times New Roman, Arial, Bookman, etc.) in the cells to the immediate right of each word "MrExcel," -- cells E2, G2, I2, K2, M2, O2, Q2 and S2 -- I would like the "MrExcel" word in each corresponding cell changed to the corresponding font to the right. I would like the ability to do this for up to 15 different fonts. There will be many rows to this worksheet I need to use this process for.
Is this possible? I'm in a bit of a jam and have exhausted my knowledge of formulas in that they won't work in this case ... I'm assuming I need to delve into the unknown land (to me) of macros, private macros, or VBA.
View 9 Replies
View Related
May 6, 2014
Please see the attached workbook.
I would like to create a bar chart which is linked to a drop down box (from data validation) that allows me to choose of the 3 sets of data to show in the x axis.
So if i choose "HALF YEAR", the bar chart will have two bars (one is "jan-jul" the other is "jul-dec") with the green section data if i choose "QUARTER YEAR", the bar chart will have four bars ("jan-mar", "apr-jun", "jul-sep", "oct-dec") with the blue section data if i choose "INDIVIDUAL MONTHS" i have twelve bars....with the red section data.
View 2 Replies
View Related
