Refer to dynamic table in closed workbook

[jaryszek]

Hi,

i noticed that i can not refer to dynamic table in closed workbook because i am getting error.

I used NamedRange instead:

(i have to paste because firewall is blocking me - last time this is happening often).

Screenshot_12.png

How can i solve this?

I am trying to avoid macros here.

here screen to download:

https://drive.google.com/file/d/1XPj...ew?usp=sharing

Best,
Jacek

[CK76]

FYI - You can't use structured table reference on closed workbook. Named range is suitable workaround.

[jaryszek]

Hi, thank you.

CK76 but named ranges with refering to range address not to structure tables?

Or i can workaround it?

Problem is that my closed workbook is genereted automatically from R.
And it would be good to use Indirect formula to create named range referencing to structure table...

Best,
Jacek

[CK76]

1. You can't use INDIRECT on closed workbook. There are UDF that workaround it, but has limitations of it's own.

2. You have Excel 2016... why not use Get&Transform to bring in data from closed workbook. Load to data model. And use PivotTable or other means to summarize data?

I've never used R to create workbook (usually use it to read content, or work with dataframe in PowerBI), are you able to create named range at the time of workbook creation?

[jaryszek]

Hi CK76,

thank you.
With power pivot it would be ok but main workbook is big and loading more data it is somethiong what i want to avoid.

Yes in R you can create workbook and dynamic table so maybe there is possibility to get table address and create named range with this.

Best,
Jacek

[jaryszek]

Hi,

i tried with:

Please Login or Register  to view this content.

and this is working. So you cna refert o named ranges.

How to create this string dynamically?

"'C:\Users\admin\Desktop\Azure Workbook\[AzureSubscriptionSpecific.xlsx]" ?

When i am adding quotes i have reference error.
Please help,
Jacek

[jaryszek]

Please help,

Jacek

[CK76]

PQ and data model handles millions of rows of data without issue. I typically deal with 1~2mil rows of data with 15 or so columns.

As for your issue with generating string dynamically. Where and how are you trying to use it? It would help to see your code to pinpoint the cause.

[jaryszek]

hi Ck76,

do you mean that you can use power query and not load data, only read them to workbook?

In workbook you can have ~1 mil of rows.

Best,
Jacek

[CK76]

Data model stores data in memory. You only use that model to load data to PivotTable.

Pivot table is aggregated/summarized report, so raw data is transformed into meaningful group/sets.

Edit: Oh and instead of doing VLOOKUP, you can easily load both table into PQ and perform join operation.

[jaryszek]

I have single cell and when user is changing value in other cell,
the single cell is running using vlookup.

I can not load PQ source data into my main workbook, i can store it in memory and somehow vlookup through it.
Maybe using join for one cell.

But how to refresh cell after join is performed.
How to trigger PQ to work after changing value by user?

Best,
Jacek

[CK76]

It is quite simple really.

Assign name (named range) to where the user enters value. I assume this is single value.

In PQ's Advanced Editor. You can access named range like below. Where mVal will be the variable holding the returned value.

Please Login or Register  to view this content.

You can then use this variable to either join/filter the other table. You can then load the result to Excel Table or to data model.
When you refresh the table/query, it will update with new result.

See attached for sample use to filter table based on variable supplied by user.

[jaryszek]

thank you.

hmm nice solution.
But loading data from PQ it is to table.
Not to single cell.
PQ is slow, demands to trigger refreshing table with VBA and furthermore, Excel 2010 has not in as normal feature.
You have to install PQ as add-in.
To many disadvantages.

I solved the problem using string text and vba.

First of all i created reference to closed workbook like:

Please Login or Register  to view this content.

and next simple replaced text between "'" signs for current workbook path.

And this is working while workbook is opened.

And while R is outputing data i am creating dynamic table and named range with table address.

Best,
Jacek