Populate Adjacent Cell Based On Pull Down Choice
I have three hidden columns (Text only) one column with Parts, then Description, then Cost. I have a pull down menu (i.e. Cell 3, A) listing all of the choices in the hidden Parts column. I want to automatically populate Cell 3, B with the corresponding Description when a Part is chosen from the pull down menu, and the same in another column for the Cost.
I am using Excel X for Mac, in OS 10.5.2
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Double Lookup And Pull Adjacent Cell
I need is spread across various rows. In a separate sheet I want to be able to lookup two identifiers and pull the number needed in the adjacent row. The first identifier is a 3 letter character, the second is CURREVO and I need the number to the right of CURREVO. My problem is CURREVO is not always in the same column, but in relation to the 3 letter character is always in the same row. A B C D E F GDMGCURREVO52011.25YTDREVO243085.00DTYCURREVO11892.50YTDREVO59783.50ECUCURREVI1943.00YTDREVI 5,541.25CURFRQI4.00EEGCURREVO32864.75YTDREVO205426.75EICCURREVO658761.26YTDREVO3507022.68EILCURREVO335741.70YTDREVO1720830.72ENTCURREVI161242.39YTDREVI638681.84CVLCURREVO796266.21YTDREVO4816890.98ENDCURREVI34479.19YTDREVI44074.54EYECURREVI11880.12YTDREVI 108,007.02(Null)MICCURREVO1098694.15YTDREVO5766072.54NEUCURREVI25251.90YTDREVI 158,236.60CURREVO207.00 i.e. in a seperate sheet, I want to pull NEU, in that row I want to find CURREVO and bring back the adjacent number = 207.00
View Replies!
View Related
Populate Drop-Down Based On Another Drop-Down Choice
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 Replies!
View Related
Transfer TextBox Value To Cell Based On ComboBox Choice
I thought I had finished my project but I keep getting errors, the latest one being that I have 2 comboboxes on userform "timekeeping". When I press the commandbutton "Submit", I want the values in the textboxes on that form to be placed in the spreadsheet, depending on what the selections the user has made in the comboboxes but I keep getting an error saying that the macro doesnot exist in the workbook even though it does! The file is too big to upload here so it is found on rapidshare [url]
View Replies!
View Related
Populate Adjacent Cells From Single Dropdown
I have the need to create a spreadsheet that will populate multiple fields in the spreadsheet based on a drop down selection, i.e.: In A1 I will have a drop down with 300 items. When an item is selected the next four cells will populate with predetermined data. Example: For each person I have information that applies only to that person like so: Bob Red Hair Blue Eyes 6' tall California Jan Blond Green 5' tall Florida Ian Brown Brown 5'5" Texas If someone chooses bob from the drop down, I need the next four cells to display the information relating to bob.
View Replies!
View Related
Automatic Time Stamp Cell Based On Adjacent Cell
I am looking to add a function to a current spreadsheet that writes the current date to a cell when another is updated. The sheets function is to have a user record when a particular action has been completed and then remind them after a given amount of time. The user is faced with an option to input a "1" to essentially "start the timer" and then the date that is automatically input by the code will be used to compare with the current date. I have tried the following on the Workbook_SheetChange sub If ActiveCell.Column = 9 And ActiveCell.Value <> 1 Then ActiveCell.Offset(-1, 2).Value = Date End If But cannot figure out how to eliminate user error. Let me explain. If a user enters a value (will only ever be a 1 to indicate "yes") into column 9 then the date appears in the correct place (two cells to the left) If however a user deletes a value then the date will update one cell too high!? I also thought of using a cell based if statement (if j7 = 1 then today() else "" but this only updates with todays value each time you open where I want the date to stay as the day the cell was updated. The date is going to be used as a way of working out when to change the original value of the cell in column 9 to "".
View Replies!
View Related
Sumproduct Based On Adjacent Cell ID Code
I am trying to get the sumproduct of a column of amounts based on the adjacent cells ID code. For example column B will have codes 1FHD, 2FHS, 3FHG etc. these codes will be mixed otherwise I would have just did a sum of the cells. Column C would have dollar amounts. Sumproduct based on adjacent cell ID Code
View Replies!
View Related
Combining Cells Based On Value Of Adjacent Cell.
I have been reading through trying to find a similar post but the nearest i could find was one dealing with numbers rather than text. This is how the workbook looks. Column A contains a list of numbers based on vehicle types. Column B contains a list of vehicle names. A B 1 Car, 1 Taxi, 2 Bus, 2 Lorry, 3 Skateboard, 3 Bike, 1 Motorcycle, What I am looking for is the best way to combine the text from column B depending on the value of column a. the finished results would then end up as: "Car,Taxi,Motorcycle,", or "Bus,Lorry," etc. This is just a simple list.. The one I am working with has around 2500 entries. I have been trying to do this using the CONCATENATE function and IF statement but it's just not working..... My head is ready to explode
View Replies!
View Related
Calculation Value Based On Month Name In Adjacent Cell
I have a spreadsheet spread over with dates in column A from September to December and I have a fuel rebate column as well. The fuel rebate is different for each month and is calculated as a certain amount of cents per dollar added to the total amount in Column B September: 26c October: 13.5c November: 43c December: 66c e.g. If there is an invoice for 10/9/07 (september) for $100, then the fuel rebate would be $100*.26 which equals $26 fuel rebate. I want to calculate column B (this is the column with the dollar amount in it), but I want the calculation to be different depending on what date is in column A, so if in column A the dated month is October, then the calculation would be something like B2*0.135. give me the calculation I am certain you don't need VBA for this right (just nested IF statements?)
View Replies!
View Related
Link Cell From 1 Sheet To Another: Pull Data Based On Time
I'm trying to make a floor plan which shows what seats are available depending on the time it is. So I made sheet1 = to the map (where the seats are located), sheet2 - rows = seat #'s, columns = Time (broken down by half hours). Now i have conditional format to show "t" in red (which means taken), "f" in blue (which means free), and "n" to show in gray (which means not working). What i need is example - Seat # 200 in sheet 1 to show red, blue, or gray depending on the value in the row that corespond to this seat and the column that corespond to the current time.
View Replies!
View Related
Pull Data From Closed Workbook Based On Cell Values
I want to pull data from closed workbook based on cell values of open workbook of column B and the source file name is on cell J1. Actually I save monthly files and opening balnce of current month should take vakues from previous month file. Suppose current month is May 2008. Then Column Column D for May month shold take value from column G of April 2008. For simplicity the previous month’s name and thus source file name will be placed on cell J1. The code should loop from column B of source file and current May 2008 file and should pull values for only those items which are in the current file in the Column B. Thus those products which are deleted or newly added item in the current item should not copied. Though for new item no name will be thre in the source file but for deleted items the item might be there in the source file but the code should ignore those value.
View Replies!
View Related
Populate Cell Based On Worksheet Name
I have a macro that copies data from 6 different worksheets into one summary worksheet - based on certain criteria - I need a way to note on each row of the summary sheet which worksheet it was copied from. Each worksheet is specific to a salesperson, so ideally I would like that salespersons last name to populate in column B on the summary page for each row of theirs that is copied over....here is my current macro I use to copy the data: (this code is repeated for each sales persons sheet - "Blankenship", "Dew", etc...) I have attached a scaled down version of my file.
View Replies!
View Related
Enter A Value In A Textbox, Search For It In An Worksheet, And Populate Other Textboxes With Adjacent Values If The Value Is Found
I want to enter a value in a textbox, search for it in an worksheet, and populate other textboxes with adjacent values if the value is found. Anyway, the problem is that if the value is not found, I get a debug error. Dim test1 test1 = TextBox1.Value Worksheets("data1").Activate Find_Range(test1, Cells, xlFormulas, xlWhole).Select TextBox2 = ActiveCell.Value TextBox3 = ActiveCell.Offset(0, 1).Value I'm sure there's far better code to do what I need, but I tried to keep it simple. With the above code, it only works if the value is found. If it's not, I get an error. So how do I make it so that if the value is not found, the value of the textbox2 is "Not Found" or something...
View Replies!
View Related
Conditional Formatting Date Cells Based On Entry In Adjacent Cell
I have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M. I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting: Cell value is less than or equal to =TODAY() All's great.....but - I would like the date to turn back to black if I enter a date in the adjacent received column, column M. In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.
View Replies!
View Related
Populate Cell Based On Multiple Criteria
I think the best thing is to take a look at the attached, then read what I'm looking for, as it would make more sense... That being said: what I am looking to do is change cell L3 based on new criteria in cell M3. Right now if B3=Stationery World then L3=Stationery. What I then want to happen is if E3=Toner then M3=67200 which then has L3=Equipment Consumables and NOT stationery.
View Replies!
View Related
Populate A Cell Based On Range Of Data
I need to modify the code below: PHP Sub Macro1() Macro1 Macro Dim strbody As String For Each cell In Range("B2:B640") Sheets("MASTER").Select Cells.Select Selection.Copy Sheets("Final Merged").Select Sheets.Add ActiveSheet.Paste Next End Sub The code above is creating a new sheet for each different value in column B. I would also like to take that value and place on the newly created sheet in cell A17. Also, how would I get the sheet to be ranamed to match the value in cell A17 of the newly created sheet?
View Replies!
View Related
Automatically Populate Cell Based On Worksheet Name
I have a macro that copies data from 6 different worksheets into one summary worksheet - based on certain criteria - I need a way to note on each row of the summary sheet which worksheet it was copied from. Each worksheet is specific to a salesperson, so ideally I would like that salespersons last name to populate in column on the summary page for each line of theirs that is copied over....here is my current macro I use to copy the data: (this scrpit is repeated for each sales persons sheet - "Blankenship", "Dew", etc...)
View Replies!
View Related
Populate Cell Based On A Dropdown Selection Of Another
I found many examples on this forum of how one can pull in data from closed workbooks, or copy it and have it stored in the active sheet. So far so good, but I need a solution which would do the opposite - I need the macro to take a range of data form the active sheet store it in a closed workbook and save any changes in the target workbook without opening it.
View Replies!
View Related
Module To Find & Move Based On Cell Adjacent's Set Value
ABCDE FGHIJK If the above is in different cells, I want to be able to move the data in "G" to the cell beside "E" (up a row and over 4 columns) IF the cell to the left of "G" equals what I specify is in "F" (for example use "product"). I would want to apply this to an entire sheet, not just once. If the macro (not sure what its called) could also then delete the row that contained "G" after it moves it, that would be even better! The output in the example above would be: ABCDEG F HIJK
View Replies!
View Related
IF And OR Statement (populate A Cell Based On Which Number Meets The Criteria I Define)
I am trying to populate a cell based on which number meets the criteria I define. This is based on sales revenue, so if the revenue is less than $6.5M, I want to use a certain value. If the value is equal to $6.5M but less than $8M I want to use another value and finally if the revenue is greater than $8M i want to use another value. Here's my formula, but it returns $0. =IF(B2<6500000,Bonuses!J45)*OR((B2>6500000)*AND(B2<8000000),Bonuses!F45)*OR(B2>=8000000,Bonuses!B45)
View Replies!
View Related
Lookup Data For Drop Down List Choice & Show Blank If No Choice
I currently have a drop down menu in one of my worksheets, in which I have several different text values entered. What I would like to do is link each of those text values to a numerical value, which would be entered in to another cell. So if I select "Option A" from my drop down list, and Option A is equal to 200, I want "200" to show up in another cell. If I select "Option B" from my drop down list, and Option B is equal to 400, I want "400 to show up in that same other cell.
View Replies!
View Related
Drop-Down List To Be Based On Choice Of Another
I have a drop-down box(K6) When a style is selected I need to have data show up in other drop-down boxes (H3,L3,P3) These selections would only pertain to the selection in K6. They would change when a different style is selected. There are multiple choices in the secondary drop-downs. [IMG][/IMG]
View Replies!
View Related
Result Based On ComboBox Choice
I'm looking to set up a combo box with different year options and add functionality so the data that can be seen on the sheet at a given time is driven by the combo box year value. (functionality somewhat like a webpage, where once you choose a certain value from a drop down box, you see data corresponding to the value)
View Replies!
View Related
Filter Table Based On Choice In Another
1. I am starting with two independent tables, one with ‘ItemA’ and the other with ‘ItemB’ 2. The user must be able to select an itemA which will point to itemB list, showing only a specific pre-defined group or combination of the itemBs. 3. This pre-defining of the itemB combination will be done uniquely for each itemA selection Example: Selection of an itemA, row 2 will show, say, an itemB rows 2, 4, & 7 only. Selection of itemA, row 3 will show an itemB rows 4, 5, & 9 only. Selection of an itemA, row 4will show an itemB rows 2, 7, 11 & 13 only, etc. 4. In reverse, the user also needs to be able to select an itemB and display all itemA’s common to that itemB. Using the example above, the entry or selection of an itemB, row 2 will show rows 2 & 4, itemA. This is represented visually in the attached GIF.
View Replies!
View Related
Fill Drop Down Based On Choice From Another
What I want to do is lets say in the drop down menu I have options Food, sports, Movies. now if I select Sports then it should give me a sub caterory options like baseball, hockey, football and not the options for food & movies as well - so as to make the choices lesser and more user friendly. How do I create that - is it too tough - I am a novice in excel. Can you upload an example sheet with tips on how you did it. I have uploaded an example.
View Replies!
View Related
Go To Sheet Based On ComboBox Choice
I have a combo box that I have populated with choices (months of the year). I require users to select the month they wish to view their payslip with and then press "Go" (a command button) and jump to the worksheet that contains their payslip info. How do I get VBA (Excel 2007) to do this please? I have tried on the CommanButton1 code page to type If combobox1.value = "May" Then Goto Worksheets("Sheet3") This doesn't work and I know I'm doing something wrong as I remember when I was doing my project at school you had to give each combobox selection a value like 0, 1 or 2 but I cannot remember how to do that!
View Replies!
View Related
Drop Down List Based On Choice From Another
What I need to do is create a Drop Down box. When a user makes their selection it gives another drop down box from what they selected. Each choice in the 1st drop down will give a different drop down box in the next field from what they selected in the 1st. I am having troubles getting this to work.
View Replies!
View Related
Setting Color Of Range Based On Adjacent Cell Color
This is probably elementary, but I'm struggling and would appreciate any help as I have very little excel VBA experience to draw from. I have assembled code which changes the cell color based on a value change in Column A. Column A will contain many different groups of repeating values. This code works well and and I have been able to figure out how to limit the number of colors to only 2. The end result is each set of similar values in column A is visually grouped by one of two alternating colors. The number rows in the data set is variable as the data set is extracted from SAP. The number of columns is fixed. What I want to do now is set the cell color in columns B through F the same color that was assigned to the row in column A. So if cell A3 is set to colorindex = 6, then I want to set the range of cells B3 to E3 to the same color. Here is the code I am using to set the color of the cells in Column A:
View Replies!
View Related
Change Font Color Based On Adjacent Cell Color
I have two columns. The first one (A) contains cells that have different Fill colors. The second column (B) contains text adjacent to the colored cells. I am trying to change the color of the text in the second column (B) to the corresponding color in the adjacent cell in the first column (A). I don't think conditional formating works well in this situation. I believe the solution would be some sort of macro.
View Replies!
View Related
Return Data Based On Drop-Down Choice
My Main data in my workbook is copied from another workbook. From there it is sorted into separate worksheets for each customer via a macro. I want to be able to set something up so that I can list the customers and then list job descriptions, products and pricing from the correct sheet and return the selected data back to its original form in sheet1. I am also trying to run the macro that sorts the data in this workbook from another workbook. I have attached the file so you can see what i am trying to explain.
View Replies!
View Related
Lookup In Table Based On List Choice
I have a way of getting a cell value from having a data validation linked to a row of cells containing lookup formulas.. which in turn are looing at a table. I have attached a small example. The problem is the spreadsheet is a large one and i was hoping I could shrink the size of this by removing the vlookup formulas by having some kind of index/match/lookup formula inserted directly in the data validation. I got this to work using nested IF formulas but its not very practicable! The attached example has data validation in column A that selects the task type. Choosing this then changes the values in the vlookup area.. which then becomes the validation list for cell in column B.
View Replies!
View Related
Select Column Based On ListBox Choice
If I've Listbox1 in "ABC.xls" which contain selected headers of workbook "XYZ.xls". How can I select entire columns in workbook"XYZ.xls" when press enter at Listbox ? Do I've to put the code here ? With Me.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) Then ? ? ? End If Next i End With
View Replies!
View Related
Return Value Based On Validation List Choice
I have created a cascading data validation list, and I would like to have an adjacent cell auto populate according to the dropdown selection. Data Eg: Oranges 1100 Apples 1101 Pears 1102 Grapes 1103 If the user selects "Pears" from the dropdown list, I would like the adjacent cell to populate with the numerical code for "pears", in this case 1102. I am designing a new expense report form at the office, and I am at a standstill with this problem.
View Replies!
View Related
Function To Return Value Based On OptionButton Choice
Ihave 2 functions which are called in my main program which should return a value due to what the user selects on a userform using option buttons and check boxes. Function getnum() As Integer If userform1.OptionButton1.Value = True Then getnum = 1 End If If userform1.OptionButton1.Value = True Then getnum = 3 End If If userform1.OptionButton1.Value = True Then getnum = 5 End If End Function Function getlevel() As Double If userform1.CheckBox1.Value = True Then getlevel = 1.2 End If.......................................
View Replies!
View Related
Conditional Summation Based On Drop-Down Choice
I have attached a spreadsheet that I need to automate. Cell C3 is a drop down box of the raw data found on the raw data sheet. I would like B6-8, c6-8 and d6-8 to automatically updated and change depending on what users select from the drop box in C3. (so basically im trying to re create the yellow table with formulae/ vb.)
View Replies!
View Related
Auto Fill Based On Drop-down Choice
I'm working on a workbook that already exists and that contains multiple worksheets (A,B,C,D,...), each with specific info (each row consists of an item name (item 1, item 2, item3,...)and all characteristics of this item). We now want to add a help page for each worksheet : A => A help B => B help ... So for each item (=row) in sheet A, there should also be an according row with additional info in A help First, I used static hyperlinks to switch from the item in A to the corresponding help in A help. However the item list can be extended in the future (meaning rows inserted) and this will cause these links to fail. Therefore I switched to dynamic hyperlinks by inserting names (in stead of the original cell names) for each item in both A and A help. This involves a lot of work since you have to give all the involved cells a unique name and you have to refer to this unique name when you set up the hyperlink.
View Replies!
View Related
Go To Range & Sheet Based On Combobox Choice
3 work sheets nameWorksheets "InfoData" to save month NameWorksheet "Jan" for JanuaryWorksheet "Feb" for February1 x ComboBox = monthBox 4 x CommandButton = Week1Button,Week2Button,Week3Button,Week4Button --------------- I have Problem opening worksheet from userform. Name of the months are in Combox. So when I select month January from comboBox and click on Week 1 command Button it should open worksheet named "Jan" and Select Cells(2,1) to show Week 1. And Same for week 2, week 3 and week4 but will select different Range or cells. I have attached the screenshot of userform and also the workbook. Private Sub Week1Button_Click() For i = 1 To 13 If monthBox.Value = Worksheets("infoData").Cells(i, 1) Then Worksheets("jan").Range(2, 1).Select End If Exit For Next i End Sub
View Replies!
View Related
Select Worksheet Based On ComboBox Control Choice
i have a user form with 4 combo boxes and one text box. what i've tried to do is make it so that if the month combo box reads january, then all of the info is placed in a worksheet called january. at the moment it is putting all the info inputted into one sheet. if some one could take a peek at the code below. If cbomonth.Value = January Then Sheets("January").Select Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) Or IsEmpty(ActiveCell.Offset(0, 1)) Or IsEmpty.....................
View Replies!
View Related
Multiple Choice - Select A Value Based On Its Probability Of Occurring
I have the following table: http://img85.imageshack.us/i/tableb.jpg/ Each question is multiple choice (either A/B/C/D/E) and the values listed are the probability of each letter occurring. What formula would I need to put in the 'output' column for it in each row to output a letter based on the probabilities. E.g. in question 1, most of the time it would output E, but sometimes (rarely) A and very rarely B/C/D.
View Replies!
View Related
|
TRACKER
