[RESOLVED] [EXCEL] Changing the Modulus Range Using CONCATENATE()

[Vanquish2888]

Hey Everybody!!

Its been awhile since I've posted and I have come across an interesting question...

I want to save the user some time AFTER the macro has finished running. The modulus range (the range selected in the SeriesCollection) of the graph is determined by the user once the macro has finished running, then they must change a place on the macro to same range in cells that use the SLOPE() & INTERCEPT() functions. It is rather time consuming & frustrating to change the range inside the cell. I want the user just to change the LOWER cell & UPPER cell of the range in a specified cell and have the others adjust based on this. I was thinking of using the CONCATENATE() function, however I have had no success. I have attached a picture below.

Thank you in advance, please ask questions if I am not making myself clear.

~AJ

[anhn]

Not sure what are the addresses of cells in used, I supose that: cell F20 = 80, cell F21 = 350

You can use OFFSET() or INDIRECT() to have formulas like this:

=SLOPE(OFFSET(Q1, F20-1, 0, F21-F20+1, 1), OFFSET(K1, F20-1, 0, F21-F20+1, 1))

=INTERCEPT(OFFSET(Q1, F20-1, 0, F21-F20+1, 1), OFFSET(K1, F20-1, 0, F21-F20+1, 1))

or

=SLOPE(INDIRECT("Q" & F20 & ":Q" & F21), INDIRECT("K" & F20 & ":K" & F21) )

=INTERCEPT(INDIRECT("Q" & F20 & ":Q" & F21), INDIRECT("K" & F20 & ":K" & F21) )

Replace F20 and F21 with your actual cells that store 80 and 350. You may see to use INDIRECT() is easier to understand.

[Vanquish2888]

Thanks for the help koolsid & anhn! I appreciate it. Anhn's method is what I was looking for, since I don't want to add any code to the workbook. Thanks again!!

~AJ