Simplifying a dynamic range formula

[amartino44]

Hello. Someone helped me with the great formula to get a range to be dynamic. The problem is that the range only deals with one cell at a time. This would be ok if my initial range had only a few cells but it has a lot more (about 50 non-adjacent cells) Is there any way to get around repeating the snippet of formula for each cell? This is what the formula is =Sheet1!$B$2:INDEX(Sheet1!$B$2:$G$2,,COUNTA(Sheet1!$B$2:$G$2)). So that just refers to row 2, but I have a lot more rows and the formula would be ginormous like Kardashian's behind or Kate Upton's mother melons.

[RobertMika]

What are you trying to achive by:

Is there any way to get around repeating the snippet of formula for each cell

Can you post a sample?

[amartino44]

That formula in my first example would just make cell B2 dynamic. That is just one cell. If my named range is larger than one cell, I will have to copy that formula for each cell and combine it into one enormous formula. I would rather not do this. For example =Sheet1!$B$2:INDEX(Sheet1!$B$2:$G$2,,COUNTA(Sheet1!$B$2:$G$2)) would be just for one cell. If I wanted a second cell it would be =Sheet1!$B$2:INDEX(Sheet1!$B$2:$G$2,,COUNTA(Sheet1!$B$2:$G$2)),Sheet1!$B$3:INDEX(Sheet1!$B$3:$G$3,,COUNTA(Sheet1!$B$3:$G$3)) If I have a 100 cells, that would be the world's largest formula. Is there a way around this?

[Ace_XL]

Have you tried

=Sheet1!$B$2:INDEX(Sheet1!$B$2:$G$2,2,COUNTA(Sheet1!$B$2:$G$2))

where the highlighted portion indicates number of rows i.e. (Row 2 and Row 3)

[amartino44]

The rows are non-contiguous. I don't see how that formula would help.

[RobertMika]

That formula in my first example would just make cell B2 dynamic.

That formula will return a range depends on number of cell from COUNTA
So if COUNTA is 2 the range from
=Sheet1!$B$2:INDEX(Sheet1!$B$2:$G$2,,COUNTA(Sheet1!$B$2:$G$2)
will be $B$2:$C$2.
on its own does not do much but can be used in futher calculation.