Create Correct Formula

[Kevin H. Stecyk]

Hi,

I currently have the following formula that I want to change slightly:

=INDIRECT("'["&xsBookName&"]"&xsHighSheetN&"'!"&"O10")

xsBookName is the name of a workbook.

xsHighSheetN is the name a worksheet for the high case scenario

I'd like to change the formula such that the cell reference at the end "O10"
is more flexible. Ideally, I'd like to copy this formula and paste it over
a range of cells. I'd like "O10" to update to the correct reference.

In other words, if I pasted the formula in A1 through C1 of Sheet1 of a
NewBook, then

B1 would reference: xsBookName, xsHighSheet and cell P10
C1 would reference the same as above except Q10
A2 would reference the same as above except O11
and so on...

Your help would be greatly appreciated.

Best regards,
Kevin

 

[Ron Rosenfeld]

Hi,

I currently have the following formula that I want to change slightly:

=INDIRECT("'["&xsBookName&"]"&xsHighSheetN&"'!"&"O10")

xsBookName is the name of a workbook.

xsHighSheetN is the name a worksheet for the high case scenario

I'd like to change the formula such that the cell reference at the end "O10"
is more flexible. Ideally, I'd like to copy this formula and paste it over
a range of cells. I'd like "O10" to update to the correct reference.

In other words, if I pasted the formula in A1 through C1 of Sheet1 of a
NewBook, then

B1 would reference: xsBookName, xsHighSheet and cell P10
C1 would reference the same as above except Q10
A2 would reference the same as above except O11
and so on...

Your help would be greatly appreciated.

Best regards,
Kevin

In A1, replace:

"O10"

With something like:

ADDRESS(10,COLUMN(O1),4)
--ron

 

[T. Valko]

One way...

Replace "O10" with:

ADDRESS(ROWS(A$1:A10),COLUMNS($A1:O1))

That expression returns the *text* string $O$10.

As you copy across the column will increment: $O$10, $P$10, $Q$10, $R$10
etc.

As you copy down the row will increment: $O$10, $O$11, $O$12, $O$13 etc.

 

[Kevin H. Stecyk]

"T. Valko" wrote in message

One way...

Replace "O10" with:

ADDRESS(ROWS(A$1:A10),COLUMNS($A1:O1))

That expression returns the *text* string $O$10.

As you copy across the column will increment: $O$10, $P$10, $Q$10, $R$10
etc.

As you copy down the row will increment: $O$10, $O$11, $O$12, $O$13 etc.

Biff,

That's exactly what I was looking for. It took me a couple of minutes to
understand the solution. Thank you very much for your help.

Best regards,
Kevin

 

[Kevin H. Stecyk]

Hi Ron,

Thank you for your help. I used Biff's solution, which is listed after
yours.

Best regards,
Kevin

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Ron Rosenfeld]

Hi Ron,

Thank you for your help. I used Biff's solution, which is listed after
yours.

Best regards,
Kevin

Glad you have a solution. Just to explain a bit further:

Both do the same thing, making use of the ADDRESS function along with COLUMN or
COLUMNS to generate a column number.

Both will increment automatically as you drag across.

I hardcoded the row argument as 10 since you did not write that you wanted to
auto-increment the rows also. If you do, just change the 10 to a ROW(A10).

--ron

 

[Kevin H. Stecyk]

Ron Rosenfeld wrote

Glad you have a solution. Just to explain a bit further:

Both do the same thing, making use of the ADDRESS function along with
COLUMN or
COLUMNS to generate a column number.

Both will increment automatically as you drag across.

I hardcoded the row argument as 10 since you did not write that you wanted
to
auto-increment the rows also. If you do, just change the 10 to a
ROW(A10).

Great, thank you Ron!

 

[Ron Rosenfeld]

Ron Rosenfeld wrote

Great, thank you Ron!

Actually, to make it even more "intuitive", since you are starting with O10,
you could code your initial ADDRESS function as:

ADDRESS(ROW(O10),COLUMN(O10))

This way the ADDRESS arguments will change as you fill down or right to reflect
the cell to which the INDIRECT argument will be referring.
--ron

 

[T. Valko]

Actually, to make it even more "intuitive", since you are starting with
O10,
you could code your initial ADDRESS function as:

ADDRESS(ROW(O10),COLUMN(O10))

This way the ADDRESS arguments will change as you fill down or right to
reflect
the cell to which the INDIRECT argument will be referring.
--ron

That's vulnerable to row/column insertions in the sheet where the formula
resides. That's why I always use ROWS/COLUMNS.

 

[Ron Rosenfeld]

That's vulnerable to row/column insertions in the sheet where the formula
resides. That's why I always use ROWS/COLUMNS.

Good point.
--ron