I have a spreadsheet set up for my son to roll up D&D characters. There's a combo box to select Race and another for Class of character. What I now need to do is automatically insert the correct set of racial and class bonuses into the relevant squares.
The Combo box points to a list of Races on Sheet 0, in cells A3 to A12. The cell reference for this returns a value between 1 and 10 depending on race selected, i.e. 'Dragonborn' yields a 1, and 'Tiefling' yields a 10. Cells H3 to H12 contain the Strength modifier for the different races.
When I select for example a Dragonborn (1) in the combo box, I want the Strength Modifier cell to pick up the value from cell H3, and if i change the selection to Tiefling (10) I want the strength modifier cell to pick up from (obviously) cell H12
How do I use a variable in the cell reference to adjust the cell data is retrieved from?
I was using the formula below which was working fine for copying across 20 columns and down however many rows, but now the requirements have changed to 90 columns. I have tested this out with the 90 columns but the Indirect function is bogging down the spreadsheet with the constant recalculating.
I rearranged the formula to the one below, but how can I have the reference to the sheet name changed as in the formula above without using the Indirect?
I'm using a template that has formulas using a $ sign to attempt to stop the ranges they refer to changing. The problem is, when I use the template (which involes Access importing some data and adding columns to the sheet the formula refers to in the process) the rows referred to in the formula change in line with the number of rows of data that have been imported. Only the row numbers change not the column headings. So for example:
I have been the code below in many a different form since royUK first helped me find a certain value and delete the entire row. I've used it to find values, text, multiple conditions and so on and so forth, but I cannot figure out how to compare the cell to the cell directly above.
way to get Excel to automatically expand the cell height when the text becomes to long to display. Is there something I am missing that will accomplish this, or do I need to write some VBA that will make the cell larger once the character limit that will fit on one row is exceeded?
I frequently have to copy information from one excel sheet and paste it into another. But when I do this the formatting doesn't carry over. So I have to go to each individual cell that I pasted into and adjust the size of it. How do I copy and paste so that the formatting/cell size is preserved?
I have a spreadsheet at work . I am tracking the quality target for the department and I need to create a formula that adjust accordingly to the target set for month end which is 90%.
I have build in the foreacasted numbers for the whole month but I need the formula to indicate the minimum error points needed to achieve the target of 90% for month end when I replace the forecasted numbers on a daily basis with actual numbers.
I have attached the spreadsheet which will be clearer.
I have cell C5 value set as 5*B5+3. Now, using vba,I would like to adjust the value of B5 (basically find it), so that the value of C5 is let's say 8.
In this easy example, one can find rapidly the correct value of B5 to be 1.
What I've tried is the following code:
Private Sub CommandButton1_Click()
Range("B5").Value = -5 Do Range("B5").Value = Range("B5").Value + 1 Loop Until Range("C5").Value = 8 End Sub
So, I am setting the initial guess for cell B5 and then increasing it by 1. However, if I set the increment to 0.5 instead of 1, program goes crazy and loop doesn't close.
What changes should I introduce to this code, so that even if the increment is 0.001 everything will work.
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D 1 Task___________Trigger_____________Days_____Due Date 2 Design begins__Proj OK______________10____10-Jan 3 Specs written__Design begins (A2)____5____15-Jan (D2+C3) 4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
EXAMPLE:
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
I have a workbook with 1000+ worksheets, all of which have 3-letter names. On a master sheet, I would like to make a query of how many non-empty cells there are on a subsidiary worksheet. This works:
Code: =COUNTA(ABC!A:A) What I'd like to do from time to time is input in column A a varying set of 3-letter worksheet names, say
AAB ABC CDE
And have a formula in column B that converts this to
Let's say I've ended up with the number 8 in Cell D4 for example. Is there a formula that can return the letter "G" (The 8th Column) so I can use it in future cell references ? If so, let's say we store that in Cell B5. How do I now refer to a cell in a chosen Row of that same Column by reference to Cell B5 ? For example if I want to refer to Cell G33 can you refer to this Cell in some form like Cell(Contents of Cell B5;33) ??? Don't want to use R1C1 type references if possible.
I have the code below that is two separate activities and I want to change the second activity from a cell trigger (Set KeyCells = Range("K42:AD42")) to a button trigger. I need to first to remain unchanged.
I'd be ok if this was just one macro that I could assign to a button but because its two and I need to write the second's to clicking a button I'm over my head.
Its occured to me while writing this that because it'll be a range of buttons I'll probably need to make each one an individual code? Is this the case? If so I may have to just keep this as it is.
I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.
Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following
I have a vlookup into a row of cells. Sometimes the new text is so long it has to word wrap. How can I get the ros to expand with out me manually doing each row, and then go back to one line with the data changes.
I am new to Excel VBA - Normally, If u move your cursors to the corner of your userform,we will see the single-two pointed arrow pop-out and then we can adjust our own desired size of the user-form.
I have a piece of vba code that uses the date in column D to group by month and subtotal the amounts in column E.
The problem is the subtotal in column E doesnt adjust. So if a user of the spreadsheet adds or deletes rows the subtotals (and grand total) do not up date.
Dim LastRow As Long Dim NextMonth As String Dim R As Long Dim Rng As Range Dim SubAmount As Currency Dim ThisMonth As String Dim TotalAmount As Currency Dim Wks As Worksheet
I have a vlookup into a row of cells. Sometimes the new text is so long it has to word wrap. How can I get the ros to expand with out me manually doing each row, and then go back to one line with the data changes.
attached a sample, with the page settings, print area set, row height, etc... all set to my needs. I would like vba to adjust the horizontal pagebreaks so that a "box" is never split into two parts. in attached sample the pagebreaks should therefore go below row30, row54, row124 and row128. Setting pagebreaks is easy, but still want VBA to do it for me.
I have a list in excel.......(see attached) In the attached you will see some empty boxes for this purpose I have only provided a dummy duplicate. In the original I have information in these boxes.
Problem: When i go to print preview the list boxes fall half way down the page instead of at the top of the page......when I try to adjust the margins in print preview, manually, or either in page setup it doesn't rectify it.
I've used various "zoom" searches and can't find what I need. My users have different amounts of toolbars so the amount of screen space changes. I need to zoom so 4 graphs show. Am looking to enable the user with a scroll bar where the user could click / slide the control to change the zoom size on the screen so they can see the 4 graphs at their preferred "zoom".
I would prefer a control on the sheet and link it to a macro (or line of code if that's the proper phrase) and let the user decide (some people will want bigger zoom due to eyesight etc).
Private Sub ScrollBarZoom_Change() ScrollBarZoom.Value = ActiveWindow.zoom.Value End Sub
But I get Runtime error '424': Object required. Auto Merged Post Until 24 Hrs Passes;Nevermind. I found it. Answer was:
Private Sub ScrollBarZoom_Change() ActiveWindow.zoom = ScrollBarZoom.Value End Sub
