GETPIVOTDATA('price per unit', B2, 'division', 'east', 'subdivision', 2) GETPIVOTDATA('AVERAGE of price per unit', A1, 'division', 'east') GETPIVOTDATA('SUM of number of units', 'Pivot table'!A1) 'Sheet9.Extracts an aggregated value from a pivot table that corresponds to the specified row and column headings. With Selection.Borders(xlInsideHorizontal) 'always has data, so it is a good basis for measuring the length 'of a pivot table (I chose column 'B' in 'lrow' becuase it 'This section re-draws the borders based on the length Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Range(Selection, Selection.End(xlDown)).Select 'This section clears any previous formating Sheet9.Activate 'this is the same 'CashFlow' sheet Lrow = WS.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row I thank you in advance for your time and attention FilesĬode Private Sub Worksheet_Change(ByVal Target As Range)

I think that whatever solution you can provide (if you can.) should be based a workbook_change event, because in my “real” file, the length of the table is changed whenever I select a different “page” of the PT. However, the table can grow or shrink (in length) and I want to see borders only around cells that are on the same rows as the data in column “B” (staring with B5). The idea is to make the cells in columns “D:G” look like they are a natural extension on the PT. I have tried doing it with conditional formatting but there are some other rules which contradict this setting. What I now need to do is add borders to the cells in columns “D:G” only if the cells in “B5” and downwards are not empty. In my 'real' file, the cells next to the PT are formatted with conditional formatting based on other values in the sheet (see attached sample file). I have a pivot table and next to it I added some calculations which are based on data from the table.

I am faced with a small challenge whish is beyond the scope of my current level of knowledge.

Excel getpivotdata function true