Forum Discussion
"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
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))
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!
- 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?
