After range and formula expansion
November 30Hits:0
Hi,
I' ve the same issue, i need to introduce a subtotal with the after range function, but i've a formula in a column, which is expansioned, this formula reffers to members in the rows, the problem is when the after range row is introduced the formula doesn't take this row into account so the reference is bad.
I explain myself:
a b c
1 10000 clientA evget(a1,b1....)
2 20000 ClientA evget(a2,b2....)
3 10000 clientB evget(a3,b3,....)
But if i insert the after range
a b c
1 10000 clientA evget(a1,b1.....)
2 20000 clientA evget(a2,b2...)
3EVAFTER =evsum
4 10000 clientB evget(a3,b3....)
If you can see in the row 4 the formula lost the correct reference.
Any suggestion?
I'm thinking in put the input schedule into stactic but is not a good solution, i can't insert blank rows because in the expansion i'm saying the bas members of a parent.
Thanx in advance
Answers
Hi Jose,
If you have an expansion in the rows and columns, then just write the formula as evget($A1,$B1....) in the first row.
Please note that I have used $ symbol in front of the column number to make them static. The row number has been kept dynamic. Now, if you run an expand, then the formula will be copied to the other cells, appropriately.
Hope this helps.
Read other 3 answers
Tags:
Related Articles

Hi, I' ve the same issue, i need to introduce a subtotal with the after range function, but i've a formula in a column, which is expansioned, this formula reffers to members in the rows, the problem is when the after range row is introduced the formu

Is there a way to pass Two (2) dates from a single formula to the Select Expert? I calculate Beginning and Ending dates of periods ie: fiscal year to date, calendar year to date, last calendar quarter. I would like to pass both the beginning and end

HERE IT IS: Private Sub fillLicenseFormulas() Dim LastRow As Long LastRow = ActiveSheet.UsedRange.Rows.Count Dim gRange As String gRange = "G3:G" & LastRow & "" Range("g3").Formula = "=IF($G$3($F$3+$E$3)<0,0,

Hello I have an EVDRE() SAP BPC report where one of the member id selection is derived using a VBA script. The problem I'm having is when I click on expand and refresh icon, the member id changes to #Value and the expansion and refresh fails as #Valu

Hi, I have a doubt that i would like to be clarified. I need to split a partition of a table with more than 800 Million records, but at the same time (during the split operation) i need to insert new records (not in the partition that is being splite

I have created a Column Line Chart and allow the user to input different values that change formulas to produce the columns and line on the graph. I can see the graph perfectly fine with all columns and the line on the canvas view. Whenever I try to

Hi ABAP Experts, I created one Inbound IDOC using some standard segments. When I see the Idoc using WE02 and double click on it it does not show me the same no of segments as I included in 'IdocType'. Can someone please suggest how to check and get

This error was found in Excel 2010 32 bit but may be present in other versions. The error happens when writing specific dates to an Excel cell. The VBA code Dim datDate as Date datDate = CVDate(41586.9999999995) ActiveWorkbook.Sheets("Sheet1").C

Hello, I am really a beginner in Excel (2007). I am not sure if this is the correct forum, anyway. I have grouped some rows in a sheet (rows 1, 2 and 3, columns A and B, table below), some of the columns have numeric values in that group and I have a

Hi Guys, I wonder what is best practice, having YTD and MTD as a Measure for each of the core measures like: Actual Actual YTD Actual MTD Budget Budget MTD Budget YTD .. about 30 measures Or having it in the time dimension 2010 Qtr Jan Ja