Log In
Register
Home
December 29, 2022
Offline
Hello Everyone,
I've read through several posts in an effort to remedy the issue I'm having with no luck. Essentially, following the instructions Mynda posted related to the Interactive Finance dashboard, I've attempted to replace/duplicate the Transactions page with my information, as well as changed the Data Validation entries to items more fitting to our finances. In the most frustrating fashion, I cannot figure out how to update/refresh/link the Sub-category, Category and Category type to my transactions, hence updating the dashboard.
I’ve watched several of Mynda's amazing videos online in an effort to find bits and pieces of info to help update the data with no luck, I have a few days in this effort and I'm at a dead stop unfortunately.
Any advice you could offer would be fantastic!!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Sammie,
Please upload a sample file, we cannot see what you did in that file to be able to help.
Thanks
December 29, 2022
Offline
Thank you, I truly appreciate any insight you could provide!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Sammie,
In Data Validation sheet, the tblDV table is the source for Transaction categories, there should be no dropdown here. Remove the dropdowns and manually adjust to the categories you need.
In Transactions sheet - Sub-Category column, there is a dropdown related to Data Validation sheet - sub-category column, this is where you choose a sub-category for each record in transactions. The other 2 columns: Category and Category Type are using formulas to bring the corresponding fields from tblDV:
=XLOOKUP([@[Sub-category]], TblDV[Sub-category],TblDV[Category],"")
You can use VLOOKUP if XLOOKUP is not available in your excel version:
=VLOOKUP([@[Sub-category]],TblDV,2) (these 2 columns should not be formatted as Text for formulas to work)
December 29, 2022
Offline
Hi Catalin, I can't thank you enough for your response, it was very helpful! I reformatted the data validation sheet to remove the drop downs as you've mentioned, then attempted to perform the vlookup with your instructions along with following Mynda's video. It's almost as if the Transaction sheet name is off a bit possibly, however I cannot confirm that to be the case. The reason I mention that is because I'm using the formula =VLOOKUP([@[Sub-category]],TblDV,2) because I'm using a newer version of Excel (Office Pro Plus 2019), however I'm receiving #NAME? in the Category field.
I searched the web for this error and I've learned that I may have the name of the transaction table sheet wrong, however I'm unsure.
Thank you again for your advice, I appreciate it more than you know.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Sammie,
Manually type the table name:
it should be TBLDV, my guess is that instead of lower case "L" you have upper case "i"
December 29, 2022
Offline
Catalin, it worked!! Thank you, now working to fill in the category type, however since I manually put in the formula, I don't quite understand how to replace Category with Category type :(.
Once that is complete, I'm wondering how would you update the data in the sheet to reflect on the analysis and Dashboard?
Again, I can't thank you enough for your help, you've been a life saver!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
In this formula:
=VLOOKUP([@[Sub-category]],TblDV,2) , 2 represents the second column - Category
Replace with 3 to get the third column - Category Type:
=VLOOKUP([@[Sub-category]],TblDV,3)
How to update data depends on how you get the data, in the file you have data is manually typed or pasted. Easiest way, if you have external data, to use Power Query to bring that data into the file.
December 29, 2022
Offline
That makes sense, thank you for explaining! Now all columns are displaying the appropriate category.
As for the data, it was pasted into the transactions sheet under Mynda's original format. I'm not quite sure how to update what we have in the "new" transactions with the analysis and dashboard..
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
I guess you did not discovered yet the Refresh button?
After you paste your data in Transactions sheet, go to Data tab in ribbon and press the Refresh All button. That is all you need to do to update the reports.
December 29, 2022
Offline
Yes, and thank you. I've been trying the refresh all button for a week now, however I'm encountering an error (Cannot change part of an array), therefore the data doesn't update. I looked on the internet to see if I could find a resolution with no luck.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
you have an array formula somewhere where the results are spilled over multiple cells, one of the pivots are trying to expand over those cells. Make sure there is enough room for pivots to expand, they cannot overlap one over another or over array formula cells.
1 Guest(s)
