I m trying to get around here within the company I work for.
I'm interested to know how to set a specific range value e.g. "5 to 10" once a value is between those 2 numbers, and then if the value in the next row below is 34.12 it will be "30 to 35".
Here's an example code I constructed, but the darn thing just keeps on running
I have two defined ranges, and they keep changing to include the workbook name. XV2 =
View 3 Replies View RelatedI'm designing a spreadsheet currently. I want to input a number into cell A1 - for example 13. Now the number 13 has a percentage value of 67.3%. When I enter the value into A1 I want excel to automatically fill cell A5 with the percentage value of 67.3%.
I need to have the numbers from 1-20 all with varying percentage values.
1 = 100%
2 = 97.8%
3 = 94.3% etc..
So essentially I would like to enter 13 into cell A1 and for cell A5 to automatically fill in the value of 67.3%, if I was to change the value in A1 to 10 it would also automatically change the percentage from 67.3% to 74.7%..
Macro that could look at a row and take the values of two cells, combine them and then create a range name for a third cell in the row.
For example; for row 5420, in column C there is the word Florida, in column D there is the number 6235, and in column F there is a sentence or two. Is there a way to automatically create a named range for the cell of column F that would be named FLORIDA6235? And if so, can the macro do this for every row even if the word and/or the number changes.
I have roughly 28,000 rows and nine columns that I am working with. That's why I was wondering if there was a way to automate this. There are 10 states and I don't know how many different numbers attached to the states, however there are many state and number combinations that repeat, so there would be several rows with Florida in column C, 6235 in column D but a different description in column F.
I have a worksheet with data in columns F,G,H and I. There is a total in column J. (=SUM(F1:I1)
I've written some code that allows a user to insert additional columns. However, I'm finding it difficult to find a way of changing the calculation in the total column automatically when a new column is inserted.
I would like to automatically color fill a series of rows based on like information from a single column. When the information in the column changes then the rows would either stop filling (this would be best) or fill with a different color until the information changes again.
Summary: series of alternating rows would either be filled or unfilled based on changes from the column information.
Hope this isn't too confusing. Below is an example except I would want the cells filled, not the text or numbers to change. The highlight is changing based on the changing of the numbers.
ABCD 12345
ABD 12345
ABCD 12349
ABDF 12349
ABCD 12358
ABF 12358
I am looking for a way to simply change the values in column B (start time) back by one hour, for instance 06:00:00:00 would become 05:00:00:00. Because this schedule is a broadcast schedule, time is shown in 4 fields hour, minutes, seconds, and frames (hh:mm:ss:ff).
I can't find a way to quickly adjust it, and because my company broadcasts in a lot of different timezones, I spend a lot of time manually adjusting the values myself. It seems frustrating because I know excel recognizes the values, because it is capable of sorting them, of course that may mean absolutely nothing. I have attached a sample file that I hope should make my problem a little clearer.
I have two named ranges, let's call them A and B, each referring to a different single row in a worksheet.
I have another row in which I would like to compute the maximum of the values found in A and B in that column.
=MAX(A,B) gives the maximum value of all the values found over the entire ranges A and B, rather than just those in the current column.
What formula should I use to find the maximum of the values found in range A and range B in the current column?
Based on the attached (much simplified) spreadsheet, I want the values in the green cells on the "Month" page (B6:B10) to vary depending on the value in the yellow cell (B4), and for these cells to to look up the relevant column in the "Full Year" page - i.e. if B4 (on "Month") = Jan, look up C6:C10 in "Full Year"; if B4 = Feb, look up D6:D10, and so on. I know I can nest IF functions in order to achieve this, but could be a bit messy. I'm sure there must be a simpler formula within Excel ... just don't know what it's called!
View 3 Replies View RelatedI'm trying to execute something like this: [URL] ......
but without changing the cell value... I basically want a button that will wrap/unwrap all of the cells in the "B" column with one click. If i have to do the cell range for every individual cell in column B that's fine, but I can't seem to make the above example work while keeping the cell contents the same.
I have a challenge related to a excel sheet we are using for personnel planning.
Vertically we have several projects with action items listed row by row in the following format:
- Project 1 xxxxxx
Project management
Project Engineering
Workshop
Shipping
Etc.
- Project 2 xxxxxx
Project management
Project Engineering
Workshop
Shipping
Etc.
Horisontaly on the columns we have dates, day by day for a year +
For each day we chose from a drop down who is assigned to the action item. The problem is when there are 30-40 + projects and each have 20-30 lines of ation items it is hard to get the overview and avoid double entry. I would use conditional formating to do this, but firstly each project is separated by a line containing different formulas and I would not like the conditional formation to change these cells, and at the same time to put this up manually for each row would take a long time since there are 365 + rows.
how a VBA code could solve this little challenge? IT would also be beneficial to have it do the check upon cell change, that way we would not need to manually run the VBA to check.
Is it possible to format a row of cells (B7:KX7) depending on the value in column (G7:G1000) changing?
For example column G might have the following value entered into them.
A
A
A
B
B
B
C
C
C
So all the rows containing the letter A would be formatted one colour, then it would notice it changed to B and format all the rows with the letter B in, then C etc... The letters would always appear in blocks of 10 or more (representing students in a group) so it wouldn't go A,B,A,B,C,D,E
I am not bothered about the letter being tied to a certain colour, it just needs to change when the letter changes. I'd have no more than 10 different variables in this column, meaning no more than 10 different colours to format. Formatting would be black text with different coloured backgrounds.
I have a list of two columns. Here’s an example. The left most column provides the row number.
_ A B
1 1 0
2 2 1
3 2 1
4 3 2
5 4 2.5
6 5 4
7 1 0
8 1 0
9 2 2
Whenever there are two repeating numbers in column one, I want to reduce certain numbers in column 2 by a certain amount. The amount is determined by half the difference between the number in column B corresponding to the second repeated number and the number in column B corresponding to the row after the second repeating number. The range of numbers that are to be reduced begins with the row after the second repeating number and ends with the last row before number one appears in column A. The values in column A are integers, always starting with one. For example, 1, 2, 3, 4, 1, 2, 3, 1, 2, 3, 4, 5.
For the above example, after finding the repeating number two’s at A2 and A3, it would reduce B4 through B6 by half the difference between B3 and B4 (1/2). The values for B4 through B6 would be updated in column B. The same for the next repeated numbers, which is one at A7 and A8. Half the difference between B8 and B9 is 1.
Here’s what the updated list would look like:
1 1 0
2 2 1
3 2 1
4 3 1.5
5 4 2
6 5 3.5
7 1 0
8 1 0
9 2 1
I am trying to create a sheet on docs.google.com that takes the sum of 1 row divided by the sum of another row removing numbers from the denominator if fields are left blank. Hard for me to explain so here is an example:
Counter
Column 2
Column 3
[Code]....
Leaving a blank value in column 2 makes it read 4/6 giving me 66%, Column 3 giving me 50%.
What I would like it to do is if there is a blank value in any column remove column one from the denominator.
Upon completion the total output percentage for column 2 should be 100%, and column 3 should be 75%.
I have a simple script that puts the save time in the last row of column C when the spreadsheet is saved. Another script puts a username in Column A and column B contains the time opened. I would like to add to the save script to make column D od the active row have a calculation similar to:
View 2 Replies View RelatedThis may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
View 2 Replies View RelatedIs it possible to use the same macro when ranges have been changed? Attached is a sheet that I update frequently (rows will change day to day, not columns) and the goal is to have a dynamic macro that sums the rows highlighted in yellow and red. Is this possible?,
View 4 Replies View RelatedCode:
Sub appointment_nieuw()
With CreateObject("Outlook.Application").CreateItem(1)
.Subject = Range("C3")
.Start = DateValue(Range("A3")) + TimeValue(Range("B3"))
.Duration = 0
.Location = Range("D3")
Dim cell As Range
For Each cell In Range("F2:H2")
.body = .body & Space(2) & cell
Next
.Save
End With
End Sub
I have this macro that works great, but every time I have a different appointment. I have to go into VB editor and change the ranges for the subject, datevalue, timevalue, location and body.
Can I get a text box (or something better) to pop up and I enter the ranges for the 5 in it and have it change in the macro?
1. An amount of money is invested on the first day of any given month of the year;
2. This investment earns interest (calculated at a nominal , not effective, rate) which is credited each month in arrears;
3. Once the interest is credited, it is withdrawn and the effective original investment amount is carried forward to earn interest again (which is yet again withdrawn) until the investment matures in, say, 24 months, when the capital is paid out;
4. On each withdrawal, a certain portion is (with this specific investment, in case you wondered!) susceptible to certain Taxes, which I want to calculate for each Tax year (or portion thereof)
In South Africa, the standard Income Tax year runs from 1 March through 28(9) February of the next year.
I am trying to figure out a formula that will calculate the Taxable Portion of the withdrawal for each Tax year (actually, it may even be done for each month and simply totalled in February for the previous Tax year / portion thereof) until the investment matures.
If an investment is made on 1 June, I need to sum the payments from 06 to end 02 (Tax year1), then sum the totals for months 03 to 02 of the next Tax year and then sum the totals for months 03 to 05 (i.e. 31/05 - ending the 24 month investment period) for the third Tax year.
What can I do do allow for this ever changing differing in the inception month of an investment?
I've attached a s/ sheet where I've used all sorts of statements to mark the inception month (year1) as "Mark1", month 2 as "Mark2", month 3 as "Mark3" and the last month as "Mark4", I've used "running months" (1-24 in the example above) etc, but cannot figure out how to use any function I know to help!
Vlookup, IF, Sum, AND etc -
I have a workbook with several sheets and named ranges like ES_Date, ES_Range, ES_Volume or MC_Date, MC_Range, MC_Volume. The 1st two letters are the name of the sheet where the named ranges are located (one is dates, another volume, etc.).
What I'm trying to do is calculate averages, extract maximum and minimum values from the the named ranges using a start and end date that are entered in cells "H6" and "I6". The formula I am using right now is:
{=AVERAGE(IF((ES_Date>=H6)*(ES_Date
I have a database in excel with a lot of tables in which lets a uses a combo box so the user select can view just one table.
I want to be able to create a macro so when a button is pressed, it copies the data into a new workbook and creates a chart so they can do whatever they want without disrupting anything in the original workbook.
I have been able to find code that will export the data and create a chart (see below) but the problem is the range changes from table to table so I need to find a way of changing how the chart picks up the data range.
Sub Chart_New_Book()
Sheets("Temp").Select
Cells.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("A1").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C10")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub
So at the moment when charting ony cells A1:C10 will be plotted no matter what the size of the table is and making the target range bigger only sees the blank cells being plotted.
I need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.
View 5 Replies View RelatedChanging Ranges, Vlookup(), If() And Sum()
This is the link to the thread where ByTheCringe2 showed me the power of SUMPRODUCT and sorted my problem out. I don't think you'll need it, but I'd rather reference it before Roy reprimands me! lol
I am building on the same topic, though.
Hi All!
I have an investment where the monthly interest is added back to the capital. The next month earns interest on the higher figure and this higher interest is again added onto the capital and the story starts all over at the next month. OK, that's easy to do (Again, I think!).
All works fine UNTIL the end of the Tax year is reached and Tax on the interest earned is payable from the investment as the capital carried forward is reduced by the Tax payable. In the attached file I show how I calculate the interest earned for each following Tax year (which always ends at month 2) using SUMPRODUCT. Sumproduct is used because I never know whether the initial investment is made in month 5, 11, or any other month and by using certain controls (thanks ByTheCringe2!) I can simply total the interest earned in each tax year - the months between month 3 (start) and 2 (end).
On a seperate sheet I calculate the Income Tax payable as follows:
Firstly, I calculate the standard Tax payable on the investor's income from other sources. I then add the taxable portion of the interest to the standard income from other sources and calculate the tax payable on that combined figure. I then deduct the two figures from each other and get the increase in Tax payable because of the interest earned.
This "increase in Tax payable"-figure is then returned to the spreadsheet on which the interest is added onto the capital. See Sheet2! cells O9:O15. The Tax payable for each Tax year is placed next to taxable values I calculate using SUMPRODUCT (again!) in column N9:N15.
VLOOKUP is then used to transfer this Tax payable figure into the calculations (Column K9:K68) as and when required (only when tax is payable) - and should be deducted from the value in (Same row) Column H and that answer should be transferred to next row Column E (Balance at beginning of month) -
BUT it causes CIRCULAR REFERENCE faults and I am absolutely frustrated at not being able to figure out how to sort it out!
Sheet2, Column E9:E69 (See Sheet2!E19 for Comment explanation) in the attached workbook.
In the following sheet I'm tracking daily numbers against a monthly total. In the cell E4 for example I have a minimum per day number needed which is based on the monthly goal divided by the number of days availble to work. What I would like to be able to do is have the number auto-adjust if a letter (i.e. V=Vacation, S=Sick, etc...) is used in place of a number on any given day.
I have been using the following to change the year in the Header
Code:
Sub Change_Format()
' Ctrl-y will change the year per individual sheet
ActiveSheet.PageSetup.RightHeader = Format(Now(), "YYYY")
End Sub
Is there a way to have this done automatically? There are absout 6 sheets in the workbook that have the year in the header and right now the code is in Module 3 and I have to press ctrl+y for every sheet.
How do I stop my sum formula from adding to it's range every time I type a number in? I have 12 columns of numbers by date and 4 quarter columns at the end totaling each of the 4 quarters. If I start typing directly across, the 1st quarter sum formula updates to include every number in the first 12 columns. The other quarter column sum totals stay the same.
View 7 Replies View RelatedI have 2 Excel Files. In the main file I have a macro that copy/pastes data from the secondary file. The secondary file is a daily file that gets its file name changed every day (eg. 14OCT09.xls).
The last piece of the Macro goes back to the secondary file and ends with:
Windows("14OCT09.xls").activate
Range ("A1").Select
Now tomorrow, I have to manually edit the Macro and change the code to 15OCT09.xls before I run the Macro. Is there a way to automatically change this date without having to manually edit it on a daily basis?
I have a sheet containing employee data - 13,000 rows - one for each employee.
Column A contains their store number (there are about 1300 stores in total) and column H contains their name.
Is it possible to create a macro that, for each change in store number, will create a named range for the employees' names in that store automatically?
I have a workbook, with 2 sheets. first page has graphs, second has data. in my charts I refer to a named range on the second one as follows: Data!Named_Range, where "Data" is the name of the second sheet. However when i save this, it changes the range of the chart to: '101044.xlsm'!Named_Range, where 101044 is the name of the workbook. This is a problem for me as this workbook will often be loaded into a document handling system where the file will change name, throwing out all sorts of errors because the chart path is invalid, and i have to change the range of all the charts manually. why does it have to use the file name when reffering inside the same workbook? and how do i force this to not change?
View 5 Replies View RelatedI am writing/creating macros and I want to create one that will automatically run when a cell value is changed to a certain entry.
View 2 Replies View Related