08-31-2020 06:16 AM
08-31-2020 06:16 AM
I have made a pivot table, where I use a field in the filter to lookup another informations. For example I use the customer number as a filter in the pivot - but I also use it to 'vlookup' the customer name, address, bonus.
But now I have by a mistake entered a wrong customer number, that doesn't exists and I accidently pulled 'Ok' to rename the data. I renamed it back - but now my lookups don't work anymore for that particular customer no.
I have tried several things several times :
Checked the spelling and no blancks - removed the field - refreshed the pivot - recalled the 'raw data'
- checked the vlookup. Nothing helps.
It works perfectly for all the other customer, but not this one.
I hope you can help me.
08-31-2020 07:09 AM
It sounds like you've done a reasonable search for possible causes. Is it possible for you to post a copy of the spreadsheet(s), just making sure first to remove any actual names, addresses, etc.--i.e., any and all confidential or personal info.
08-31-2020 07:30 AM
Try simply to compare value for changed number with one in lookup array, like =Cell1=Cell2
Perhaps data type was changed from number to text (check the cell by =ISTEXT()). Or non-printable character was added (check by =LEN() ). Or something else. As @mathetes mentioned without sample file it's hard to say something concrete.
08-31-2020 11:32 PM
Now I have made a light version of the spreadsheet :
On sheet 'Pr. kunde' - in field 'CustAddrNo' :
If I use no. 2001792 everything is fine.
If I use no. 2001811 my lookups are interrupted.
I have tried @Sergei Baklan's suggestions and it seems to be text now.
How did that happened ? (I know it happened when I by a mistake overwrite the number and then changed it back - but why did it then became text ?)
How do I correct it ?
And how do I prevent other users to do the same failure ?
Hope you can help
09-02-2020 03:10 AM
I have now rebuild the pivot, but I can still enter a non-existing number in the filter and then I get this warning/question "No item of this name exists in the Pivottable report. Rename '2001811' to '8888888'?". See attached.
How do I prevent this ?
In your example I can only enter existing number, which is what I want.