In simplicity: We want to be able to enter a value in cell A1 (and hit enter), but we want the value in A1 to be increased by the value saved in H1.
So: if H1 had 250 saved in it, if we enter 150 in A1 (and hit enter), we want the value in A1 to become 400.
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.
View 9 Replies View RelatedI 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.
View 9 Replies View Relatedi am trying to build a spread sheet showing materials in a stores tools, materials,and usage ect. i have put all the formulas in for example cell c =sum(b2-a2) to get a remaining stock left after usage but what i need to know is a way to add more new stock onto the sheet because every time i go to add on new stock it just resets itself is their anyway of doing it so that when you add new stock to cell a for example it automatically adds on to the total in cell c??
View 1 Replies View RelatedHow or if it is even possible to have a table automatically adjust its range or the number or rows it has based on another table
I have two tables:
Table1 is raw data that is added two daily
Table2 has formulas with column header references to Table1 that processes the information and converts it to a readable format. (basically barcodes to words)
Is there a way to get Table2 to automatically resize to the same number of rows that Table1 has whenever data is added??
I have a worksheet used for a car game. Each row is designated to show one particular car that can be used in the game, so let's say that there are 40 cars taking up 40 rows.
Column A shows the car type and model; column B shows the engine that the cars has represented by a number (the higher the number the better the engine is); and so it continues, Column C shows the transmission; column D the brakes etc.
Underneath the 40 rows (lets say from row 45 to 65) I then have a table with spare-parts that can be used to replace the cars existing parts. This Parts-table is also arranged with engines in column B, transmissions in column C, brakes in column D etc...
This point of this layout is that it should be easy to compare each type of part in the spare-parts table with the corresponding type of part already used in any of the cars as everything is perfectly lined up by the columns.
About the spare-parts table: because the inventory in the spare-parts table often are added to or subtracted from, as parts are won in races or being put on the cars, I have created a macro to sort the parts in each column so as to put the best parts at the top of the table and the bad ones towards the bottom - so far so good.
The problem is this - as I add new cars, the table of spare-parts is pushed down worksheet and that means that the macro no longer will reference the right rows. So the question is this, how do I automatically update the macro to adjust for rows being added above it? I am not really interested in redesigning the worksheet.
how to automatically set a text box size to display text contained by right-clicking the textbox and put a check in the auto-size box under "Alignment".
However I would like to set the widht of the text box and automatically adjust the height according to the number of lines of text. Is this possible by VBA code or othe means?
I really DO have a problem. I need excel to create me a list of whenever someone enters their name. Now, this may sound simple, but, the text in the list CANNOT be allowed to be overwritten.Also the text needs to be entered into the NEXT empty cell between C30 and C50. This is basically so I can keep a record of whenever anybody does anything/modifies the cells that include text between S7 andS21
So basically if text is ever entered into S7 all the way down to S21, I need an automatic copy created into the next available cell between C30 and 390. It is also ok if there are duplicates values(names).
I would give a print screen, but I don't know where the attach button is on this forum.
I have two files which are attached.
One is the main report file "ops report.xls" and other is the raw file (Air.xlsx) from which data is to be added to this file. i manually take the data and enter it into the ops report everyday. This i want to automate as there are many such sheets to be completed (i have mentioned just "Air" sheet here in ops report).
Now what exactly i want to do is?
take data from Air.xlsx workbook as per the columns in the Air sheet in Ops report and paste it on that particular date row. this i will have to do everyday so the nest day the data will be pasted on next row against the date. The yellow columns which i have marked have formula so you need not touch that columns.
I nee to paste the average of Air sales and Air service. so we have to take average of row 43 and row 89 for first two columns in ops report. and then average of row 44 and row 90 for column L,N,O,P,Q in the ops report.
Afterwards the average of column J must be pasted in "Interval" sheet in ops report.
This is a bit tough but i know there are many genius people out there who can easily solve this.
Is there a way to get cells in a column to adjust their height automatically, or with a macro to account for text overflow/wrap?
For example, lets say a column is 200 px in width and it's height is enough to view a single line of text, and the column is set to wrap text. Now more text is entered into the cell and it needs to be re-sized to fit two lines of text.
Is there a way to create a macro button to have this done automatically?
Is there a way to make a chart adjust automatically when you add to the source data? I'm trying to use a named range in order to power a chart (something like chart_data=offset(a1,,, counta(a:a),counta(1:1)); the range is working fine). When I put "chart_data" into the source field for the chart, it seems to work fine; it picks up the right range... But when I later add more data, the chart source data field seems to have converted my named range into a hard-coded range, absolute range. So the dynamical named range updates, but the chart doesn't and I don't see the new data. Am I doing something wrong that I need to tweak? Does what I'm trying to do just not work? Is there another way to make the chart pick up the new data without adjusting the range?
View 3 Replies View RelatedHow do I enter a value into a cell and divide that number automatically by another value all within the same cell.
For example..
I want to enter 20 into a cell and have it change into 17.24 (20/1.16) automatically all within that same cell.
Range("B25").Name = "EndMull"
Its fine but if i insert a new row or column then it mucks the whole thing up. Is there away of naming them but if any cells, row or columns are inserted the range will automatically adjust to suit1
I have an excel sheet which has a macro which captures the current time in a cell with the formula =now(). What I am looking for is that once a user runs a macro and the time is inserted into that cell then that cell should be locked and cannot be changed then. If this can be done by any formula / function or VBA.
View 2 Replies View RelatedI remember years ago that I made a couple formulas that on sheet one would transfer to another sheet when I enter in sheet one.
View 5 Replies View RelatedIn a sheet I enter the following:
... in A1 a year (say 2012)
... in A2 a month, formatting as "MMM" (JAN, FEB, MAR etc.)
How to automatically get in column A (say from A3) all the dates of the month entered, formatting as "D/M/YYYY" (e.g. 1/1/2012, 2/1/2012/ 3/1/2012, etc.)?
I want to concatenate two Cells into a single cell BUT have the first field left justified and the second cell right adjusted.
A1 = "John Williams", A2= "Single"
A3 = "John Williams Single"
I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?
View 2 Replies View RelatedI would like to do some calculation with percentage as it showing in the attached file.
[url]
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.
How to do that in EXCEL VBA?
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?
Is there a way to make a macro to make this true?
The WS is 300 rows,
The column width is 6.90
If LEN(B1)>147 then format ROW Height to 0.25
If LEN(B2)147 then format ROW Height to 0.25
If LEN(B300)
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
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.
View 6 Replies View RelatedHow to adjust the shape of the chart in the worksheet? I try to write the code, but can not work.
ActiveChart.Shapes.ScaleWidth 0.87, msoFalse, msoScaleFromBottomRight
ActiveChart.Shapes.ScaleHeight 1.5, msoFalse, msoScaleFromBottomRight
I currently have dates in a column in the format DD.MM.YYYY. I would like to convert this into MM/DD/YYYY, with the slashes instead of the periods.
View 9 Replies View RelatedI 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
I have this VBA (from a recorded macro) how can I amend it so that these two formulas change depending on the number of values....right now the range is to 129 only because we only have 129 cells of info but some sheets will have more some will have less. I want excel to base it based on the column I which used to be H.
VB:
Sub SLCREPORTDATA()
Cells.Select
Range("F1").Activate
Cells.EntireColumn.AutoFit
Columns("H:H").Select
[Code]....
In need of a formula to trim data:
Examples:
* 5 Extravagant Mak (nz) (100) Need the number plus a dot then space then the name, then minus everything after the name, so this would now read 5. Extravagant Mak thats 5dot space Extravagant Mak
*
11 Frisbee (100) This one would become 11. Frisbee thats 11dot space Frisbee