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"
),
""
)