INDIRECT Vertical Auto-Fill

[stuartjohnwood]

Hi, I am trying to consolidate data from the same column of multiple spreadsheets.

=INDIRECT("'"&A$1&"'!"&"I14")

A$1 is the first worksheet name with other worksheet names in the subsequent columns to the right. I14 is the first cell where the data begins in each worksheet. My formula above is in cell A2. Therefore, when I drag horizontally, it returns the data in cell I14 for each worksheet listed in Row 1.

However, when I drag vertically, the reference to I14 does not change with auto-fill and I need this to be dynamic i.e. I14, I15, I16 etc.

Can anyone help me understand why this won't work and support with a solution?

 

[GuzmanServices]

Please try the following modified formula, which alludes to a helper Column B with values commencing with 14 on Cell B1 and increasing with each subsequent Row.

=INDIRECT("'A$1'!"&"I"&B1)

 

[dreid1011]

Perhaps this:

=INDIRECT("'"&A$1&"'!"&"I"&ROW($I14))

 

[stuartjohnwood]

Please try the following modified formula, which alludes to a helper Column B with values commencing with 14 on Cell B1 and increasing with each subsequent Row.

=INDIRECT("'A$1'!"&"I"&B1)

Works perfect - thanks.

 

[stuartjohnwood]

Perhaps this:

=INDIRECT("'"&A$1&"'!"&"I"&ROW($I14))

Also works perfect - thanks