Excel: Pivot Tables Edit Row

One of the problems with a Pivot Table is that you can't easily edit the underlying data.

However it is possible to create an "edit" link to edit a row of a pivot table:

A formula like this will create an "Edit" link which jumps to the UID of the pivot table row:

=IFERROR(HYPERLINK("#Sheet1!" & ADDRESS(MATCH(G4,Sheet1!J:J,0),12), "Edit"),"")

Here's an example breakdown of the formula:

=IFERROR(

    HYPERLINK(

        "#'sheetName'!" & ADDRESS(   // sheetName: Name of the sheet that contains the database

            MATCH(

                uidCell,             // uidCell: The cell in the Pivot Table that contains the UIDs

                uidColumn,           // uidColumn: The UID column in the Database

                0                    // Exact Match

            ),

            columnNumToJumpTo        // columnNumToJumpTo: The column in the Database to jump to

        ), 

        "Edit"                     

    ),

    ""

)

If there are multiple comma-separated UIDs returned in a DAX formula, then this formula will create a hyperlink to the first instance:

=IFERROR(HYPERLINK("#Sheet1!" & ADDRESS(MATCH(TAKE(TEXTSPLIT(G4,","),1,1),Sheet1!J:J,0),12), "Edit"),"")

Here's a breakdown of this formula:

=IFERROR(

    HYPERLINK(

        "#'sheetName'!" & ADDRESS(   // sheetName: Name of the sheet that contains the database

            MATCH(

                TAKE(                // If you have multiple UIDs (using a DAX formula) then...

                    TEXTSPLIT(       //   ... split the UIDs 

                        uidCell,     // uidCell: The cell in the Pivot Table that contains the UIDs

                        ","          //  ... assumes DAX creates comma separated UIDs

                    ),

                    1,1              //  ... and grab the first cell

                ),

                uidColumn,           // uidColumn: The UID column in the Database

                0                    // Exact match

            ),

            columnNumToJumpTo        // columnNumToJumpTo: The column in the Database to jump to

        ), 

        "Edit"                     

    ),

    ""

)