KingJulian
New member
- Joined
- Jun 7, 2018
- Messages
- 4
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- 2016
Hello everyone,
I've had some big benefits due to my recent discovery of Power Query in Excel and Ken's book M is for (Data) Monkey. My company works in the accounting arena providing financial controls to some very large projects at fortune 100 companies. I was hired to help bring some sanity to the situation.
Background: Our team lead worked on one project to provide controlling advice on one project. Being an excel whiz/accountant, he whipped up a nifty 10-tab spreadsheet with hard-coded references and data tabs that draw from a general ledger (50,000+ lines) and other sources that are placed into the workbook. It works so well and provides such accurate and timely information that the once project expands to five with 16 additional projects on the horizon. For the 5 projects, he has a master, portfolio level workbook where he copies and pastes the information from the 5 individual projects on a daily basis. Additionally, he manually updates the reference pages to match the master workbook. These tasks result in lots of wasted time since the workbooks are stored on a Sharepoint and take considerable time to load and calculate on the laptops we're provided with. I wish to optimize this process to create a single repository for reference information and, instead of copying data daily, reference the data contained in the smaller projects.
Problem: VLOOKUP and INDEX/MATCH both return #REF or #N/A when referencing the power query loaded as a table in the subordinate workbooks. There is a catch: They only do this when a formula is used to find the lookup value. The following works flawlessly:
=VLOOKUP(515080,Subaccounts,3,0), where 51080 is the account in the Subaccounts query (loaded as a table).
This, however, does not: =VLOOKUP(MID($G2,2,6),Subaccounts,3,0) and returns #N/A. Building a separate cell =MID($G2,2,6) returns 515080. A VLOOKUP with that cell reference instead of the MID() call also returns #N/A.
I also tried Index/Match with similar perplexing results. Using a hard-coded value in the function to lookup a result is successful, but if the function contains a string manipulator or reference to another cell, it does not work.
Being primarily an Access/VBA person, I am worried I am missing a syntax bit, but the odd behavior between a function derived answer vs. a hard-coded answer makes me wonder that this is a limitation of Excel. Does anyone have any experience with External queries that could help with this?
Thanks,
Jul
I've had some big benefits due to my recent discovery of Power Query in Excel and Ken's book M is for (Data) Monkey. My company works in the accounting arena providing financial controls to some very large projects at fortune 100 companies. I was hired to help bring some sanity to the situation.
Background: Our team lead worked on one project to provide controlling advice on one project. Being an excel whiz/accountant, he whipped up a nifty 10-tab spreadsheet with hard-coded references and data tabs that draw from a general ledger (50,000+ lines) and other sources that are placed into the workbook. It works so well and provides such accurate and timely information that the once project expands to five with 16 additional projects on the horizon. For the 5 projects, he has a master, portfolio level workbook where he copies and pastes the information from the 5 individual projects on a daily basis. Additionally, he manually updates the reference pages to match the master workbook. These tasks result in lots of wasted time since the workbooks are stored on a Sharepoint and take considerable time to load and calculate on the laptops we're provided with. I wish to optimize this process to create a single repository for reference information and, instead of copying data daily, reference the data contained in the smaller projects.
Problem: VLOOKUP and INDEX/MATCH both return #REF or #N/A when referencing the power query loaded as a table in the subordinate workbooks. There is a catch: They only do this when a formula is used to find the lookup value. The following works flawlessly:
=VLOOKUP(515080,Subaccounts,3,0), where 51080 is the account in the Subaccounts query (loaded as a table).
This, however, does not: =VLOOKUP(MID($G2,2,6),Subaccounts,3,0) and returns #N/A. Building a separate cell =MID($G2,2,6) returns 515080. A VLOOKUP with that cell reference instead of the MID() call also returns #N/A.
I also tried Index/Match with similar perplexing results. Using a hard-coded value in the function to lookup a result is successful, but if the function contains a string manipulator or reference to another cell, it does not work.
Being primarily an Access/VBA person, I am worried I am missing a syntax bit, but the odd behavior between a function derived answer vs. a hard-coded answer makes me wonder that this is a limitation of Excel. Does anyone have any experience with External queries that could help with this?
Thanks,
Jul