Changing Ranges, Vlookup(), If() And Sum()
May 2, 2007
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 -
View 9 Replies
ADVERTISEMENT
May 28, 2007
Changing 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.
View 9 Replies
View Related
Jul 21, 2014
Is 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 Related
Jun 24, 2013
Code:
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?
View 6 Replies
View Related
Oct 5, 2009
I have two defined ranges, and they keep changing to include the workbook name. XV2 =
View 3 Replies
View Related
Oct 14, 2008
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
View 4 Replies
View Related
Jan 25, 2012
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
View 2 Replies
View Related
Apr 25, 2008
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.
View 3 Replies
View Related
Nov 30, 2008
I have the below formula....
View 6 Replies
View Related
Apr 29, 2014
I want to merge two tables with keeping the data in the two tables in two worksheets, so my idea is to create a drop list in one of the two worksheets and depending on what the user will chose the datas will appear. for informations, the datas are all Vlookup in the same workzone but for each table the lookup will be done in differents columns, so how to make the column for the vlookup changement and depend on what I choose??
clarifications: it's like the choice on the droplist will be a link cell to the column of the research.
View 3 Replies
View Related
Oct 28, 2008
I want to create dependent lists using data validation. The lists need to be created from ranges that will be growing as users add more data. I think the best way to create the list is a VLOOKUP. However, I am not sure how to use the VLOOKUP when the range is changing.
I have attached an example. I have a list on sheet "Vlookup" called "FRUIT" with "apple", "orange", "banana". Then to make the depedent lists I have created three other lists called: "APPLE", "ORANGE", "BANANA". I want to pull the COST from sheet "VALUES" into the lists "APPLE", "ORANGE", "BANANA". Users will be adding costs next to the FRUIT they purchase.
View 5 Replies
View Related
Jan 14, 2009
I am having problems getting the code below to follow my intentions. I have 2 files. I have a temporary file that holds all the data that I need. I have a destination file that needs to have data copied into it. In the temp file, I have data for several dates and product types. What I intend to do is to do a vlookup in VBA to look for the date and the product type in the temp file and copy the appropriate data to the destination file. I have numerous named ranges both in my temp file and my destination file. For the code below, I wanted to make a loop to find the date in the temp file that is listed in the destination file. Once this is done, I wanted to find the product type in the temp file that is listed in the destination file. If both conditions exist, then copy a certain range from the temp file to the destination file. My intention is evident in the code, but I don’t think that I am putting in the correct “code format”.
View 9 Replies
View Related
Feb 19, 2010
=VLOOKUP('ITEM ADD-CHG Form'!Z19,Brand_V,2,FALSE) Another wrench!. If the value in cell Z19 is not found in the named range "Brand_V", the formula returns #N/A. I need to replace #N/A with the verbiage DEF while leaving the other results intact.
View 2 Replies
View Related
Feb 24, 2012
I am trying to create a vlookup to get a count of trouble tickets techs completed daily within a table I created, I am using a table since its dynamic. For example I need to see how many tickets Joe completed in a day. See below...
Tech Ticket# Comments Status
Joe 1234 Replaced HD Closed
John 3212 Replace Motherboard Closed
Joe 5678 Installed OS Closed
Above is just an example (my table has 40 columns). I need to vlookup joe and get the count of the closed tickets.
View 6 Replies
View Related
May 8, 2008
I have an Excel File with 2 worksheets.
Sheet1 contains column A as "Product Number", Column B as "Product Family". Rows contain about 20,000 lines of data. A Range has been created to cover both columns, and named "ProdRange"
Sheet2 contains Column A (blank), Column B with "Product Number"
What I am trying to do is start at the top of Sheet 2 "Product Number" (B2 is first cell ref), and use this value to do a Vlookup on Sheet 1, to extract the "Product Family" code on the Offset 1 - and insert it back into Sheet 2 Column A.
I have this below :
For i = 2 To LastRow
Range("A" & i).Value = "=VLookup("B" & i, ProdRange, 2)"
Next i
BUT for some reason it doesn't like the [ "B" & i ] part of the equation.
View 9 Replies
View Related
Nov 28, 2008
Is there anyway to, instead of indicating specific cells to be included in the range of a VLOOKUP table, designate the table range as whatever cells are selected in a spreadsheet.
The VLOOKUP formula is in a spreadsheet called "Template". The VLOOKUP table range will be in a spreadsheet called "Statement of Assets".
Here is the formula I have:
=VLOOKUP("Lookup Value",'Statement of Assets'!$A$122:$C$142,2,0)
But the row numbers in "$A$122:$C$142" is going to change depending on what plan number I am working on. Columns stay the same.
What I want is something like:
=VLOOKUP("Lookup Value",'Statement of Assets'!whichever cells are selected,2,0)
View 9 Replies
View Related
Nov 9, 2006
I am trying to use vlookup in vba but i receive an error '1004' "Unable to get the VLookup property of the Worksheetfunction Class". I need a way around this. I have ID numbers in the first column and I am trying to input the name of the table it corresponds to in column "O". I have 4 different tables. I am using For Each cell in Selection to go through every cell by using If statement along with VLookup. As soon as the function is forced to go to the next IF selection, it gives me the error.
Range("O3:O" & myCount + 2).Select
For Each cell In Selection
If IsEmpty(cell) Then
If cell.Offset(0, -14) = Application.WorksheetFunction.VLookup(cell.Offset(0, -14), _
Workbooks("Warehouse Inventory"). Sheets("TWGReports").Range("$a$2:$z$1000"), 1, False) Then
cell.Value = "TWG Facility"
If cell.Offset(0, -14) = Application.WorksheetFunction.VLookup(cell.Offset(0, -14), _ .....................
View 3 Replies
View Related
Feb 9, 2007
When I type in a ticker into TextBox2 the form is prepopulated. Sometimes the info that is prepopulated is incorrect and the user should just be able to click in the textboxes and make the changes. The problem is that when the user does click on the boxes and makes the changes and hits submit the combobox values do not change and stay that of the vLookup results. So, the vLookup results can't change even if the user makes the change and hits submit. What am I missing. Do I have to declare somehwere that ComboBox1.value (for instance) is actually the value as of the submit button ebing clicked and not when the vlookup happens?
Private Sub TextBox2_Change()
On Error Resume Next
TextBox1.Value = WorksheetFunction.VLookup(TextBox2.Value, Range("Data_Company_Names"), 2, False)
ComboBox1.Value = WorksheetFunction.VLookup(TextBox2.Value, Range("Data_Company_Names"), 4, False)
ComboBox8.Value = WorksheetFunction.VLookup(TextBox2.Value, Range("Data_Company_Names"), 3, False)
ComboBox11.Value = WorksheetFunction.VLookup(TextBox2.Value, Range("Data_Company_Names"), 5, False)
On Error Goto 0
End Sub
View 9 Replies
View Related
Jun 25, 2014
I am looking for the formulae in E9.
I may change the value in E8 as 1.5 or 2.5 or 4 or 6 I may scrolldown C9 as it set out there. I need to know the farmulae in E9.
View 6 Replies
View Related
Feb 4, 2014
Can I use a vlookup combined with IF function to lookup up to a different file dependent on what the cell says.
For Example cell a1 could have the value Alan Smith, Tony Brown or John Doe dependent on what value is entered the lookup would "lookup" against the file named Alan Smith, Tony Brown or John Doe.
View 6 Replies
View Related
Sep 25, 2009
I've 2 worksheets, Summary and Sales. I need to get the total sales figure from the "Sales" worksheet onto the Summary worksheet. Sales data are pasted onto the worksheet monthly. Tried using vlookup but problem is, the column containing the total sales figure may vary and I need to update the vlookup formula. Another problem is that sometimes the Total sales (the lookup value) is not in the first column. Tried using the Index and Match combo but cannot get it to work. I've attached a simple mock up to hopefully illustrate my case better. I believe the experts in this forum should find this rather easy!
View 14 Replies
View Related
Dec 19, 2012
I have about 85 files that I need to rename. These files contain data concerning delivery and quality data of each of our suppliers...1 file per supplier.
Current File Name: SupplierA 2012.xlsx
New File Name: Supplier A 2013.xlsx
Is there a way to do mass rename the files..
and that leads to my second question...each of these files contain vlookups to 2012 data...which is contained in the a file with 2012 in the file name. I need to replace source data file (i.e. 2012 Index) with the new file containing the 2013 data (i.e 2013 Index). Is there a way to replace that source data across 85 or so files using some time of mass find and repalce? All the cell references remain the same
View 2 Replies
View Related
Feb 17, 2014
I have a user which needs to run a vlookup daily. There is some formatting involved, especially with 2 exported workbooks...
So I wanted to make her a macro to do it. So she exports 2 workbooks where the names change based off the date range she runs the data for.
I was thinking I wanted her to close excel completely, then run them in a specific order and do the vlookups based of Index Numbers.
I can do the entire macro except for the syntax of the vlookup. I think if i write what I need, you guys could put it in proper syntax:
=Vlookup(RC[-10],Workbooks(2).Sheets(1).Range(A:A),1,false)
View 2 Replies
View Related
Jun 20, 2007
I am pasting vlookup formulas into a spreadsheet using a macro, and want to change the reference column number based on a variable generated within the code eg
= vlookup(RC1,table,i,false) where i is a predetermined variable in the code
for i=23, I need the result to be of the form
=vlookup(a1,table,23,false)
View 7 Replies
View Related
Jan 20, 2010
I've information in two sections of a worksheet, columns A and B (range named "temp1") and columns D and E (range named "temp2"). I should also say that the reason I have two sections is that each section will have about 150 rows, so instead of having a huge list of 300 rows, I've tried splitting them into two sections.
AB
1a
1b
3c
DE
4d
1e
6f
What I want to do it show all the results that match the value 1 using a vlookup formula. Also the values in column A and D may change, i.e if value 4 in D1 changed to 1, I would expect to see "d" included in the results.
This is the original formula I got somewhere else which sorts out my vlookup on multiple matches issue.
View 4 Replies
View Related
Feb 23, 2012
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(ActiveCell, Sheet8.Range("C16:Y1000")) Is Nothing Then Exit Sub
Dim rw As Integer
Dim arw As Integer
rw = 16
[Code] .....
As you can see, my code is located in the sheet8 worksheet object. Now, I have a few questions about this. Because I am located in the sheet8 worksheet object does that mean my code can only work in sheet8, i.e., the following won't work because I am in a Sheet8 worksheet object?
Sheet10.Range("B12) = ..... ....... .....
This is not returning a value in Sheet10? My question is how do I make my code return a value in Sheet10?
View 6 Replies
View Related
Sep 23, 2013
I have a spreadsheet that records the date, specific sporting activities and cash award for participation in a round robin sporting event that was held over 52 weekends. I'm trying to lookup the cash award value of each event the person participated in by using a date range within the vlookup formula.
Each participant has their own spreadsheet which they are able to access via server, and each spreadsheet looks a bit like this:
-Column A contains the date of an event in which the person participated
-Column B contains a category number related to the type of event eg marathon = 12, hurdles = 15, relay = 18 etc. This info is manual input
-Column C contains the amount the person will be awarded by simply participating in the event. This is a lookup value and varies depending on the date the person participated, so I'm trying to incorporate date ranges and 'if' formulas into the vlookup
I'm from Australia so my dates might be backwards...
Spreadsheet:
A B C
1 02/01/13 15 (supposed to be $10)
The formula should read a bit like this:
if (date at A1 is within date range 1st Jan 2013 and 31st March 2013, lookup B1 in table 1 column 2), if (date at A1 is within 1st Apr 2013 and 30th Jun 2013, lookup B1 in table 2 column 2)...etc for each quarter
I don't know if the following is right
=IF(AND(A1>='01/01/2013'+0,A1='04/01/2013'+0,A1
View 5 Replies
View Related
Apr 3, 2008
I am trying to merge data from two worksheets onto a 3rd for a mail merge. The COLLECTIONS sheet contains the acct #, name and amount owed. The ADDRESSES sheet contains the acct #, name and all of the address information. The 3rd sheet is the MERGE sheet that I'm using as the reference point for my word document and the mail merge. Upon reflection the MERGE sheet is probably not necessary, but made sense to have it when I started out.
Never the less...
On the ADDRESSES sheet I am trying to name a dynamic range "AddressList" (I'm trying to go dynamic because the list of addresses will change from month to month). I am using the following formula in the refers to field when I name the range:
=Offset(Addresses!$A$5,0,0,CountA(Addresses!$A:$A),CountA(Addresses!$1:$1))
I think this is what is giving me my problem, because the named range does not show up in the list when I try to go to the named range.
Just in case that is not the problem, the #Ref is showing up when on the MERGE sheet I type a vlookup formula referencing the acct # on the ADDRESSES sheet. That formula looks like this:
=VLOOKUP(A4,AddressList,3,0)
which seems simple enough. Before trying to make the named range dynamic the formula worked fine, which is why I'm thinking the named range is what's giving me the problems.
View 9 Replies
View Related
Apr 9, 2009
As I copy and past my lookup formula down the page it is changing the lookup range which I think is what is giving me so many #N/A results. My first Formula is
=IFERROR(VLOOKUP(A2,Coors2!A2:D3765,3,FALSE),IFERROR(VLOOKUP(A2,'AB2'!A2:C13944,3,FALSE),(VLOOKUP(A2 ,'WM2'!A2:C4843,3,FALSE))))
Then for instance at line 59 the formula is
=IFERROR(VLOOKUP(A59,Coors2!A59:D3822,3,FALSE),IFERROR(VLOOKUP(A59,'AB2'!A59:C14001,3,FALSE),(VLOOKU P(A59,'WM2'!A59:C4900,3,FALSE))))
View 2 Replies
View Related
Aug 23, 2012
I'm working on an excel report that handles reporting for a large number of people. Another person at work is doing the VBZ coding, and I'm doing the formulas that aggregates the data. which has led to an interesting problem whenever I use a vlookup. I copied the sheets with the formulas from the test document I had been working on to an updated version. And all the vlookups automatically adjusted themselves to reference the document they had been created in. I had also moved over the tabs they were supposed to reference, but they keep looking at the old document. I spent an hour removing the references last night, but I don't want to do it again when I get the final product.
How to get excel to copy a formula EXACTLY as written, and not att in the name of the original document?
It should say
=VLOOKUP($A438,totals!$A$1:$AH$63,32,FALSE)
and instead it says
=VLOOKUP($A438,'[reporting formulas.xlsx]totals'!$A$1:$AH$63,32,FALSE)
View 2 Replies
View Related
