I need to find a way of determining the row number from the data source table of the selected cell in my pivot table.

The reasoning behind this is that I want to be able to edit/update the values from a UserForm.

My table with the data source is in sheet 1 named Data and my pivot table is in sheet 2, named Project management.

I know I can edit values in a Pivot Table by setting

EnableDataValueEditing = False

..but this does not give me the desired result since my original data remains unchanged and I also have hidden columns that I use for conditional formatting only and it would be a bad user experience to have to unhide the columns every time I need to edit them.

Does anyone have any idea or suggestion as to how I can retrieve the original data row number for a specific cell in my pivot table ?

As an example, the top is my original table and the bottom right is my pivot table. When the cell "Dummy Data" is pressed, I want it to give me the Row number of that data from my original table.

A "bad" workaround would be to use VBA to match the selected cell value with the data from the table and when the match is found, retrieve the address but this is not foolproof, the data should be unique but with more data flowing in over time, this might not be the case anymore. Therefore I am trying to avoid this method.

PS: if someone could edit my post and embed the image, I would highly appreciate it.

CodePudding user response：

A pivot table is a report summarizing information from the linked table or query. The only ways you're going to be able to update the information is if you know the row ID/number you want to change or filter the data source down to a point where you can identify it.

If you're going to be keeping and altering records, I'd suggest building a database (Access, for something quick and easy) to enforce some form of integrity and then link your pivot table and form to that database.

EDIT: VLookup or Index(Match(...)) is only going to return the first hit and that may not necessarily be what you want.

CodePudding user response：

I will put my non-orthodox but perfectly functional way of doing it just in case someone is facing a similar issue or until a better way arises.

I have actually added a new column in my data table with the formula

=ROW([@Column1])

This gives me the row number for every entry.

Next I am just adding that column as a row in my pivot table, display in tabular mode to have it's own column and hide it. Therefore when selecting a cell, I am looking in the hidden column for the number and therefore I am getting the source data row number.