INDIRECT(ADDRESS... Across worksheets

M

MikeDH

I'm looking for a way to refer the Indirect(Address(... functions to yield a
result from a cell in a different page of my spreadsheet. Any help?
 
H

Harlan Grove

MikeDH wrote...
I'm looking for a way to refer the Indirect(Address(... functions to yield a
result from a cell in a different page of my spreadsheet. Any help?
Click to expand...

Very little help possible since you haven't provided sufficient
details. If you had worksheet names in B2:D2 and wanted the values of
cell X99 for each of these worksheets, you'd be better off not using
ADDRESS but

=INDIRECT("'"&B2&"'!X99")

Even if you have row and column indices, you're be better off using

=INDIRECT("'"&B2&"!R"&99&"C"&24,0)

If you mean something other than this, it's up to you to provide the
necessary details.
 
M

MikeDH

Gomen nasai, sir. I had posted this question once before with full details -

I need to have a column auto-updating from every-other column of a row on a
different worksheet - literal example:

'ValleyResultsVsBadDay' column A is identical to Column (B,D,F,H,J,L, etc. -
all the even-indexed ones) Row 5 of 'ValleyLots'.

Is there a way to use Indirect and Address to refer to a different worksheet
like that, or is there a simpler, quicker way to make a function for that
task?
-
and received no response. So I decided not to look for full help like that
and was looking for more general hints on the indirect funtion - namely, how
to put in the sheet's name. I understand it now and appreciate the help.

As for address, I need to make the formula updating forever, so it serves my
purpose; in addition, I'm using it because I can cut down on the number of
cheat-columns I have blacked out in my document which - to me - looks a
little more professional. Thanks again.
 
H

Harlan Grove

MikeDH wrote...
....
I need to have a column auto-updating from every-other column of a row on a
different worksheet - literal example:

'ValleyResultsVsBadDay' column A is identical to Column (B,D,F,H,J,L, etc. -
all the even-indexed ones) Row 5 of 'ValleyLots'.
Click to expand...
....

If the first formula would be in ValleyResultsVsBadDay!A2 (modify as
needed), use

A2:
=OFFSET(ValleyLots!$B$5,0,2*(ROWS(A$2:A2)-1))

Select A2 and fill down as needed. There's no need for either INDIRECT
or ADDRESS.

If the worksheet would also vary, consider

=OFFSET(INDIRECT("'"&WorksheetNameHere&"'!A5"),0,2*ROWS(A$2:A2)-1)

or

=INDIRECT("'"&WorksheetNameHere&"'!R5C"&(2*ROWS(A$2:A2)),0)
As for address, I need to make the formula updating forever, so it serves my
Click to expand...
....

My point is that there's NEVER a need to use INDIRECT(ADDRESS(..)).
Anything you could accomplish with INDIRECT(ADDRESS(..)) could be
accomplished using either OFFSET or INDIRECT with R1C1 references with
one fewer level of nested function calls.
 
Top