Need to increment columns in a formula while dragging down...not across

[haganator]

Greetings all,

Is it possible to have a formula, that references a table in another worksheet, increment the column by dragging down? The formula is =MIN(SFLD_Stats!B$3:B$26), and I need to drag it down multiple cells so that it increments to =MIN(SFLD_Stats!C$3:C$26), and so forth. The row range is always 3:26, the column is the only thing that changes. I know that if I drag a formula down, the rows will update and if I drag across, the columns will update, but I need the opposite. I saw a way to use INDEX to do a swap like this for data points, but I don't know how to use it with a formula.

Any help would be greatly appreciated.

Thanks,

Jeff

 

[par60056]

I can think of 3 ways to do it. None is what I would call elegant.

1) drag copy the formula horizontally, select the cells, copy, pastespecial transpose, clear the horizontal cells.
2) try a formula like =MIN(INDIRECT("Sheet1!" & ADDRESS(2,CELL("row",B3)-1) & ":" & ADDRESS(26,CELL("row",B3)-1)))
the formula assumes B3 is the cell the formula is in. So when you copy it down it becomes C3, D3, ...
3) write a macro to insert each formula

I hope somebody comes up with something better.

 

[ukmikeb]

Hi

Try :-

=MIN(INDIRECT(ADDRESS(3,ROWS($1:2),2,,"SFLD_Stats")&":"&ADDRESS(26,ROWS($1:2),2)&""))

and drag down

hth

 

[haganator]

Par60056,

My apologies for not replying sooner...#2 worked. Thank you so much. I was debating on whether or not to swap rows and columns in my data tables, but that would've required a macro too. LOL. Kind of painted myself in a corner on this one.

Thanks again,

Jeff

I can think of 3 ways to do it. None is what I would call elegant.

1) drag copy the formula horizontally, select the cells, copy, pastespecial transpose, clear the horizontal cells.
2) try a formula like =MIN(INDIRECT("Sheet1!" & ADDRESS(2,CELL("row",B3)-1) & ":" & ADDRESS(26,CELL("row",B3)-1)))
the formula assumes B3 is the cell the formula is in. So when you copy it down it becomes C3, D3, ...
3) write a macro to insert each formula

I hope somebody comes up with something better.

 

[haganator]

ukmikeb,

Your solution is similar to #2 from the previous post. Yours worked like a champ...it took me a while to figure out how to step through it and be able to follow where I was in my table, but now it's a snap.

Thanks again,

Jeff

Hi

Try :-

=MIN(INDIRECT(ADDRESS(3,ROWS($1:2),2,,"SFLD_Stats")&":"&ADDRESS(26,ROWS($1:2),2)&""))

and drag down

hth

 

[ukmikeb]

Jeff

Thanks for the feedback.

Good luck with your project.

 

[Jonmo1]

Glad you have a solution, but just wanted to add some value..

I was debating on whether or not to swap rows and columns in my data tables, but that would've required a macro too.

It's actually very easy to do that (without a macro)

Highlight and copy the entire range
Now go to an available area and Right Click - Paste Special - Transpose.

 

[haganator]

Jonmo1, This works really well. I'll have to play around with this to get used to it. Thanks for the info. Jeff

Glad you have a solution, but just wanted to add some value.. It's actually very easy to do that (without a macro) Highlight and copy the entire range Now go to an available area and Right Click - Paste Special - Transpose.