excel link table range to value

0
votes

I'm searching for a way to link the range of an Excel table to a specific number.

Let's say I have a table that has a range of A1:B10. I want to make the number '10' dynamic and link it to a number that is calculated from another sheet. Let's say cell F1 contains that number and the value is '20'. Is it possible to make a dynamic range so the table range changes to: A1:B(F1). Then my table would adapt automatically and this has to work in my file.

I prefer a formula instead of a macro in VBA, because I normally don't work with VBA.

2
excelvbaexcel-formula
Use the INDEX or INDIRECT formula. - SJR
That said, I'm not sure how you define a table dynamically without VBA. - SJR

2 Answers

4
votes

An alternative is to use a dynamic named range which is what we used to do before tables. The range, enclosed by marching ants below, is defined as follows:

=OFFSET(Sheet3!$A$1,0,0,Sheet3!$F$1,5)

The top left cell is A1. It is as many rows deep as the number in F1 and 5 columns wide (which could also be made dynamic).

enter image description here

4
votes

Yes, INDIRECT is your way to make any formula dynamic / dependent on other cell values.

Say you want to sum range A2:C2. But the value 2 for C is located in Cell G2.

=SUM(INDIRECT("A2"&":C"&G2))

this is equal to write =SUM(A2:C2) in my example below.

enter image description here

If you set G2 to 3 it would calculate A2:C3 for this =SUM(INDIRECT("A2"&":C"&G2)).

If you mean Excel table, I guess the answer is no with formula, probably with VBA.