Feb 22 2024 02:15 PM
I am trying to compare information from two pivot charts - in Pivot Table A - I have company names in column A, then the number of devices said company is paying for in column B. In Pivot Table B - I have company names in column D and the number of devices we have listed in our system in column E. I want to show the difference between computers from Pivot Table A and Pivot Table B. I want to be able to look at a glance if the number from column B is larger or smaller than column E. However there are more company names in Pivot Table A than Pivot Table B, so i can not just compare the cells.
I already copied the company names from both pivot tables into column G and removed duplicates, but when i try to paste this function =IFERROR(VLOOKUP(G2, 'Pivot Table A'!$A$2:$B$100, 2, FALSE) - VLOOKUP(G2, 'Pivot Table B'!$D$2:$E$100, 2, FALSE), "") it does not calculate at all, and when i hit enter it tries to open a file - i have copied it pasted it in everyway imaginable ------please help
Feb 22 2024 09:32 PM
@nikkil85 Are 'Pivot Table A' and 'Pivot Table B' the names of your pivot tables, or the names of the worksheets where the pivot tables are located? Unlike an Excel table, a pivot table cannot be referenced in a formula by name. In your first VLOOKUP formula, for example, 'Pivot Table A'!$A$2:$B$100 is referring to range A2:B100 on worksheet 'Pivot Table A'. If no such worksheet name exists in the workbook, Excel assumes the range reference is external, and will prompt you to open the source file where this range can be found. Be sure to use the Sheet Name in your formula where the data is located.
If you're still experiencing issues, please consider sharing a mock-up file, or screenshots showing your current setup (including the sheet tabs) with sensitive data blacked-out. There may be alternative methods to VLOOKUP that are more appropriate here, but additional information is required in order to make such suggestions.
Feb 23 2024 12:01 AM
Based on your description, there are a few potential issues that might be causing the problem:
1. Incorrect File Reference:
2. Circular Reference:
3. Missing Workbook:
4. Data Type Mismatch:
5. Formula Syntax Error:
Additional Tips:
Remember, the key is to identify the specific cause of the file open prompt and address it accordingly. By carefully analyzing the potential issues and trying the suggested solutions, you should be able to resolve the problem and use the VLOOKUP function effectively.
Feb 23 2024 02:15 AM
@smylbugti222gmailcom Are you a real life person, or are you a bot? Either way, your posts smell suspiciously like AI generated content and often miss the point of the question.
Feb 23 2024 03:14 AM - edited Feb 23 2024 03:16 AM
Adding a layer: Use AIs at your convenience if you trust them. Assuming they provide possible solutions the minimum would be IMHO to:
And BTW, the OP on Excel Privacy Level Dialogue Box on data refresh - Making the right selection asked you a question (I have the answers but will give you time to respond)