Dragging a formula to increase by 2

[sdoppke]

Hi everyone, I think my title needs some work.

I have this formula

=scatterchart(Sparklines!B27:C28,,ScatterPlot!$M$2,ScatterPlot!$M$3,ScatterPlot!$N$2,ScatterPlot!$N$3,ScatterPlot!$U$11,ScatterPlot!$T$11)

and wish to drag it down a column and have "Sparklines!B27:C28" increase by 2 instead of 1. ie the next would look like:

=scatterchart(Sparklines!B29:C30,,ScatterPlot!$M$2,ScatterPlot!$M$3,ScatterPlot!$N$2,ScatterPlot!$N$3,ScatterPlot!$U$11,ScatterPlot!$T$11)

and so on...

Thanks in advance for any help.

sd

 

[sdoppke]

Hi everyone, I think my title needs some work.

I have this formula

=scatterchart(Sparklines!B27:C28,,ScatterPlot!$M$2,ScatterPlot!$M$3,ScatterPlot!$N$2,ScatterPlot!$N$3,ScatterPlot!$U$11,ScatterPlot!$T$11)

and wish to drag it down a column and have "Sparklines!B27:C28" increase by 2 instead of 1. ie the next would look like:

=scatterchart(Sparklines!B29:C30,,ScatterPlot!$M$2,ScatterPlot!$M$3,ScatterPlot!$N$2,ScatterPlot!$N$3,ScatterPlot!$U$11,ScatterPlot!$T$11)

and so on...

Thanks in advance for any help.

sd

Morning everyone, does anyone have an idea or trick to be able to do this? thanks for any help.

sd

 

[Cindy Ellis]

I can think of at least one way that would use indirect(), but it would require knowing the relative positions of your formulas to the rows in the sparklines sheet.
Short of that, depending on what else is on the worksheet, you could try this (in a copy of your worksheet, in case it doesn't work well). This is a workaround, not really a complete solution, but it's how I do this kind of thing when I'm in a hurry.
Starting in the row where you have your first scatterchart formula, in an unused column, enter

=iseven(row())

Drag this down to cover twice as many rows as you want in the end.
Autofilter on TRUE or FALSE (whichever one leaves your first formula visible). Every other row is now hidden.
Drag your formula down the the last row of the true/false.
Swap the filter to show the blank rows, and delete all the blank rows, and turn off the autofilter.
Your formulas SHOULD stay pointing to every other row in your sparklines sheet.
A bit messy as a workaround, but less tedious than editing the references inside the formulas.
Hope that helps,
Cindy

 

[jrw1]

sd:

In theory, replacing your "Sparklines!B27:C28" as the first argument with the following formula should work:

=INDIRECT("Sparklines!"&(ADDRESS(ROW(B27)+(ROWS($B$27:B27)-1),COLUMN($B27),2))&":"&(ADDRESS(ROW(C28)+(ROWS($C$28:C28)-1),COLUMN($C$28),2)))

It adds an incrementer as the formula is dragged down. However, if you run formula evaluator (F9) you will notice that it returns a #value error. I'm not sure why this is (I've had trouble with INDIRECT (ADDRESS) in the past), so hopefully someone else could shed some light on it.

K

 

[sdoppke]

I can think of at least one way that would use indirect(), but it would require knowing the relative positions of your formulas to the rows in the sparklines sheet.
Short of that, depending on what else is on the worksheet, you could try this (in a copy of your worksheet, in case it doesn't work well). This is a workaround, not really a complete solution, but it's how I do this kind of thing when I'm in a hurry.
Starting in the row where you have your first scatterchart formula, in an unused column, enter

=iseven(row())

Drag this down to cover twice as many rows as you want in the end.
Autofilter on TRUE or FALSE (whichever one leaves your first formula visible). Every other row is now hidden.
Drag your formula down the the last row of the true/false.
Swap the filter to show the blank rows, and delete all the blank rows, and turn off the autofilter.
Your formulas SHOULD stay pointing to every other row in your sparklines sheet.
A bit messy as a workaround, but less tedious than editing the references inside the formulas.
Hope that helps,
Cindy

Thank you so much for the reply. I will try that now, is there a way to delete all the rows with code, mine tota 3780?

thanks again for the help

sd

 

[sdoppke]

sd:

In theory, replacing your "Sparklines!B27:C28" as the first argument with the following formula should work:

=INDIRECT("Sparklines!"&(ADDRESS(ROW(B27)+(ROWS($B$27:B27)-1),COLUMN($B27),2))&":"&(ADDRESS(ROW(C28)+(ROWS($C$28:C28)-1),COLUMN($C$28),2)))

It adds an incrementer as the formula is dragged down. However, if you run formula evaluator (F9) you will notice that it returns a #value error. I'm not sure why this is (I've had trouble with INDIRECT (ADDRESS) in the past), so hopefully someone else could shed some light on it.

K

Thankyou so much for the reply. scatterchart is a sparlines class module, maybe that is the error?

Im not quite sure what the entire formula would look like. would you be able to use the one above and tie in your example?

thanks for taking the time

sd

 

[Cindy Ellis]

If this is a one-time deal, it will probably be easier to not use code, but instead, with just the blank rows showing, highlight the first of those rows, scroll down to the end of the filtered rows, press Shift, select the last row, and select Delete rows.
In code, I'm not sure how to just delete the visible rows, although I know there's a way.
Cindy

 

[sdoppke]

I can think of at least one way that would use indirect(), but it would require knowing the relative positions of your formulas to the rows in the sparklines sheet.
Short of that, depending on what else is on the worksheet, you could try this (in a copy of your worksheet, in case it doesn't work well). This is a workaround, not really a complete solution, but it's how I do this kind of thing when I'm in a hurry.
Starting in the row where you have your first scatterchart formula, in an unused column, enter

=iseven(row())

Drag this down to cover twice as many rows as you want in the end.
Autofilter on TRUE or FALSE (whichever one leaves your first formula visible). Every other row is now hidden.
Drag your formula down the the last row of the true/false.
Swap the filter to show the blank rows, and delete all the blank rows, and turn off the autofilter.
Your formulas SHOULD stay pointing to every other row in your sparklines sheet.
A bit messy as a workaround, but less tedious than editing the references inside the formulas.
Hope that helps,
Cindy

Sorry Cindy i realized what a stupid question that was. That worked great!!!!

sd

 

[jrw1]

Thankyou so much for the reply. scatterchart is a sparlines class module, maybe that is the error?

Im not quite sure what the entire formula would look like. would you be able to use the one above and tie in your example?

thanks for taking the time

sd

sd -


The issue revolves more around using INDIRECT with ADDRESS, I don't think it really has any issue with what the data is actually referring to...

 

[Cindy Ellis]

That worked great!!!!

Thanks for the feedback! Glad I could help.