VBA Code
I would like to combine data into one cell based on check box selection. I have 25 check boxes all named CheckBox1, CheckBox2, etc...
The data will be placed in cell "Q10".
Checkbox 1 would use the data from "AL130"
Checkbox 2 would use the data from "AL131"
etc...
There could be any combination of Checkboxes selected.
Each selection must be separated by a comma.
I have a spreadsheet in Excel, there are 13 columns of information being used. 3 of the columns have just data I typed in (model name, item code, original price) the other 10 have formulas (these formulas are price discounts that will be taken off of the original price. 9 of them also have a check box on the top of the column so if the the checkbox is selected, the formula will give the customer the amount discounted off the original price(keep in mind that not all of the columns can be used together, for example, on product "A" maybe only 3 of the boxes can be used whereas on another product maybe 5 can be used). I made a multiple listbox, so that if a customer selects a product or multiple products and clicks the ok button, the sheet will only show the specific products they selected. My problem is that when I press the "OK" button nothing happens. I don't know how to link all of this together.
View 3 Replies View RelatedIn the workbook, 3 columns of importance Column N,S and AC. Concatenate order ("S"&"N"&"AC") - combine it and show value in column AG. Column AC has either 0 or any other number (50,60,100 etc). If it is 0 I need to insert an 'F' at the end of the code in column AG, any other numbr and i need an 'O'.
Column N has contract code, this will be in the middle, usually its alphabets like RF,EF, but sometimes numbers like 21,33, etc but when its a single digit number like 6, i need it changed to 06 (need the 0 infront). Column S has exchange codes, and this has to appear in the front of the code in Column AG. Same as Column N, if there is a single digit number then put a 0 infront of it.
I have a column say column A. It has a pattern of cells where there is a code C10A3(alpha numeric) in say A1 and then in cells A2-A9 there are descriptions (alpha only). This continues uniformally down to cell 300. So every x amt of cells down a code appears and then below this for x cells is a description. What i would like to do is create a macro to concatenate the code waith each of the descriptions and paste it in the adjacent cell to the description. So....
A1 =C10A3
A2 =Global
A3 =America
A4 =South AMERICA
A5 =aFRICA
ETC
A10 =D05a9
A11 =Global
I would like to concatenate A1 and A2 and have the output in B2 (C10A3Global.)
And A1 and A3 with the output in B3 etc. This should continues until A10 (where the cell contains alpha numeric characters) where nothing is entered in B10 and then in B11 there should be D05A9Global....
The only difference between the description and the cell is numeric characters...
I have a list of people to send drawings out to. I have already made the selections am just trying to work out how to simplify the output so as to use as a field in a data file to go onto a letter.
"| A01 | A02 | A03 | A04 | A05 | A06 | A07 |" are for Joe Bloggs
( | are cells)
In another cell, i would like to merge them together like so
"A01-A07" are for Joe Bloggs
And also as follows
"| A01 | | A03 | A04 | A05 | | A07 |" are for John Smith
would like this to be
"A01, A03-A05,A07" are for John Smith.
So differing conditions depending on what happens in the range. I have had a couple of attempts at this using a VBA concatenate command, I can get them to display like "A01,A02,A03..." with a selected separator, but cannot get it how i want it.
I am looking to write a formula that can concatenate cell A$1$ (absolute) & B1: B245 (dynamic)and then A$2$- B1:B245 and then so on.
So for example, I need to concatenate cell A$1$ & B1, and then A$1$ & B2, all the way up until B245, and then I need to restart the sequence with A2 and B1.
Formula to preform this concatenation, or will this need to be done with VBA?
In one of the worksheet. I have list of questions in column A.
In column B I want to have the following 3 checkbox
1> YES
2> NO
3> Not sure.
Based on the question, user selectts one of the check box.
User should not be able to select multiple checkbox. i.e. User can select only 1 out 3 possible options.
I have the following code working, it pickups cell values then used them to make an email in outlook.
What I would like to do is tick checkboxes to make a selection so that it can generate emails from only what I have selected. As i will be adding new IT Problems to a sheet and want to inform users of their problem number to tracking issues.
I have a userform with a CheckBox and a ListBox. Is there a way to have the CheckBox to automatically get checked if a selection is made in the listbox?
View 9 Replies View Related I need to concatenate varying numbers of cells based on duplicates found in a separate column, but I'm not sure how to approach it. I have 41,000+ rows of data, so I have to find a formula.
Example:
1AB2Denton, PaulFB357D4D3OwensTest, MarcyFB539F934Brennan,
JosephFB539F935Bowser, AmyFB539F936LaRock, ChuckFB667D3B
Based on duplicates in column B, I want to combine the data in column A into one cell. The duplicates in column B could be only 2, or could be 20+.
Sampling table :
one
two
three
four
one
two
three
one
two
one
Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")
one , two , three , four
one , two , three
one , two
one
Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?
My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,
I am trying to create a spreadsheet which allows me to select an antibiotic from a drop-down menu (refer to cell D1) on Sheet 1.jpg, which would then color the cells below based on the color-coded coverage based on another sheet (refer to D2-D17 of Sheet 2.jpg).
View 10 Replies View RelatedWhat I'd like to do is fill specific cells with specific information drawn from other pages based on a combo box selection.
For example, if I were to pick 10131 in the combo box, I'd like it to fill out cells X, Y and Z with information from the three cells to the right of 10131. Assume X, Y and Z are not touching.
I am working on a spreadsheet that is essentially a question and answer based document .... I need to set up the macros so that if a certain response is chosen from a list, it will automatically update the cell / row next to it with a pre-determined response ....
The way the spreadsheet is set up is that the first column has the question, the second column has the answer (yes, no or N/A - from data validation - as a list) and the third column is blank - but is titled "comment/action" .....
For example: if the question is "have you locked the car" and the answer column selects "NO" then I want the "comments/action" to come back with "you need to lock the car", and if the answer is "YES" then I want the comment/actions column to remain blank ...... The same with N/A ......
I will have around 100 questions in the document ...... I know I need to set up the responses on a separate worksheet - i just dont know what the macro's are to identify and place the appropriate response from the worksheet to the relevant comment/action column .....
I have created a named range that includes raw data layed out in 4 columns (Finish Name, Fill Color, Pattern, Font Color). On a separate sheet, when one of the finish names is selected in a particular cell, I want the cells fill, pattern, and font color to change according to the raw data. Below is the vba code I am trying to get to work. However, it is not working. It does not break, but does not achieve the desired result. It looks like the variables are getting set correctly, but still not changing the cell when they are referenced. I am only testing it on the first finish color, as seen in the code. All of the rest are static values for the cell, but I want the values to be dynamic.
Sub SetFinishSeparatorColor()
ActiveCell = frmFinishSeparator.cboFinish.Value
Set rngTarget = ActiveCell
With Sheets("Pick List Do Not Tamper").Range("Finish_Cell_Color")
lngFinishCellColor = . Find(What:=rngTarget, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1)..............
I am trying to use a row of cells as update cells, where the user inputs an amount into greyed out cells, which in turn updates Sheet1!$I$2:$I$11, from which it will update other worksheets that are currently in progress. but I do not know how to go about it. Is it possible?
For each amount the user enters (for each Code Number) a date will be displayed below the amount.
I am working on a macro and this section has me stumped. The worksheet columns are A:AE. Occasionally, the procedure needs to select a varying number of cells in a row which don't always fall in the same column, count the number of cells in the selection and then paste the data on the row above it, in column ("AE" minus the Selection Count).
Here is what I have so far:
[Code] .....
The red-bold bracketed code is the syntax I'm not able to figure out how to phrase.
I have two sheets that I am using. sheet 2 has a list of experiments in column C with information regarding the experiment in columns F - J. On sheet 1 I have a dropdown box with a list of all the experiments. When I select an experiment from the dropdown box I would like to populate columns K - O on sheet 1 with the information from columns F - J for the selected experiment on sheet 2.
View 9 Replies View RelatedI have created a pricing sensitivity for a list of products. In cell C3, users can select a product (i.e. Apple, Elmo, or Bowl) and based on this selection, a range of Prices and Units are then updated and the Revenue for each price point calculated. A few lines beneath this, I have a little summary table that lists all the products. I'd like for users to be able to enter in the optimal price point for each item and then have the Units and Revenue for that selected price point for that specific product automatically update.
For example, if I select Apple from my drop down list my price choices are $5, $10, $15; units are 100, 85, 70; and revenue are $500, $850, and $1,050 respectively. On the summary table, I would like units 85, revenue $850 to update automatically if I select/input a $10 price. This would happen for all the products so at the end I want to be able to calculate a blended revenue mix given my pricing changes.
A project I am working on calls for me to implement a macro that will clear the contents of a cell (but not affect the validation list it contains) based on the selection of another cell or cells. So on the simplified attached example, I need to achieve the following;
On the fist row of options, if cell "C3" is selected, then the contents in cell "D3" are cleared. If cell "B3" is selected then the contents in cells "C3" AND "D3" are both cleared. Similar is needed for the second row of options;
If Cell "C6" is selected, then the contents in cell "D6" are cleared. If cell "B6" is selected then the contents in cells "C6" AND "D6" are both cleared. Finally, if cell "A3" is selected, then ALL of the above cells' (B3-D3 & B6-D6) contents must be cleared.
Is it possible to be able to select a range of dates, and a time interval, and then to automatically fill in cells in a column? to be more specific, I want to be able to select, say 1 Jan 2013 to 31 dec 2013 in one box, then specify a time interval, for example "hourly" and then a column is populated with each interval, i.e. 1 jan 2013 00:00, and then the next one is 1 jan 2013 01:00 etc. I have tired the normal automatic fill tool but it never seems to get it right. It works for the first few days then it all goes funny.
View 3 Replies View RelatedI am trying to find something that would allow me to have a spreadsheet clear the contents of a certain cell based on the selection made from a data validation list which resides on the same row. This can best be described with an example.
The user selects anything but " " or "none" from the validation list, which for this example resides in C5, would trigger code to clear the contents, if any, in AA5. If " " or "none" is chosen the value remains.
This capability should be available for each row through 100.
I need to make a dropdown list (I know how to do that) and based on that selection, I need some cells below to automatically complete. How can I do that? Is it possible with or without VBA?
View 5 Replies View RelatedI'm using Excel 2007. I would like to seek some advise on how i may update cells after selecting a month from a drop down list.
i have created a simplified version of what i intend to have. One the 1st tab 'Cash Budget 2009', i have filled up a table with numbers, sorted by months. On the 2nd tab 'Dec09', i would like to create an expense table, to be able to be selected by month. So i have created a drop down list based on the months that are created in the 1st tab.
Upon selection of the month from the drop down list, i would like the cells to display (fetch) the data from the 1st worksheet. It would also be helpful if the name of the 2nd tab can be updated to read as the month that is being selected.
I've created seven names in my data validation source list on Sheet 3 in the following range of cells, A2-A7. My data drop down validation list is located on Sheet 1, A2 in the same workbook. I would like to trigger a macro based on the name I select in this drop validation drop down list that will fill out an address, telephone number, and email address in the following cells next to my data validation drop down list on Sheet 1: B2, B3, and B4. I want it to be designed so that users will select a name and have that person's corresponding address, telephone number, and email address fill right next to their name on the same sheet.
View 6 Replies View RelatedI have a workbook that has two worksheets. One worksheet is an input worksheet. A user will select a date from a drop-down list and type in the events that occurred on that date in 8 adjacent cells. The user selects a button that advances date and clears form. On sheet 2, whatever was typed into sheet one is saved via VLOOKUP formula. However, if I try to change something retroactively and select a previous date from the drop-down list, it clears everything in sheet 2 that was typed for any selected day.
View 1 Replies View RelatedFirst sheet has a client name and several cells of data along the same line. All of the data on this sheet is kept long term.
The second sheet is completed copied and deleted. In completing the second sheet the client name is selected of a drop down list and pulled from sheet one.
What I want to do and not sure if there is a way is that when I select a client and populate it on sheet two I would like the related data on that clients line from sheet one to populate to same named lines on sheet two.
if this is possible and if so provide a brief description of how it would work
If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.
View 1 Replies View RelatedI have attached a portion of an excel file I am using at the moment. What I require is a piece of VBA code which will allow me to copy selected data based on a combo box selection. What needs to happen is this :-
If the selection in the "Index" worksheet combo box related to "Month" in cell G19 is for instance "December", I would like the macro to compare this cell value to the cell values in cells C96, C124, C152, C180, C209, C236, C263 and C290 in the "Tech Services" worksheet and where the values match.......copy the commentary (Range C126:Z147 in December's case) to the range C34:Z55.
I want to have a drop down list in a cell so that the value in the cell can be only selected from two columns of data. Additionally once the data from the first column has been selected I want to be able to limit the inputs the user can select from in the second column.
e.g. In cell C115 I want to have the value BDS05.
I want to be able to select the value BDS from a drop down list of values and once that value has been selected I want to be able select 05 from a list of values from 01 to 14
If I select BCS as the first value then I want the second set of values to be limited to 01 to 02 etc.
I have read about combo boxes and list boxes and I'm a bit confused about the best way to achieve this (or even if I can).