=min(100, 'selected cell'/15*100)
Type: Posts; User: sdckapr; Keyword(s):
=min(100, 'selected cell'/15*100)
Is the code located in the ThisWorkbook Object? It needs to be located there.
Some formulas must be entered explicitly as an array (ie using the ctrl-shoft-enter). Some formulas implicitly work with arrays (sumproduct, index, etc) so the formula does not explicitly need to...
Are all the rows unhidden in both the copy and the source?
This is not a bad source for the information: http://office.microsoft.com/en-us/excel-help/create-a-custom-number-format-HP010342372.aspx
The IRR can get a Div/0 error on some occasions if during the iterations the function gets that type of error internally. I think having the values flip too much between positive and negative can do...
Same as before: details of a specific example and how you are calculating it, so we can try and understand the issue.
could you provide details of the situation or at least an example that we can work through. Also what is an "EAY"?
Change the formula in B1 to something like:
And it will sort the way you expect it to sort. By explicitly adding a sheetname (even if it is sheetname on the sheet), the...
I don't understand the logic to get 0.21 as the average. The average of the values from B5:B15 is only 0.17. You can get this average with the array formula (confirm with ctrl-shift-enter):...
At runtime you could determine the number of pivottables in the workbook:
x = 0
For each wks in Activeworkbook
x = x + wks.PivotTables.Count
x = x+1
and then in your creation code use...
A fill hides the grid. If you follow Retired Geeks Instructions you can add borders to those cells which will be over the fill and act as a grid.
This is cross-posted at http://www.eileenslounge.com/viewtopic.php?f=27&t=17311&sid=1eb5c3b7be4bf1d4a149a9d0796a749a. That forum has answers in it, so please go there for any followup and additional...
I am not sure I understand completely. Could you elaborate? Is it ONLY the number 1 or is it more general. If a duplicate is found, what should happen to the duplicate cell: clear it, change it to a...
There is no need to copy/paste
' Date_Enter Macro
ActiveCell.Value = Date
ActiveCell.NumberFormat = "d mmmm yyyy"
An alternative which only requires entering the values in 1 place is to use match/index.
Public Function Grade(rng As Range) As String
'DECLARE AND SET VARIABLES
Will 2 pivot tables work like in the attached?
If it is more than one cell, you can also use in VBA something like:
If using the shortcut key, you can select the cells you want to change the date of:
Perhaps this example will work.
You can essentially accomplish what you have with your formula with:
You can use the line of code:
ActiveCell = Date
You don't need a button, you can just use the shortcut key:
to add the date to the current cell. [ctrl-shift-; (= ctrl-: ) adds the...
I wholeheartedly second RetiredGeek's comments about Option Explicit.
I don't understand your use of the Grand_Total named range. In the file in Aug 14, it was in cell FW$ so I assumed it served some purpose unrelated to the pivot table. But in this one the name is...
Change the ">60" to the respective "60-90", "90-120", and ">120" as listed in the Pivot table col a.
It is your formula, with my addition to give you the range desired. That part is:
OFFSET('Pivot Table'!$B$1,MATCH(">60",'Pivot Table'!$A:$A,0)-1,0,1,MATCH("Grand Total",'Pivot Table'!$4:$4,0)-2)