Refresh a sheet that come from a pivot table

Dear Alice BORE,

Kindly refer to the attached file. This is basically a sample file that demostrates how the entire activity can be done by using formulas, without the use of a Pivot Table.

The Grey coloured cells which have been introduced in the Data sheet are Index columns. Yellow coloured cells contain formulas. Orange coloured cells have Drop down menus for selection.

There are 2 independent output sheets. You may retain the sheet that you prefer to use.

The original sheets have not been disturbed. A Data sheet is a replica of the original data.

The entire output has been generated using the Index function. Match function has been used for both Rows and Columns. The Index Columns, filter out the required data. If you need to filter the  data based on multiple criteria, then edit the formula in the grey index columns.

The ISBLANK function helps to eliminate zero from blank cells. In case cells in the data sheet, which appear to be blank have some formula, the you may need to replace the ISBLANK part with If(LEN(Index Formula)=0,"", Index formula)

Do let me know if this is similar to what you want to do. If you need some clarifications or ammendments, then let me know.

Vijaykumar Shetye,

Spreadsheet Excellence,

Panaji, Goa, India