Excel function that returns column of a pivot table as an array

[azlefty]

Hello Mr. Excel and friends,

Please help. I'm trying unsuccessfully to write a function that looks for a column in a pivot table and returns the entire column as a text string. The function should take a pivot table and a text string as an argument. The text string is the name of one of the columns. The function needs to return the entire column (excluding the header and footer rows) as an array.

In the example I pasted below, the function should take the pivot table (PivotTable1) and a color, say "Red", as arguments, and then return the values in the Red column as a 48x1 single vector array, in the order shown, starting with 43 and ending with 1. I can only post a text version of the spreadsheet; I was not able to post the actual spreadsheet.

Thanks everyone.

Average of Cost		Color
Date	Hour	Blue	Red	Yellow	Grand Total
9/1/2012	1	$12.00	$43.00	$41.00	$32.00
	2	$36.00	$42.00	$26.00	$34.67
	3	$5.00	$38.00	$44.00	$29.00
	4	$27.00	$33.00	$22.00	$27.33
	5	$43.00	$4.00	$44.00	$30.33
	6	$35.00	$11.00	$35.00	$27.00
	7	$13.00	$8.00	$47.00	$22.67
	8	$13.00	$12.00	$25.00	$16.67
	9	$3.00	$9.00	$2.00	$4.67
	10	$49.00	$46.00	$25.00	$40.00
	11	$41.00	$19.00	$31.00	$30.33
	12	$16.00	$45.00	$30.00	$30.33
	13	$30.00	$33.00	$38.00	$33.67
	14	$15.00	$22.00	$49.00	$28.67
	15	$22.00	$38.00	$14.00	$24.67
	16	$38.00	$23.00	$5.00	$22.00
	17	$38.00	$21.00	$7.00	$22.00
	18	$48.00	$31.00	$47.00	$42.00
	19	$8.00	$27.00	$5.00	$13.33
	20	$3.00	$35.00	$14.00	$17.33
	21	$25.00	$29.00	$17.00	$23.67
	22	$33.00	$32.00	$47.00	$37.33
	23	$30.00	$18.00	$36.00	$28.00
	24	$42.00	$25.00	$47.00	$38.00
9/2/2012	1	$47.00	$48.00	$49.00	$48.00
	2	$8.00	$2.00	$28.00	$12.67
	3	$29.00	$44.00	$16.00	$29.67
	4	$15.00	$24.00	$35.00	$24.67
	5	$4.00	$9.00	$30.00	$14.33
	6	$13.00	$31.00	$47.00	$30.33
	7	$30.00	$39.00	$39.00	$36.00
	8	$29.00	$20.00	$15.00	$21.33
	9	$43.00	$28.00	$19.00	$30.00
	10	$22.00	$17.00	$13.00	$17.33
	11	$6.00	$3.00	$8.00	$5.67
	12	$17.00	$14.00	$40.00	$23.67
	13	$17.00	$37.00	$40.00	$31.33
	14	$17.00	$10.00	$43.00	$23.33
	15	$12.00	$34.00	$3.00	$16.33
	16	$26.00	$50.00	$31.00	$35.67
	17	$34.00	$32.00	$26.00	$30.67
	18	$1.00	$37.00	$38.00	$25.33
	19	$17.00	$17.00	$21.00	$18.33
	20	$40.00	$24.00	$8.00	$24.00
	21	$37.00	$37.00	$7.00	$27.00
	22	$34.00	$45.00	$1.00	$26.67
	23	$31.00	$16.00	$7.00	$18.00
	24	$21.00	$1.00	$17.00	$13.00
Grand Total		$24.48	$26.31	$26.65	$25.81

<colgroup><col><col><col span="3"><col></colgroup><tbody>
</tbody>

 

[Marcelo Branco]

Maybe this

Function selColorRange(SheetName As String, PvtName As String, PvtField As String, _
                        PvtItem As String) As Range
    
    Set selColorRange = ThisWorkbook.Sheets(SheetName).PivotTables(PvtName).PivotFields(PvtField). _
        PivotItems(PvtItem).DataRange
    
End Function

Assuming
Sheetname = Sheet1
PivotTable name = PVT1
Field Name = Color
Color = Red

Usage (for example to get the MAX of Red values)

=MAX(selColorRange("Sheet1","PVT1","Color","Red"))

M.

 

[azlefty]

Maybe this

Function selColorRange(SheetName As String, PvtName As String, PvtField As String, _
                        PvtItem As String) As Range
    
    Set selColorRange = ThisWorkbook.Sheets(SheetName).PivotTables(PvtName).PivotFields(PvtField). _
        PivotItems(PvtItem).DataRange
    
End Function

Assuming
Sheetname = Sheet1
PivotTable name = PVT1
Field Name = Color
Color = Red

Usage (for example to get the MAX of Red values)

=MAX(selColorRange("Sheet1","PVT1","Color","Red"))

M.

Yes, that seemed to work without the MAX function. Thanks Marcelo! Much appreciated.

 

[Marcelo Branco]

You are welcome and thanks for the feedback.

M.