On a sheet, I have 35 cells that use B14:B22 as parameter values, and another 6 cells each that have D14:D22 through I14:I22.
However, I add new data now and then, and this increases the range I need. It would be a real bummer to have to update all instances of 22 to 23, and then again at some point from 23 to 24. Since I'm adding the new data to end of the list rather than inserting it, Excel doesn't automatically update the arrays.
I had an idea but I haven't been able to figure out how to implement it exactly. I have a cell J14 that contains the number of rows of data for B14 through B-whatever. What I want is basically to have all the previous instances of B14:B22 changed to B14:B(13+J14), but that syntax doesn't work. But I think you can see what I'm trying to do. How would I go about doing this?
Bookmarks