is there a way that i can insert cell values in a userform
i have data which needs to be displayed on 33 lines
line 1 cell value from d10 then space cell value from d185 then space cell value from d186
this needs to be repeated
line 2 cell value from e10 then space cell value from e185 then space cell value from e186
line 3 cell value from f10 then space cell value from f185 then space cell value from f186
line 4 cell value from g10 then space cell value from g185 then space cell value from g186
this continues trough to line 33
two formulas for one data set. The data is attached in the spreadsheet: "Product IDs". The data is a set of Master Product IDs (parent) and the Linked to them Products (children). I need to create a relationship between unique parents (Master Product IDs) and their children (Linked Products)
I need to create two formulas:
1. From the Data Set table, need to vlookup the unique value in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). So, the result will be as shown in Table 2.
2. From the Data Set table, need to vlookup the unique (de-duplicated) parent/children relationship in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). There are total 3 parent/children relationships in Table 1. So, the result will be as shown in Table 3.
I am trying to merge three cells using the following formulae =CONCATENATE(A3,": ",B3,", ",C$1,", ",C3,"") where C$1 is a title (header) I will use if C3 is populated. However, I would like to leave C$1 out if C3 is not populated.
Then loop from 1 to RowsNumber and concatenate values in cell(2,2) but I need, if this is possible, to define a FUNCTION (nested functions) in cell(2,2).
I have a bunch of cells (could be varying amounts from 2 to about a 100). I need a macro which can find out how many rows in column A contain values and then i need to pick values from each cell in column A and put them in Cell B2 seperated by a comma. eg. Column A Column B 22 22,35,67,34,56 35 67 34 56 Column A could contain as many as 100 values.
Depending on the result of a formula in R67, one of three things will occur. If the result is a 1, UserForm1 is displayed. If the result is a 2, UserForm2 is displayed. If it is nothing, nothing happens. Ok, I can do that.
However, with the code below, whenever I select any cell, and there is a 1 or a 2 in R67, the userform pops up. I only want it to pop up if I change a value in C6 which in turn changes the result of the formula in R67.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("R67").Value = 1 Then Run "UserForm1" End If If Range("R67").Value = 2 Then Run "Userform2"
[Code] .......
I've tried setting a monitored range. I've tried the code below in one form or another and it didn't work.
Code: If Not Intersect(Target, Range("C6, R67")) Is Nothing Then Macro
I have two sheets. sheet2 is a lookup table. sheet1 is information sheet. i have created a userform where a user inputs data into 3 textboxes and clicks a button. i have the code for transferring the data from the textboxes into row after the last used row on sheet1, columns a,b,c. in columns d and e i have vlookup code that takes the value in column c and performs the lookup function to return values found in sheet2. this all works fine.
I am trying to display the vlookup results in a 4th textbox on my userform.
I have designed a multiple list box on my userform and display it on one of the cells in the spreadsheet but with each list item separated by a comma when its display on excel.
The problem with my code is the delimiter appears at the first list item, but it should only appear if more than one list item is chosen.
|Apple|Orange
I was thinking if there is something like if gItem = 0 then only display sFruits Else display what I have below???
VB: For gItem = 0 To FruitsList.ListCount - 1 If FruitsList.Selected(gItem) = True Then sFruits = sFruits & delimiter4 & FruitsList.List(gItem) End If Next .Cells(gRow, "AO").Value = sFruits
following on from previous post (http://www.excelforum.com/excel-prog...g-up-form.html) what if the selected cell is a merged cell (cell count is greateer than 1), what is the best solution to enable double click event on merged cell ?
I am using concatenate to create a list of bottles based on other data, so that each cell contains a different combination of bottles.
The problem is that the list appears but there is also FALSE for every bottle that doesn't relate to that cell.
Is there a way to create a formula such that nothing appears if it is not true? And is there a shorter way of writing what I am after, I can't quite finish the formula becaise it is so long.
I need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...
I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.
I have a problem where I am linking data from two worksheets say: worksheet 1: A1 = 10, A2 = 20 worksheet 2: A1 = =Sheet1!A1, A2 = =Sheet1!A2
I have filled this formula through a certain number of cells on sheet 2 so that if i add more data to sheet1 it will appear in the appropriate place on sheet2. Is it possible to show the formula results in the formula bar, i.e. sheet2: A1=10, A2=20, such that i can used 'Find' or 'Autofilter' on the cell values rather than the formula?
I'd like a formula to be inserted in cell A4 that returns all products listed in row 1 that have FALSE in row 2. Is this possible? I tried a HLOOKUP and a MATCH yet it wasn't returning all occurances
Am using Excell for some times and understood the basic operations like VLOOKUP and other formulas, and it was really usefull. Now i have another requirement where i need to get all the values matching from another tab.
Below is the example: I will be entering all the values in Tab 2 manually. As well as i will be entering the Categories (Column A) in Tab1. I want the Values (Column B) in Tab1 to be populated.
TAB 1CATEGORY VALUES
AA1 1 2
AA2 5
BB1 3
BB2 4 6
TAB 2 CATEGORY VALUES
AA1 1
AA1 2
BB1 3
BB2 4
AA2 5
BB2 6
I have 2 problems in that
1) It is not dynamic, i have to change Tab 2 frequently. 2) values are listed below the categories
I would like to have a dialog box "popup" if the value of two cells do NOT equal.
cell C11 has net sales values for the day, including cash, checks, and credit cards. After sub total of these values, I subtract any charges(voids) and get a total(c11). I enter a sales(c12), tax(c13), no tax(C14), misc. amounts(C15 & C16) ,reductions(c21) and put the total in Cell C22 with the formula =Sum(c12:c16)-c21). Because I do not want the formula overwritten, I protect the sheet including the amounts in cell c11 and c22. If they do "Not" match I would like a dialog box to appear so the error can be corrected.
Triggering a message box. one of the worksheets in my workbook is called Update Comments - this is a sheet that contains data in the following format (headers)
B7 = Week Number C7 = W/C D7 = Update Due E7 = Updated By G7 = Update Comments
I have a formula in column D (beginning D8 and copied down for the year) as follows:
In range B4:M4 some cells contain values and some are blank. I am trying to write a formula that will omit the blank values and just give me the nonblank values. The code below returns the FIRST nonblank value, but I can't seem to figure out how to drag it across a row to get the others. I want to ensure that the numbers stay in the same order (so no largest/smallest formulas), but the blank cell values need to be removed.
This would then need to be repeated for (B5:M5, B6:M6, ETC).
I am trying to create a formula to display a string of text that refers to multiple cells. for example, =If(A1=0,"insert text here" &B1 "insert more text here" &B2 "insert even more text here" &B3, "insert text here" &C1 "Insert text here"). It works fine for one cell value, like ="Total: " &A1
I need a formula that will make every make every cell in column A unique. This formula needs to concatenate columns H, M, and N and display the result in column A. As you can see, you can get two instances of the same value (e.g., Dog:Brown:Small for A2, A9 and A10). For this reason, we then need to concatenate a counter number on the end that increases by 1 based on the total number of matching values ABOVE the cell in column A.
For example, I need a formula in cell A12 that concatenates H12:M12:N12 and then counts the number of "Dog:Brown:Small" instances above and then appends the largest incremented value +1. This would result in "Dog:Brown:Small:0004" in cell A12 and "Cat:Black:Large:0003" in cell A13. I will then drag this formula down column A and it will dynamically execute the formula against all column A cells above it.
I'm trying to Concatenate values as per a condition.
The condition is being a member of group: 1 The result should be ACE but I need it to be presented in one single cell (like in C9). (Pls see attached picture)
I tried to use SumProduct but it seems that this function handles only Numeric values.
I was able to present the result in a Range(!) of 6 cells with the Array-Formula: =IF(B2:B7=D2,A2:A7,"") but this is not what I need.
I know how to solve this with VBA but as it is sort of an excercise I need it to be solved with sheets build-in Functions only.
I am trying to concatenate the values of many cells, not the formulas in the cells, to avoid having to repetatively paste special, value. Perhaps something like this: =CONCATENATE(VALUE(AA2):VALUE(AQ2))
we want to add/concatenate the values of first column and show the result in next column. The problem is fully explained in the comment section of the sheet attached. But still if you are having in getting the problem
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....
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 am trying to come up with a user defined function to accomplish several things at once.
First, I need to lookup a reference value in one column and determine the value from another column (on the same worksheet) in the same row. Then, concatenate each "return" value (that isn't blank).
I have the following code so far, but my formula results in a zero. If I remove the On Error Resume Next, the formula results in a #VALUE error.
Public Function ConcatUnique(Separator As String, Ref As Variant, LkupCol As Range, _ RetCol As Range) Dim lkup As Range Dim ret As Range Dim colDif As Long Dim mCollect As New Collection Dim i As Integer Dim b As Variant ' Determine the number of columns difference between ' the lookup column and the return value column. colDif = RetCol.Column - LkupCol.Column On Error Resume Next ' Determine which lookup values in the lookup range match the reference value. ' When the lookup value matches the reference value, set the return range object ' to the cell in the return range (column) in the same row as the lookup value. ' Note we use the difference between the lookup column and the return column to ' determine the location of the return range object. For Each lkup In LkupCol If lkup.Value = Ref.Value Then Set ret = Range(Cells(lkup.Row, lkup.Column _ + colDif)) ' Store the return value in the collection object. Ignore any blank return ' values. Note we use the range value converted to a string as the key ' value. If ret.Value <> "" Then mCollect.Add ret.Value, CStr(ret.Value) ' Loop through each cell in the lookup column range. Next lkup ' Write each item from the collection and the separator to the final result, ' writing each value and the separator after the previous value and separator. For i = 1 To mCollect.Count b = b & mCollect(i) & Separator Next i ConcatUnique = Left$(b, Len(b) - Len(Separator)) End Function
In a sheet I am showing a calendar where a user can choose Day, Month, Year and if he/she presses enter the data is submitted in Sheet 3. This is working fine for me.
In this sheet ( Sheet3) I have the column headings as Day, Month, Year, Date and Submitted By.
Now in the user form I have a command button as Show data (User form Name CmdShow) and Text Box (User Form Name TxtDateLeave) . which I want is that when someone will click on Show data text box will show the data last row value of column D from sheet. Column D of sheet 3 may be Date or Text format.
I have written code for that
Private Sub CmdShow_Click() With Thisworkbook.Worksheets("Sheet3") Me.TxtDateLeave.text = .Cells(.UsedRange.Rows.Count, 4).Value End with end sub
trying to incorporate userforms into my spreadsheets.
At the simplest level, I create a form and display it using userform1.show.
However, I've noticed from some tutorials that the " proper" way to do it is to create an instance of the form before trying to do anything with it i.e.
Dim frm As New userform1 frm.show
way to initialise a userform? Is there a pratical difference between the two syntaxs or in reality does it achieve the same goal?
Is there something special that I *should* do in the initialise event to allocate memory for the form?
