Hi all
Although i'm not a novice when it comes to the excel side of things, and i can navigate myself round some pretty complex formulas, i have never dabbled in excels power query tool and have little to no knowledge of SQL queries either.
Despite that I've had a go and need some help please, so here we go with the attempt on explaining whats going on.....
I have 3 spreadsheet where i record all the sites accidents, incidents and near misses on, within each workbook there is a tab called Log where each row represents a new event. Each row has many columns with lots of information regarding the event.
I have created a separate spreadsheet called the dashboard to gather all this data/statistics into one place, this is where i have also built 3 separate data query tabs for the 3 logs pulling in the data.
I have edited what i'm pulling in, by deleting columns not wanted and also applying filters within the power query editor.
The filters are applied on site and open cases only, meaning they only show a few lines at most in the queries.
I have created a report tab where rows 1 to 15 reference certain cells from rows 1 to 15 on the accident query then rows 15 to 30 for incidents and rows 30 to 45 for near misses. I then filter out blank lines showing only the open cases for the site i work on. Its not quite exactly this layout but this is the easiest way to explain.
So on the report tab A1 will reference cell A1 on the accident query tab, using a if(A1="","",a1 kind of formula. and so on and so forth for each row up to a max of 15 rows for each query.
It all works great up until the query lines change info, such as one has closed and / or a new one opened. My report tab row will REF out, or change rows all together. meaning i'm missing data within the report instead of just pulling in the data within the cell.
I hope that makes sense to someone that can help me out with this very annoying problem.
Regards Tony
Although i'm not a novice when it comes to the excel side of things, and i can navigate myself round some pretty complex formulas, i have never dabbled in excels power query tool and have little to no knowledge of SQL queries either.
Despite that I've had a go and need some help please, so here we go with the attempt on explaining whats going on.....
I have 3 spreadsheet where i record all the sites accidents, incidents and near misses on, within each workbook there is a tab called Log where each row represents a new event. Each row has many columns with lots of information regarding the event.
I have created a separate spreadsheet called the dashboard to gather all this data/statistics into one place, this is where i have also built 3 separate data query tabs for the 3 logs pulling in the data.
I have edited what i'm pulling in, by deleting columns not wanted and also applying filters within the power query editor.
The filters are applied on site and open cases only, meaning they only show a few lines at most in the queries.
I have created a report tab where rows 1 to 15 reference certain cells from rows 1 to 15 on the accident query then rows 15 to 30 for incidents and rows 30 to 45 for near misses. I then filter out blank lines showing only the open cases for the site i work on. Its not quite exactly this layout but this is the easiest way to explain.
So on the report tab A1 will reference cell A1 on the accident query tab, using a if(A1="","",a1 kind of formula. and so on and so forth for each row up to a max of 15 rows for each query.
It all works great up until the query lines change info, such as one has closed and / or a new one opened. My report tab row will REF out, or change rows all together. meaning i'm missing data within the report instead of just pulling in the data within the cell.
I hope that makes sense to someone that can help me out with this very annoying problem.
Regards Tony
