Create Correct Formula

  • Thread starter Thread starter Kevin H. Stecyk
  • Start date Start date
K

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
 
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
Click to expand...

In A1, replace:

"O10"

With something like:

ADDRESS(10,COLUMN(O1),4)
--ron
 
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.
 
"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.
Click to expand...

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
 
Hi Ron,

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

Best regards,
Kevin
 
Hi Ron,

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

Best regards,
Kevin
Click to expand...

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
 
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).
Click to expand...

Great, thank you Ron!
 
Ron Rosenfeld wrote

Great, thank you Ron!
Click to expand...

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
 
Ron Rosenfeld said:
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
Click to expand...

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