Aug 31 2020 06:16 AM
Hello,
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.
B.R. Susanne
I
Aug 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.
Aug 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.
Aug 31 2020 11:32 PM
@mathetesand @Sergei Baklan - thank you for your answers.
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 :)
Sep 01 2020 05:08 AM
I didn't find exact reason, but if re-build PivotTable it works. Most probably something with caches. Please check attached.
Sep 02 2020 03:10 AM
Hello again,
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.
Sep 02 2020 12:23 PM
My guess an error appears if you work with macros, but that's not my territory. If not, could you please share how to reproduce it on the workbook without macros.
Sep 02 2020 01:31 PM
Change your formula in D2 to include INT(B2) rather than just B2 - this looks for the whole number and discounts any trailing decimals which can throw off a formula looking for an exact match:
=VLOOKUP(INT(B2),Customer!A:B,2,FALSE)
Sep 02 2020 10:34 PM
Yes I work a lot with macros/VBA and that can give some 'fun'- most of the time it works perfectly.
Perfect ! I have changed it and it works without problems - thank you :)