I have a challenge trying to get excel to recognize yearly intervals.
Let me explain further:
Interval Percentage Yr1 Yr2 Yr3 Yr4 Yr5 Yr6 Yr7 Yr8 Yr9 Yr10 Yr11 Yr12
3yrs 10%
I want excel to insert the 10% into the cells every 3yrs.
I have attached a dummy workbook detailing my issue
Interval problem 1.xlsb
I want to create 15 minute time intervals from the data. I attached the file for reference. I would like to sum the data column that corresponds to each interval. I created an example using the formulas and I want to be able to do that automatically either using VBA or the formulas.
View 5 Replies View RelatedI would like to automatically update a 'yearly' database file with info from a file that is changed on a daily basis.
The daily file that i use has info like date, truck number, delivery stops, weight.
the database file has the similar headings.
at the end of each day this daily file is saved. I would like to have the info that is entered into the daily file automatically plugged into the yearly database file into the next available group of cells with respect to the salesperson.
This is kind of a generalization but i'm hoping to just get pointed in the right direction. If something like this involves vba then it will be beyond my ability and i'll have to do it manually, which is fine
I have a column of the date of sales. (several hundred lines long) and each line has the name of the item sold. I need to complete a report showing how many sales of each item per date. Date of sale in one column and the name of the product in another.
View 3 Replies View Related I am building a country ranking model which ranks several different macroeconomic indicators and applies a weighting to each of the indicators. I have an overall ranking column which is an average of all the ranks. The problem is whenever I change any of the individual indicator's weighting it obviously affects the country's overall ranking. Is there a way that the model can automatically "re-sort" the overall rankings without me having to manually click on the sort by smallest to largest filter each time?
PS I'm using Excel 2007.
I have written a 2007 workbook which contains 4 simple macros. One of the macros automates the process of saving the print range as a .pdf file. It works fine on my pc but when I send it to others to use, when they try the macros, they all return a 1004 runtime error.
View 6 Replies View RelatedWhat I have done is entered code to auto generate the date in column O whenever data is entered or altered in column A. Here is that code:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then _
Target.Offset(0, 14).Value = Now
End If
End Sub
What I am trying to do now, is create a column that will take the information from O and do a sort of COUNTIF function that will count how many items of data were entered on a certain date by the day. For a clearer example, I want it to tell me how many items were entered/altered on 7/23. But I also want it to continuously calculate it for each date after that. Preferably automatically, but if a macro is needed I can create an update button.
Once it can achieve that I would like to create a dynamic graph that will automatically (or via macro button) update to show the last 5 days. It should display the date and how many items were entered that day.
I am using Office 2007.
I'd like to use two columns / labels to create a colorful bar chart that just piles each field value on top of each other and shows the amount (and percent of total if I can) along the first column's values next to the bar chart something like below..
So column 1 is the "entity name" and column 2 is the "value/amount".
I can't make this chart look right at all. I have Excel 2007.
I've set up an email to be sent via a macro (excel 2007 and outlook) and I expanded the body of the message and it now comes up with an error "Compile error: invalid outside procedure"
It was working when I only had 4 lines and now it's not working.
strbody = "Hi," & vbNewLine &_
"abc." & vbNewLine &_
"def." & vbNewLine & vbNewLine & _
"ghi" & InputBox("Enter Response date (dd/mm/yyyy)") & vbNewLine & vbNewLine & _
"jlk." & vbNewLine & vbNewLine & _
"lmn" & vbNewLine & vbNewLine & _
"ABC" & vbNewLine & vbNewLine & _
"XYZ" & vbNewLine & _
[code]....
I'm trying to create a very simple order form. It's been a while since I've done this in Excel, and I couldn't find an answer when I searched. (I may be a lousy searcher, though.)
I want the user to enter a quantity in Column E of the "Common Items" worksheet, and have all the rows with quantities copied to the "Order" worksheet. (On the "Order" copy below, I just did a copy/paste to show the desired effect.)
Excel 2007
A
B
C
D
E
F
1
Item Number
Description
Unit
Price
Qty
Total
2
BX-2B1324X
0.9% NaCL 1000ml Bags
CS
$23.52
5
$117.60
[Code] ....
I'm pretty sure this can be accomplished with an If/Then, but I'm lost! Optimally, they'd enter their quantities, click on the Order sheet and hit print.
I am trying to get the formula for calculating yearly rental imcome. The range is 10 years and the interest is 34%. The first year payment is 42,000 and the 10th year payment is 56,000. I can't figure out how to do the other years. The principal is 325,000 and the sale value is 425,000.
View 4 Replies View RelatedI have to write the word "hyllvagn" in column A in every fifth row starting with $A$2.
The range should be $A$2:$A$100.
I work for a small company and we are attempting to change the way that employee's vacations are calculated. My Excel knowledge is just enough to get me by. I am building a simple spreadsheet for each employee. We want to make their vacation time roll over each year on their anniversary date. In prior years, we would just use January 1 after the employee had been there a year or two. My question is can Excel take the employees start date and add the additional hours that are earned on the anniversary date automatically.
I have a set of 4 values that I would like to show in a graph. The values I need graphed are as follows:
Brought Forward = 3
New Requests = 2
Closed Requests = 4
Remaining = 1
These 4 numbers will be part of a monthly report (so I only need the numbers graphed once) and also a yearly report (so I would need to find a way to graph all 12 months).
I'm using the code below to extract data from a 'Source' sheet to populate a "Yearly Extract Summary" 'destination sheet. With the unique distinct values copied from column I on the 'Source' ("All Data") sheet to column B on the 'Destination' ("Desired Output") sheet. In addition the values from column J on the 'Source' sheet are summed and paste under the relevant month on the 'Destination' sheet.
[Code] .......
The code works fine and the correct figures populate the correct columns and rows on the 'Destination' sheet.
As you can see from the code above, the monthly values have to be hard coded to match the column headings and this is fine when using a static 12 month period. But I'm now wanting to use a rolling 12 month period, which, at the moment, necessitates the need for me to change the code each month so I'd like to change the code but unsure where to even begin, how to produce the initial script.
I'd still like maintain the existing functionality in this section of code:
[Code] ...........
I have attached a file which contain 3 sheets.
The "All Data" 'Source' sheet,
The "Output" sheet, used for testing, and
The "Desired Output" sheet which shows the results using the current code
To run the code, please use the button at the top of the "All Data" sheet.
Sum Categories Test2.xls
I wish to have a simple planner displaying the academic year with only mondays' dates.
ROW 3 displays the dates
Column B is Week 1
Cell B3 is the first date (10.08.09)
Cell C3 should read 17.08.09
Cell D3 should read 24.08.09
ETC
I have managed to do it using autofill before but I can't get it to do it now. Is there a preferred way to achieve this?
I have a column that contains dates for an event. I would like to tally quarterly and yearly totals for these dates. What formula can I use to accomplish this?
View 1 Replies View Relatedhow to add drop list box or combo box in this yearly time sheet so every employee has his own record in this time sheet so when ever i select name from drop list all info changed, i did include table in sheet 1 as an example.
View 4 Replies View RelatedI have a problem here in calculating the Daily sales target based on Monthly Targets and Year End Target.
I am attaching the file herewith which has Yearly & Monthly Targets defined. Need calculating Daily targets which should match with Monthly & Year end target .
I have the split of day wise sales for a week as well in another tab. However not able to get the exact monthly target as listed .
i have a problem that i have been trying to get over for about a week now.
i need to calculate a lease commission, with an extensive amount of variables.
first i need to find the length of the total term which should be anywhere between 1 to 10 years.
then on a annual basis i need to define how many months are billable in that year.
which gives me to variables to account for there, which are
A= initial free months, non paying
B = the last month of last year may only be a half year
i think i have worked that out pretty successfully, so next i need to calculate the rent for each year period. with several variables
a= the rent can be caculated :
-by per month basis
- by annual basis
- by a per square foot basis
b= next in relation to annual rent operating expenses may also be calculated in the annual rent number also by the same variables, however it may or not be calcuated into the number depending on the lease.
c. this is where i am at now, and its killing me. i need to account for rent adjustments for each year.
rent adjustments can start from either the lease start date or the date that rent starts which would be after the lease start if free rent is granted.
then the adjustments will continue through the end of the term and be implimented every x number of months.
the value of the adjustments will either be a percentage of the first years rent usually 3-5 %
or per sf, per month, or just flat rate per year. but it will escalate each year.
for example year 5 is x amount of ajustment from year 4.
i am finding difficulty in finding an annual value of the original lease term in relation to this date series. expecially if the adjustment periods leave a remainder carring over to the next year, or if their are several adjustments in one given year.
any help would be appriciated on this.... i know its pretty complicated, and i have rewritten this code about 30 different ways , i am at a loss right now.
if you think you may want to see my file let me know and i can post it
I have created a excel sheet here i want the total interest charged for three months in 3rd mnth interest charged column, if i select 7 mnths term total interest charged for 7 months should come in 7th month interest charged colum, if it is 13 months total interest for 12 months in 12th month interest column and remaining 1 month interest in 13th month interest charged column
INTEREST CAL.xlsx
With the data table given below, how can I formulate the yearly installment based on the tenure. Like below example.
Name
Loan
Yrly Pymnt
No. of Tenures
Y2014
Y2015
[Code] ........
I need to write a code to create visio flow diagram with excel inputs(Excel 2007). Attached is the requirement.
Requirement_Specification.docx
within excel for a big spreadsheet they developed?
can you suggest a way of doing this or an article to read?
could users press F1 and let my own custom help guide appear?
Just striving to have this kind of "picture arrangement's macro" to work, macro that whenever you choose a certain cell and operate it on hotkey it'll:
1. Create a new folder named "PicturesAuto" beside the excel file (wherever the excel file located).
2. Inside folder "picturesAuto it'll create a new subfolder named by the same name of the sheet related to that cell you operated the macro upon. (let's say this cell placed on sheet named: "happysheet"- that'll be the name of the subfolder created.)
3. Inside the subfolder "happysheet" it'll create a new subfolder named by the text of the cell you activated the macro upon (let's say you initially operated the macro on B5 cell -which has the text "montana" so it'll create "montana" subfolder also inside subfolder "happysheet")
4. Lastly instead of having only the text "montana" in "B5 cell" like we had initially before activating the macro, the macro'll also add to that text "montana" the link to "montana" folder (path of excel file->picturesAuto->happysheet->montana)
The way I'm planning to use this is to add a lot of HR pictures inside those folders created by the macro that related to that specific sheet, and that specific cell I've activated the macro upon. Plus just having those pictures so organized is just outstanding in every scale, and the excel file will keep running smoothly without any resource consuming overload.
Adding a search engine like feature to a spreadsheet we have which has multifarious columns of data on it, here is a screenshot of what it looks like:
[URL]
We have multiple sheets for each tutor like that picture which lists all information of their learners for reference, now I want to do a search feature on the main home page or perhaps on a dedicated "Search" page which allows us to filter out specific information out of those columns, mainly the ability to search by "Tutor" (aka sheet name), "Postcode", "Venue", "End Date", "Learner Name" and "EBS" number...
I have a table
item_desc
a
b
c
a
b
c
a
b
c
As you can see, a,b and c is repeated times.
How do you make it so that it would look like
item_desc
a
b
c
item_desc2
a
b
c
item_desc3
a
b
c
Is there a shortcut in doing this instead of copy pasting it?
I have a record just like this with about 572+ rows and 13 repeating items with that 13 items having their own column so copy pasting is really a tedious task. Kindly please help me solve this?
I have created a excel document. Would like to create report using the data.
create reports using excel data
The following code is used to export values form Excel into a word document:
The report obtained then looks like this:
Excelforum.jpg
How can I make the values fit within the margins of the word document?
I've got quite of few excel charts to make and have been looking for a way to automate the process. The chart types include line, pie, and bar. Basically, I have data for each state in the US that I am comparing with national level data. Thus each chart will have national level data and the data for each state. What I need is a macro that I can easily change the parameters for because different worksheets have slightly different layouts. I started by using the macro recorder to make a chart. Then I attempted to edit it so that it would run loops to make additional charts. Below is some sample data for the first worksheet I have and my attempted macro.
Sample Data:
Code:
State Characteristic Value07 Value08 Value09
Alaska A 11 23 45
Alaska B 13 22 98
Alaska C 99 91 21
Alabama A 23 14 11
Alabama B 44 62 76
Alabama C 75 47 21[code]....
This code doesn't run at all.