Create a Dynamic Range within a Formula for External Tabular Data

[kujoking7]

Hello,

I ran into a bit of an issue pulling external tabular data into a worksheet. I have a very large formula with a bunch of conditionals that pulls from an external table. When I set up the data with both workbooks open, Excel defaulted by using the table column name rather than the physical range I was looking that. I soon found that tabular references cannot be accessed without having both workbooks open at the same time, so I made the range static (for example, $A$1:$A$5000 for one of the conditions in the formula).

The problem that I have is that the data table that I'm pulling from is a dynamic table, so last week there were 5000 rows, now there are, let's say, 5050, so my formulas no longer capture that data without manually changing the formula.

I was wondering if there was a way to change the array in the formulas while using a dynamic endpoint to the data. In essence, what I would have is something like: 'C:\[FileName.xlsm]Sheet1'!$A$1formula to get final data point in set, $A$5050). I already have a formula to get the final row number, but haven't been able to functionally get it inside a formula. Any help is appreciated!

[sandy666]

Maybe read about
Power Query for

• Excel 2010 Pro Plus or Excel 2013 Pro Plus can be downloaded as free add-in from MS site (choose correct version). I suggest Power Pivot also
• Excel 2016 or Excel 365 is built-in, aka Get&Transform
• PowerQuery works with M-language and does not contain any classic formulae or vba code
• What is PowerQuery? see here: Getting Started with PowerQuery (Get&Transform) in Excel

[FlameRetired]

Or perhaps a (some) dynamic named ranges ... done by formula.

See the formula in D2 below. Copy and paste that in Name Manager and give it a name (ex: Array
The range will size itself to the date column dynamically. The columns will be static. If columns need to be dynamic as well let me know.

I could be wrong, but I think this requires workbook to be open.

	A	B	C	D
1	Date	Name	State	Array
2	5/13/2018	Bob	Washington	=$A$2:$C$2:INDEX($A:$A,MATCH(1E+306,$A:$A,1))
3	6/2/2018	John	West Virginia
4	6/4/2018	Mike	Wisconsin
5	6/12/2018	Jacob	Wyoming
6	6/14/2018	Kelly	Alabama
7	6/23/2018	Martha	Alaska
8	6/27/2018	Joe	Arizona
9	6/30/2018	Rachel	Arkansas
10	7/7/2018	Liz	California
11	7/8/2018	Elsie	Colorado
12	7/9/2018	JoAnne	Connecticut
13	7/13/2018	Allen	Delaware
14	7/18/2018	Vince	Florida
15	7/22/2018	Steve	Georgia
16	7/25/2018	Susan	Hawaii
17	7/27/2018	Sandra	Idaho
18	7/28/2018	Phyllis	Illinois
19	7/29/2018	Paul	Indiana
20	8/1/2018	Alice	Iowa
21	8/2/2018	Linda	Kansas

[kujoking7]

Thank you both for your responses. I will reply to them in this post:

Sandy, data set that I'm pulling data from is already queried by a PowerQuery and is going to be a template for other users, so I'm trying to avoid the use of PowerQuery again.

Dave, could you explain your solution a bit? I'm just wondering because not only am I looking into the data set, but I'm also performing conditionals for it. I'm fine with creating named ranges, but just curious how it would work given the formula I'm working with. See below the formula:

=SUMPRODUCT(--(ISNUMBER(SEARCH($A$2,'[Metrics.xlsm]Report'!$Y$2:$Y$8094))),--('[Metrics.xlsm]Report'!$V$2:$V$8094 = $A$3),--(ISNUMBER(SEARCH($A$5, '[Metrics.xlsm]Report'!$C$2:$C$8094))),--('[Metrics.xlsm]Report'!$BY$2:$BY$8094<='[Metrics.xlsm]Report'!$BX$2:$BX$8094),--(MONTH('[Metrics.xlsm]Report'!$BX$2:$BX$8094) = MONTH(DATEVALUE(B$3 & " 1"))),--('[Metrics.xlsm]Report'!$BZ$2:$BZ$8094 = $B$2)) + SUMPRODUCT(--('[Metrics.xlsm]Report'!$K$2:$K$8094 = $A$3), --(ISNUMBER(SEARCH($A$5,'[Metrics.xlsm]Report'!$C$2:$C$8094))),--('[Metrics.xlsm]Report'!$BY$2:$BY$8094<='[Metrics.xlsm]Report'!$BX$2:$BX$8094),--(MONTH('[Metrics.xlsm]Report'!$BX$2:$BX$8094) = MONTH(DATEVALUE(B$3 & " 1"))),--('[Metrics.xlsm]Report'!$BZ$2:$BZ$8094 = $B$2))

There's a lot in there comparing dates, data points, names, etc.

[sandy666]

Sandy, data set that I'm pulling data from is already queried by a PowerQuery and is going to be a template for other users, so I'm trying to avoid the use of PowerQuery again

You didn't say that in your first post

[kujoking7]

After all day exploring different solutions, I've found that the central solution that would work would be using an INDIRECT function. However, since the source workbook is closed, this solution does not work.

I am aware of the INDIRECT.ext and other downloadable add-ins, but I am unable to go that route. I will mark this post as solved and move to the VBA programming forum to attempt to find a VBA solution.