Forum Discussion

yennek's avatar
yennek
Copper Contributor
Dec 15, 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' the user has entered -- in this case the user has entered A3, so I'd like the table filled with the range A3:D5? The idea is to broadly support the copy and paste of that table anywhere on the sheet and the user can just enter -any- cell and it will pull the range starting from that key (I've been working with Indirect, Offset, Cell, etc., can't get it. 

 

 

Hoping for a regular fill down, fill across, no array or absolute references since the starting point and the range of data to retrieve will change.

4 Replies

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor

    User enter A3 and want to return A3:D5, well. Why not A3:E6? Why not A3:D6? How excel will know starting cell and ending cell? Any explanation? With you current sample you may try =INDIRECT(H3&":D" &ROW(A2)+ RIGHT(H3,LEN(H3)-1))

    • yennek's avatar
      yennek
      Copper Contributor

      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's avatar
        Harun24HR
        Bronze Contributor
        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?

Resources