How To Change Values By Increment In Data Table
Aug 17, 2009
I have built a model which aims to calculate various information/statistics based upon 2 inputs which I can change (see 'Mov_Avg_Chart' tab cells C6 and C8). In cell C6 I have identified the moving average period that I would like, and in cell C8 the period for moving average of the gradient/standard deviation of the gradient.
I have created 4 data tables (one for annualized return, sharpe ratio,max drawdown, and annualized risk/reward). The first data table can be seen in cells AE15:AH18. In cells AE16:AE18, there are the moving average period inputs (as in cell C6) and in cells AF 16,AG16 and AH 16 are the moving average gradient inputs (as in cell C8). What I am trying to do is create a table that will allow me to state a minimum moving average period (as in C6) and a maximum moving average period (as in C6), and an increment which I would like to look at the data in. Thus, in this example, the minimum moving average period would be 20, the maximum would be 40 and the increment would be 10. Also, I want to create a minimum moving average gradient (as in C8) and a maximum moving average gradient (as in C8) and an increment figure. Thus, in this example, it would be minimum of 75, maximum of 125 and an increment of 25. The point in the example is to see which outcomes based on the inputs are the most favorable.
I am having difficulty with setting up a minimum and maximum range and the incremental period. The trouble is, I don't know how to put these as additional inputs and have them drop into the data table (in place of cells AE16 to AE18 for example). Would anyone have any ideas? Also, the automated recalculation of the data table is needlessly slowing down the spreadsheet; is there a way that the data table can be recalculated only when there are changes to the input functions? I have set the calculation method to 'automatic except data tables' because otherwise it takes 15 minutes for the file to open. Thus, the data table isn't calculating automatically. That is, cells C6, C8 and the minimum and maximum range cells that I am seeking to create? Would someone be able to help me with this?
View 14 Replies
ADVERTISEMENT
Mar 13, 2013
Table 1
January-12
February-12
March-12
Table 2
Sr. No
Name
Dep
Lates
CL / SL
AL
Lates
CL / SL
AL
Lates
CL / SL
AL
[Code].....
View 1 Replies
View Related
Jul 27, 2007
I am trying to do a vlookup where the values in the Table_Array have a string appended to them. For example:
I want to look up the string "Example1" and the lookup range is
Example1 - monthly
Example2 - weekly
Example3 - weekly
Example4 - monthly
So essentially I want vlookup to only look at the string before the hyphen. My initial thought was to do something like this: vlookup("Example1", left(A:A, 8), 1, FALSE)
View 6 Replies
View Related
May 1, 2014
I have a userform that has 28 checkbox's that we go through that we check which ones apply to a report.. To keep those boxes checked as the supervisor's cycle through the reports, I am leaving a 28 digit code next to the report which is created as they check/uncheck the proper checkbox's.
[Code].....
So if say CheckBox10.value = True, then the 10th digit of the ID will change to 1 while keeping the rest of the ID the same.
View 6 Replies
View Related
Jul 15, 2014
I have a large table that I want to reconstruct. For simplicity sake, let's just says it's 3 rows (excluding headers) by 3 columns.
Item Description
1/1/2014
1/2/2014
1/3/2014
Cheese Burgers
2
3
4
Hot Dogs
5
12
6
Beverages
2
5
3
I want to reconstruct it so that the column headers become values in the table. The table headers are dates, in this case, if that gives clearer picture. So the new table would have 9 rows, (3 rows of data, excluding the header times four columns).
Item Description
Date
Quantity
Cheese Burgers
1/1/2014
2
Hot Dogs
1/1/2014
5
[Code] ....
The above example is sorted by date but I would be indifferent if it's sorted by the Item Description.
Is there an easy way to do this? Pivot possibly? Again, my data table is large: 36 rows x 181 columns. Using the copy/paste/transpose feature is pretty impractical.
View 3 Replies
View Related
Jul 30, 2013
I have got the following problem. I have got a table which looks like this:
=Time!A1
=Open!A1
=max!A1
=min!A1
=Close!A1
When I mark the selected cells and scroll down using the autofill option I get this:
=Time!A1
=Open!A1
=max!A1
=min!A1
=Close!A1
=Time!A6
=Open!A6
=max!A6
=min!A6
=Close!A6
Why does it give A6? I want A2 instead of A6 because I want to create a table from A1 to A500 but without these jumps from A1 to A6.
View 5 Replies
View Related
Jan 30, 2012
I have created Pivot Table in "Sheet2" and the source data is in "Report" tab. The source data is in the range of A1:K200 -means until the last cell of the excel. Now if the data is more then this we need to change the source data every time before we refresh the pivot table - Is there any macro or any way so the range can be change automatically ?
View 9 Replies
View Related
Feb 13, 2008
Right, I have tried everything I can do (which is not so much) to achieve the following. Can someone please help?
I have a worksheet with about 300 rows of data. The columns are headed Name | Division | Department and then 8 different scores and a total score). I have created 7 different worksheets with pivot tables on them to display each division.
Now, here is what I would like to do. Each Pivot table should show the Name and 1! of the scores next to it. That is simple enough and works. But how do I create a macro that allows me to change which score to display based on the content of cell A1. I.e. If I write Effort in A1 I want the pivot table to show Name | Effort score. If I write Complexity I want the pivot table to change to Name | Complexity score.
View 9 Replies
View Related
Jan 7, 2014
i have excel sheet with name labels.i want count how many times user repated along with his branch code. acctully am done this using this formula, but not getting what am exepected.
Formula in B1 is ="JSK-SW-1"&(countif($A$2:$A2,$A2))
output is value is incremented, but i want text also be change.
A B
Name Count
Sateesh JSK-SW-1
Rajesh Raj- SE-1
Sateesh JSK-SW-2
Rajesh Raj-SE-2
Anil JAK-DE-1
View 1 Replies
View Related
Jul 23, 2010
Is there a way to increase the size of the data table dynamically?
View 10 Replies
View Related
Jun 17, 2013
I have responses from a questionnaire in the format below, and need to mail merge to a word doc.
name
subject
teacher
grade
comment
[Code]....
If I'm right I need it in the following format for a mail merge.
name
subject
teacher
grade
comment
[Code]....
I need to get this fixed by 5 pm tonight, otherwise a foresee a night of copy and paste ahead. I thought pivot tables might work, but alas no, it only tells me the number of the comments i have.
View 7 Replies
View Related
Jan 11, 2013
I am building an Excel file that will be used to track information and at the core of it all is a list of people from different offices where the number of people per office can change and/or a person leaves the company and is replaced by another. I'll simply:
Column A Column B
Office Employee
Hamilton Emp 1
Hamilton Emp 2
Hamilton Emp 3
Toronto Emp 4
Toronto Emp 5
Toronto Emp 6
Toronto Emp 7
Toronto Emp 8
Waterloo Emp 9
Waterloo Emp 10
This will be all on Worksheet 'Info'. I have a Worksheet for each Office and named them accordingly. On each worksheet I want to use Data Validation on a column, we will call it 'ChosenOne', set it as 'List' and have the Source pull all the employee names that belong to that office and use them as a selection
ex: Hamilton Worksheet, 'ChosenOne' would show Emp 1, Emp 2, and Emp 3 in the list.
If Emp 3 changed offices to Waterloo 6 months from now I would like to change A4 from Hamilton to Waterloo and the formula would not have to be changed and the next time someone selects 'ChosenOne' it would only show Emp 1 and Emp 2.
Of course this means on the Waterloo Worksheet, 'ChosenOne' would show Emp 3, Emp 9, Emp 10 now.
So basically I am trying to not specify a specific named range for each office and am hoping there is a way to poll information from a Table (or any other tool that can simplify this).
I would be ok with something like:
Column A Column B
Office Employee
Hamilton Emp 1, Emp2, Emp 3
Toronto Emp 4, Emp 5, Emp 6, Emp 7, Emp 8
Waterloo Emp 9, Emp 10
and just move Emp 3 from B2 to B4 but I don't know if a list can be created from multiple items in a single cell seperated by a , or ; or :.
View 4 Replies
View Related
Feb 7, 2007
I am trying to achieve can not be based on a range of rows or cells it must as this data is imported from a forecasting application and the location of the targets may change.
Perhaps an offset to the current selection can be used some how, but have a look-see if you know where Im coming from.
'I have used this to find a target in a data range.
Dim r1 As range
Public Sub FindDataIn Range(r As Range, target As Variant)
Set r1 = r.Find(target)
If r1 Is Nothing then
Msg Box target & " was not found"
Else
r1.Select
End If
End Sub
'Then I inserted and Named this procedure to find the data on various assumptions or targets - SUCH AS THE VALUE "14306".
FindDataInRange ActiveSheet.Range (A1:A226), "14306"
Selection.EntireRow.Select
Selection.Copy
Selection.Insert Shift:=x1Down
Selection.EntireRow.ClearContents
Then I write this again for another Target such as 14307 and it repeats
The result is that it finds the target cell I get an empty row above the two rows that contain that target. (LET ME EXPLAIN WHAT I MEAN BY TWO ROWS)
The thing is there are two rows containing 14306 in that range and the range is sorted ascending so that they are positioned one under the other. Each row has different forecast totals beside this number because one is an export SKU and one is a Domestic.
What I want to do is combine the two rows as one with one row of forecast totals for the number rather than two.
Like this:
14306big Widget Domestic 26 89 (This is combined as a new row)
Instead of this:
14306big Widget Export 12 14 (These 2 rows are then deleted)
14306big Widget Domestic 14 75
Also the Forecast totals run across 12 columns (one for each month) and then there is a column for year totals of each row that needs to remain the same.
Most important this here is this cannot be based on a range it must as this data is imported from a forecasting application and the location of the targets may change.
View 9 Replies
View Related
Aug 28, 2009
I can't understand why my rate1() function (my macro) doesn't work properly. While I'm dragging M5 cell in Sheet2 down to M14 cell it gives the wrong result.
Even when I change value in Sheet1 and refresh data of pivot table in Sheet2, my function rate1() isn't updated. But my problem is solving (getting the correct result) when I'm going Edit Mode (pressing function key F2) in active Cell M5 or M6 or M7..so on and pressing Enter each time.
View 4 Replies
View Related
Mar 7, 2014
I have created a simple UserForm to enter new customer details to the Customer List in the spreadsheet, form works fine except for one little thing, which is New Customer ID, which works, but only with numeric values like 1, 2, 3 and so on.
Basically what I need this for to do is once form is opened/called new customer ID need to be created, which could be and Alfa numerical set of characters like AA-01234, AA-01235, AA-01236 and so on.
Also, is there a way of posting newly added Customer ID in the MsgBox along with MsgBox "One record added to Customers List. New Customer ID is "
All of my attempts to create this are failing and causing errors.Here is my code, Customer ID is TextBox1.
[Code]....
View 10 Replies
View Related
May 24, 2008
I'm quite a novice at Excel. I have a column of values that I sum as follows;
A
0
0
0
0
0
0 <----------------sum of A1:A5
A formula may change one of the values in the above column to a '1' which means the sum will become '1'. The sum can only be '1' or '0' and only one value in the column will ever change. I need to add a value of 2 to another cell (say, C1) when the sum of A1:A5 changes from a value of '0' to '1'. I know this will probably involve the worksheet change event but am having a problem implementing it.
View 7 Replies
View Related
Jun 18, 2014
vba in excel 2013 pivot table that updates from an online CRM rows are a to h and it can be any number of rows. What I am trying to do is track progress. In column f values indicate probability for success 10 25 45 90, which can go up or down. The pivot table is refreshed to get the latest values from CRM. the update is handled by a connection to the crm not in the vba.
So far I been researching methods to conditionally format values that went up, down or remained the same since the last refresh with up down and across arrows. I have managed to piece together what I think should work but alas it is not. So I have come to you internet.
My code first clears any formatting and goes down the column avoiding null or empty cells, comparing the values in column f to values in column j.
I have 3 conditions greater than, less than or equal to, and would like add an icon for each based on the result of the comparison.
Finally when it finishes the column the code copies the current values in the pivot table column f to column j outside the pivot table which i hope to be able to hide once the cf works. The code is below
[Code] .....
View 1 Replies
View Related
Mar 5, 2013
I have the following code to update a pivot table:
Code:
Dim pt As PivotTable
Application.EnableCancelKey = xlDisabled
For Each pt In ActiveWorkbook.Worksheets("sheet1").PivotTables
pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:="source!R4C1:R33443C55" _
, Version:=xlPivotTableVersion10)
Next pt
the R33443 term is what will be changing, the columns and the starting row should stay the same. is there a way to instead of using R33443, to enable the range to be changed based on a cell value?
View 2 Replies
View Related
Feb 26, 2014
I have 5 pivot tables on 5sheets, all looking at the same source data in sheet 6
On the source data there is a filter on the headers, if you change the filter, is it possible for all 5 pivot tables to update according to the filter?
My starting point is the below, but there probably is a better way but i would want the sourcedata to equal visible rows on the source data sheet headers run A:K and up to row 10000 .
View 2 Replies
View Related
Sep 5, 2007
I am a absolute newbie to VBA, I've got 2 timetables, they show deliveries for different countries. I need to make my code as DYNAMIC as I can. TableCustomer shows the customer's name, country and their opening times, Saturday - Friday, it uses 1s and 0s to determine if they are open or closed, 1 = open, 0 = closed, so the headers are: Customer Name, Country, Saturday, Sunday, Monday, Tuesday, Wednesday etc.
TableCompany shows the days of deliveries made to each country by the company, using 1s and 0s, 1 = Deliver, 0 = Don't deliver. The headers are Country, Saturday, Sunday, Monday, Tuesday, Wednesday etc. An example here is that, if in TableCustomer, the customer is opened(1) on a Saturday, but in TableCompany, the company does not deliver(0) to that country on the Saturday, change that 1 into a 0 in TableCustomer. But if the Customer is closed(0) on the Saturday, then don't change the value 0, because the company will not be able to deliver to them anyway, even if they can deliver to that country on that day.
View 3 Replies
View Related
Aug 12, 2014
how to conditionally format the data in one table based on the criteria specified in another table on a different worksheet.
The Table I'm trying to format has a series of Products and Volumes that'll be available on different dates. Table 1.jpg
The Criteria table has each product and a corresponding Key Data. Table 2.jpg
All I want to be able to do is have the cell corresponding to the criteria to be highlighted but can't for the life of me figure out how to do it. If it makes a difference I'm working offAttachment 338501 a Mac. Excel Version 2011 14.4.3
View 3 Replies
View Related
Jan 14, 2013
I have a range of data on one worksheet that is pulled into a pivot table on another worksheet. Looking at the table below I would like to assign the values in the pivot table to a 1 if there is data. For example if any field is >0 I would like to assign it a 1. If not leave it blank. I tried using the formula function in the options tab but didn't have any luck.
Car
Truck
SUV
Color
Black
1
15
Red
7
1
Blue
1
5
View 4 Replies
View Related
Oct 24, 2007
I have a sheet that has to look up value on a report from a sheet sheet that has more than value. Ex)
On the report I have values 123A and 1234A
On the data tab I have table that has.
Ref Tot Value Desc Value1 Value2 Value3
1 123A Widgets 1 2 3
2 123A Widgets 4 5 6
Ref Tot Value Desc Value1 Value2 Value3
1 123B Nuts 7 8 9
5 123B Nuts 1 3 5
Ref Tot Value Desc Value1 Value2 Value3
7 1234A Bolts 2 4 6
11 1234A Bolts 3 5 8
So the report need find the values for Value 1, 2, 3 on where the value matches the data tab.
View 14 Replies
View Related
Jun 6, 2014
In my spreadsheet, each row of data in Column B starts with 3 numerics (e.g. 001) followed by some text. For example:
Cell B2 contains '001 - Text for this cell'
Cell B3 contains '002 - Different Text for this cell',
Cell B4 contains '003 - Different Text for this cell', etc.
I have hundreds of rows which follow this format.
I have had to insert several rows in my spreadsheet in between existing rows which has thrown off the numbering in the first three positions of my Column B cells.
Is there any way to update the data in Column B so the text data in each cell is retained but the first 3 numeric values are updated and again sequential?
View 3 Replies
View Related
Mar 16, 2012
I have made a custom list in Excel 2010 so it auto-increments the alphabet using the auto increment pull down/copy square on the bottom right of a cell in Excel. My question is I have some data in a sheet such as this
600
600
600
600
601
601
601
601
602
602
602
What I want to do is add a letter of the alphabet to the end of these numbers and have the alphabet auto increment based on the data above like this:
600
600A
600B
600C
601
601A
601B
601C
602
602A
602B
I thought it would be simple since I now have a custom list but every formula I try fails.
View 5 Replies
View Related
Nov 28, 2013
I've got 2 worksheets in the same workbook.
Sheet 1 contains huge amounts of data - thousands of rows and multiple columns
Sheet 2 - I want to extract the data from sheet 1 column A into sheet 2 column A but only the data from every 21st row.
I want to be able to copy the formula automatically down, otherwise it will take hours to do it manually. So far I can only get the cell reference to increment by 1 each time after copying.
So what I'm trying to achieve is :-
Sheet 2 A1 = Sheet 1 A1
Sheet 2 A2 = Sheet 1 A22
Sheet 2 A3 = Sheet 1 A43
View 3 Replies
View Related
Jan 29, 2010
I want to drag a formula however i want one of the values of the formula to change and the other not to change. for example:
c1=a1+b1
c2=a1+b2
c3= a1+b3
: : :
how can i make it do this when i drag? a1 is not just a constant and it depends on other parameters.
View 3 Replies
View Related
Aug 13, 2012
I'm coding a userform where there are some comboxes which are popolated by values coming from Sheet2. Up to now I work it out (maybe its not elegant but it works).
Now I would like that when the user selects one combobox the values of the other comboboxes are set accordingly to the grid in Sheet2.
Please download the XLS file at: [URL]....
View 2 Replies
View Related
May 12, 2014
how can i sum pivot table filtering some values in other table. if i change filter, sum is changed
View 1 Replies
View Related
Jun 24, 2014
Vlookup a table, to another table that has multiple values.
E.g.
Table
GPASP002
Look up table - need to return second coloum but not the first value, all the values?
GPASP002 KZASP100
GPASP002 KZASP500
GPASP002 KZASP600
GPASP002 KZASP501
GPASP002 KZASP502
GPASP002 KZASP601
View 2 Replies
View Related
