• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Interactive Finance Dashboard Updating Issue|Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / Interactive Finance Dashboard Updating Issue|Dashboards & Charts|Excel Forum|My Online Training Hub

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumDashboards & ChartsInteractive Finance Dashboard Updat…
sp_PrintTopic sp_TopicIcon
Interactive Finance Dashboard Updating Issue
Avatar
Sammie Meadows
Member
Members
Level 0
Forum Posts: 6
Member Since:
December 29, 2022
sp_UserOfflineSmall Offline
1
December 29, 2022 - 11:40 pm
sp_Permalink sp_Print

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!!

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1800
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
December 31, 2022 - 7:23 pm
sp_Permalink sp_Print

Hi Sammie,
Please upload a sample file, we cannot see what you did in that file to be able to help.
Thanks

Avatar
Sammie Meadows
Member
Members
Level 0
Forum Posts: 6
Member Since:
December 29, 2022
sp_UserOfflineSmall Offline
3
January 4, 2023 - 2:15 am
sp_Permalink sp_Print

Thank you, I truly appreciate any insight you could provide!

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1800
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
January 8, 2023 - 3:46 pm
sp_Permalink sp_Print

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)

Avatar
Sammie Meadows
Member
Members
Level 0
Forum Posts: 6
Member Since:
December 29, 2022
sp_UserOfflineSmall Offline
5
January 9, 2023 - 9:23 am
sp_Permalink sp_Print

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.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1800
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
January 10, 2023 - 3:15 pm
sp_Permalink sp_Print

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"

Avatar
Sammie Meadows
Member
Members
Level 0
Forum Posts: 6
Member Since:
December 29, 2022
sp_UserOfflineSmall Offline
7
January 11, 2023 - 3:04 am
sp_Permalink sp_Print

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!

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1800
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
January 11, 2023 - 3:56 am
sp_Permalink sp_Print

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.

Avatar
Sammie Meadows
Member
Members
Level 0
Forum Posts: 6
Member Since:
December 29, 2022
sp_UserOfflineSmall Offline
9
January 11, 2023 - 6:33 am
sp_Permalink sp_Print

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..

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1800
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
10
January 11, 2023 - 3:13 pm
sp_Permalink sp_Print

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.

Avatar
Sammie Meadows
Member
Members
Level 0
Forum Posts: 6
Member Since:
December 29, 2022
sp_UserOfflineSmall Offline
11
January 12, 2023 - 2:59 am
sp_Permalink sp_Print

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.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1800
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
12
January 12, 2023 - 3:37 pm
sp_Permalink sp_Print

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.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 170
Currently Online: Bouskila stephanie, Brenda Krol, James Hwang
Guest(s) 51
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1431
Anders Sehlstedt: 848
Velouria: 574
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 213
A.Maurizio: 202
Aye Mu: 201
Jessica Stewart: 185
Newest Members:
David Collins
Andras Marsi
Orimoloye Funsho
YUSUF IMAM KAGARA
PRADEEP PRADHAN
Vicky Otosnika
Abhishek Singh
Kevin Sojourner
Kara Weiss
And Woox
Forum Stats:
Groups: 3
Forums: 24
Topics: 6047
Posts: 26544

 

Member Stats:
Guest Posters: 49
Members: 31497
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Riny van Eekelen
© Simple:Press —sp_Information
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x