Forum Discussion

Copper Contributor

Dec 14, 2022

"Key Cell" Text reference supporting fill down/across, copy paste formula

Referring to the pic - Is there a formula for cell H4 (where I have that Indirect() currently) that can be copied down and across and will fill the table with the range that starts from the 'key cell...

excel

Excel on Mac

Formulas and Functions

office 365

yennek

Copper Contributor

Dec 14, 2022

Harun24HR >>Why not A3:E6? Why not A3:D6…<<

It won’t always be a fixed shape. Sometimes A3:E6 other times A3:D6 still other times maybe A8 to F20 or something else. Which is why I was hoping for a formula that would start with that key cell and could be Filled Down and Filled Across to return what the user needed. (Your function did work, but returning the fixed size range doesn’t work in this particular instance.) And thank you for your the efforts!

Harun24HR

Bronze Contributor

Dec 14, 2022

Yes, I was trying to understand how to define end cell. Do you have any logic to understand excel to define end cell rather than fixed shape?

yennek
Copper Contributor
Dec 15, 2022
yeah, the logic is somewhat complex, updated regularly, and goes across multiple sheets, but it’s an easy thing for the users to manage in their head - another reason I was trying for the fill down and right approach. But, I can eventually pull out the logic and use the formula you provided for the range. (Formulas that return arrays are difficult for most end users.)