I'm trying to link objects and pricing to groups via a combo box. If the user selects Group A from the combo box and enters Object A on the line item, I want the price associated with Object A and Group A to populate.
Group A
Group B
Group C
Group D
Object A
$1
$3
$5
$7
Object B
$2
$4
$6
$8
Object C
$3
$5
$7
$9
Object D
$4
$6
$8
$10
I created a table where based in several competences anyone can evaluate performance and potential. My issue comes when based on that result (colum Y in "Perf&Pot" Sheet) I want to export that to a nine box matrix (Sheet 2 "grid" in the attached excel). As could be several people in the same box I wasn't able to com up with a formula that solves this.
Pasta1.xlsx
A B C
1Timber$200A-rating
2Metal$500C-rating
3plastic$100B-rating
A1:A3 was input into combo box list. If I select an option in combo box, how can I make B11 and B12 to update data accordingly? (ie, if Metal was selected, B11 should show $500 and B12 should show C-rating)
On a userform, is there a way in which to set a scrollbar's slider postion to the ListIndex value of the item chosen in a ComboBox?
In other words, if the total ListCount for this choice is is ten, and the item chosen in the Combobox is in the fourth position, can the scrollbar slider automatically be set to the fourth position rather than be all the way to the left or right? I would think that this would make it visually easier for the user to understand in which direction they can scroll the list. Here's what I've got so far:
I have three combobox...if user click in the option 1 the combobox2 change the values to show and when the user click en one of the option of combobox2 change the options of the combobox 3
If combobox1.value = 0 Then
Set c = Sheets("Codigos de Area"). Cells(1, 2)
While c.Offset(x, 0).value <> ""
ComboBox2.AddItem c.Offset(x, 0).value
x = x +1
Wend
And I can change the code for one select case.
I have a name list on sheet2, column A that will have names added to and removed from. Everytime this list is modified it sorts in ascending order and creates a new sheet named after the name added. These items are added to an ActiveX combo box on sheet1. I need to know how to link the item in the combo box to the corresponding sheet(ex: item: "Jim", sheet name: "Jim").
View 2 Replies View RelatedI need to have a single combobox pull values from a very large term list (>40,000), and place selections in a single column in sequential cells (eg, B1, B2, B3, etc..). With the following code (credit to Ingolf and MickG) I am able to get the list to popluate cells in a column. However, I cannot seem to find the proper attributes to control the auto selection of items in list. I do want the autocomplete feature so the user can quickly navigate this huge list, but as soon as i start typing, it starts populating terms into the growing list. I would like to have the user actually select the item.
Private Sub Worksheet_Activate()
Dim Rng As Range
With Sheets("Listing")
[Code].....
I want to have 2 cells where I can enter 2 numbers (length & height) so that a grid can be plotted. For example, a big sheet of paper size is length 30" x height 8", if first number is 10" (length) is entered, it will plot 3 lines across 30" (cos 30" divided by 10" equals to 3) and if I enter 2" (height) on 8", it will then cut it into 4". Attached a file for better understanding.
View 14 Replies View RelatedI'm not worried about the order of the data. What I want is information on turning a grid into a list.
View 4 Replies View RelatedI'm creating a fairly large risk analysis spreadsheet which uses a fairly standard 5x5 risk matrix.
I need a function or a formula which refers to the score a cell in a 5x5 grid, if given the row and column values, like this (where the columns are probability and the rows are impact):
VL
L
M
H
VH
VL
1
2
4
6
7
[Code]....
Example.xls
Within my attachment, I have a grid of data, Row A are headers, and beneath, various corresponding dates, there are no row headers.
I'm trying to do a lookup so that column header is returned bases on a exact cell value.
Example: inputting 03/03/2014 will return "Week 3"
Week 1|
Week 2|
Week 3|
Week 4
07/07/2013
|14/07/2013
|21/07/2013|
28/07/2013
[Code] ......
I have a worksheet ('Matrix') that has a grid that is 40 rows x 40 columns. On another sheet ('Drivers') I have a two columned table where a user is enters two numbers between (you guessed it!) 1 and 40. What I'm trying to do is within the grid, have a "Y" appear on the crossing point as denoted by the table. I've tried a couple of experiments with MATCH, but I'm not getting very far.
I've attached an example document to make this a bit clearer and I've coloured the references on the 'Drivers' sheet to correspond to where they should appear on the 'Matrix' sheet. Sorry if that's a bit garbled. Let me know if you need any clarification.
about using list as a datagrid type. I was able to research some samples and applied it on my own, but unfortunately I am missing something again here. The data on the worksheet are not being populated when my range is on the AA column. It works with A1 column but not the far ranges. Im not sure about the limit of range anyway here is the
View 14 Replies View RelatedI'm having an issue with VLOOKUP, I'll keep it short as I don't think I need loads of details because I have done some troubleshooting and believe got to the cause of the problem... I just don't know how to fix it!
View 4 Replies View RelatedThis is to inform you that I frequently use toggle grid tool to make my worksheet white/plain,doing this with the help of mouse is eating lot of my time which i really feel anonying/pain.
I would like to have a short cut key for toggle grid which lies in Forms toolbar.
I am trying to modify the code below to toggle the snap to grid on/off or off/on and is giving me an error.
Sub ToggleSnapToGrid(Optional Dummy As Long)
With Application.CommandBars.FindControl(ID:=549)
If .State = 0 Then
.Execute
Else
.State = 1
End If
End With
End Sub
I hope someone can help me with this, Ive attached a copy of an example spreadsheet that has the same requirement i need for another one i am creating.
Sheet 1 has the user input area, where the user enters the data they want stored.
All i need is to be able to transfer the data into the correct area on the grid on sheet 3,
Looking to expand grid in Excel 2003,
View 4 Replies View RelatedI have tried to make cartesian coordinates (X)and (Y) using excel 2007. now I want to represent some shapes (circles, squares, triangles, and so one). My problem now is with formating those shapes. I use this path (Insert > Shapes),, but the shape I add cover the grid,
I want (for example) a circle but I need only its circumference, and without covering the grid by its area. the same for other shapes.
How can I set up my sheets so that the background and grid are isolated, in other words only the background, the grid, and my data shows on the sheet, the rest will be blacked out. I need to email only the certain area of the worksheets.
View 3 Replies View RelatedBasically, I need to extract data from a grid/table (11 columns wide) and transpose it into a list for use in a pivot table. It like sort of reversing a pivot table to access the original data but I only need 5 of the columns repeated for each occurrence.
I have read many internet sources but they don't seems to fit my needs. Here is a solution from a similar question posted on StackOverflow entitled "How to “flatten” or “collapse” a 2D Excel table into 1D?"
The attached simplified mock-up explains what I am trying to do. I have colour coded the data to understand. The number of rows (10) will always be the same but the number of columns (Branches in this case) can extend for many columns.
I want to create a function called PasteGrid(SourceRange As Range, GridHeight As Integer, GridWidth As Integer)
What this will do is take a long single row or column of data and paste it into a grid with a defined GridHeight and GridWidth, I have came up with the below so far:
Code:
Function PasteGrid(SourceRange As Range, GridHeight As Integer, GridWidth As Integer)
Dim rRange As Range
Dim rCell As Range
Dim DestinationRange As Range
Dim GridWidthCount As Integer
Dim GridHeightCount As Integer
[Code]....
Something appears wrong and when following the code when it hits this line:
Code:
Cells(Destination.Row + GridHeightCount, Destination.Column + GridWidthCount).Value = rCell.Value
The function just stops and I'm not too sure why?
I have a huge number of excels that are formatted poorly. They basically look like a checkerboard of data. Looks similar to the illistration below. The X's represent Data and the periods blank cells.
X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
. . . . . . . X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
. . . . . . . X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
. . . . . . . X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
. . . . . . . X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
Except that this goes on for over 100 columns and 30,000 rows. Each block of data is about 4 to 6 sheets printed. My boss does not want to get any blank sheets, only sheets with data on them.
I can see the pages outlined after I Print Preview. But I really don't wat to Ctrl+Click and set the print area thousands of times. Is there a macro that can do this?
I was thinking something along the line of check the range of cells for each page, and if the page has data in it, then set that page's cell range in Print Area.
I have a grid to determine interest rates. The are add ons to the rate that are dependent on two factors - the credit score and the Loan to Value percent or LTV, both of which are shown in terms of ranges (720-739, etc). See the screen shot below.
I have already written formulas for converting the score and LTV to the ranges as they appear on the sheet (ie, if you type in a score of 722, the formula converts it to the range of 720-739.
What I need to do is this - when the score and LTV are input and determine which set of add ons come into play, I need the sheet to take those add-ons, identified by an x next to it at the top of the page, and add them to the base interest rate, giving me the final rate. Again, see below to make this more clear.
I've thought of using VLookup, but I don' think that would be applicable here.
What's the best method, and can you give me an example of what a formula might look like?
Loan Level Pricing Adjustments
Base Rate 6.25 6.25
Credit Score 741 >740
LTV 60 740 Cash Out 0 0 0 0.25 0.375 0.375 n/a >740 Investment 1.5 1.5 1.5 2 2.5 2.5 n/a >740 2-unit 0.5 0.5 0.5 0.5 0.5 0.5 0.5 >740 3-4 unit 1 1 1 1 n/a n/a n/a
720-739 all -0.25 0 0 0 0 0 0 720-739 Cash Out 0 0.125 0.125 0.375 0.5 0.5 n/a 720-739 Investment 1.5 1.5 1.5 2 2.25 2.5 n/a 720-739 2-unit 0.5 0.5 0.5 0.5 0.5 0.5 0.5 720-739 3-4 unit 1 1 1 1 n/a n/a n/a
I have created a form for my customers. It is attached.
In creating the form, I have merged cells so that the area behind my text boxes will appear clean (no lines).
However, I cannot remove the lines around certain cells see, e.g. range K21.
I would like to make the form as "pretty" as possible for my clients (and it would help me look good to management).
I'd like to automatically populate a 9 box grid using data from Excel. The number of names for each rating can be anything from 1 to 100 so the size of the grid would need to be adjustable. I would like one table for each function and grade. Attached is what I'm trying to do. In the 'example tables' sheet, I've shown how I'd like the tables to look, but with my live data the tables would be bigger so I'd like to do one table per worksheet (or possibly one function per worksheet - so 3 tables).
View 3 Replies View RelatedI sometimes import data in the copy-paste way from completely different sources, eg. a program on the web or whatever, which has arranged the data in a table.
It is often succesful with the data neatly arranged in the spreadsheet in columns and rows and all, but sometimes the gridlines disappear even though 'cell format' is set to 'none' in 'grid lines' and the settings have a mark for 'visible grid lines' and their color is 'automatic'. Only the imported area misses the gridlines.
It is of course possible to set some grey gridlines manually in cell format, but then I get them on the print also and I don't want that.
There used to be a function called "snap to grid". What happened to it? I want to draw a line and make it "snap" so that the ends of the line are at the intersections of cells.
View 3 Replies View RelatedI have the attached spreadsheet which makes the question easier to understand. Basically when you read across and down rows and columns, I'd like the intersecting cell to turn red if the horizontal and vertical row columns letters are different. So in the attached B5 is red because B1=A and A5 is B. Where the intersecting cells match in values, the cells will stay white.
I can create a formula to conditionally format one cell to red: =A2<>B1
But I have a large grid and it would mean re-creating each formula for each cell. How to expand it to the whole range?
macro to automatically size an XY grid based on user input (see picture - [URL]......)
I want to enter a value in B6 that will automatically create a new grid or edit the existing grid to increase or decrease it depending on the desired grid size.Once the grid has been sized I want to enter a data table into it, therefore the grid would need to be cleared of contents before it could be resized.I think these are the steps that are needed:
- Enter grid size.
- Area (named range based on existing grid values?) is cleared of the data table.
- new rows / columns are inserted with correct XY coordinates shown based on newly entered grid size
- data table written into the new grid