Indirect function and relative references

[RonM]

Is there a means of creating a relative reference to cells using the
Indirect function?

For instance I have a series of sheets for different countries and a
summary sheet that I want to populate from the same cell on the sheet
for a single country say cells C10:N15. Sheets are named Territory1,
Territory 2 etc

If on the summary sheet cell, A3 (named "TerritoryIn") the territory
whose values I want is entered say Territory 3 then I want to the
value in cell C10 to be 'Territory 3'!

I have seen an example posted on this group using Rows($1:n) to
identify the row viz. =INDIRECT("$A$3!C"&ROWS($1:10)) but when I try
to used "COLUMNS" to identify the colun reference I get a reference
error.

Can someone help?

Thanks
Ron

 

[Pete_UK]

Try this in C10:

=INDIRECT($A$3&"!"&CHAR(COLUMN(C1)+64)&ROW(A10))

You can then copy this across to N10, and then copy C10:N10 down to
row 15. The C1 and A10 parameters in the formula will change as you
copy the formula because they are outside the quotes.

Hope this helps.

Pete

 

[Pete_UK]

Hi Ron,

Well, you only said up to column N originally !! <bg>

Try this instead in C10:

=INDIRECT($A$3&"!"&ADDRESS(ROW(C10),COLUMN(C10)))

then copy it across to your heart's content.

If you have blanks in any of those cells they will appear as 0. To
overcome that you can have:

=IF(INDIRECT($A$3&"!"&ADDRESS(ROW(C10),COLUMN(C10)))="", "",INDIRECT($A
$3&"!"&ADDRESS(ROW(C10),COLUMN(C10))))

Again, this starts in C10 (or you can change C10 in the formula to the
cell where your formula is) and then can be copied across and down as
required.

Hope this helps (and please reply here rather than directly),

Pete

Hi Pete,

Thanks, that was helpful.... except when I wanted to extend my
selection beyond column AA where a "[" is returmed for
CHAR(COLUMN(AA1)+64)! .... any further suggestions?

Regards
Ron

Try this in C10:

=INDIRECT($A$3&"!"&CHAR(COLUMN(C1)+64)&ROW(A10))

You can then copy this across to N10, and then copy C10:N10 down to
row 15. The C1 and A10 parameters in the formula will change as you
copy the formula because they are outside the quotes.

Hope this helps.

Pete

Is there a means of creating a relative reference to cells using the
Indirect function?

For instance I have a series of sheets for different countries and a
summary sheet that I want to populate from the same cell on the sheet
for a single country say cells C10:N15. Sheets are named Territory1,
Territory 2 etc

If on the summary sheet cell, A3 (named "TerritoryIn") the territory
whose values I want is entered say Territory 3 then I want to the
value in cell C10 to be 'Territory 3'!

I have seen an example posted on this group using Rows($1:n) to
identify the row  viz. =INDIRECT("$A$3!C"&ROWS($1:10)) but when I try
to used "COLUMNS" to identify the colun reference I get a reference
error.

Can someone help?

Thanks
Ron- Hide quoted text -

- Show quoted text -

 

[Dave Peterson]

First, it's never a bad idea to include apostrophes around the sheet name.

Excel won't mind if they are there and you don't need them, but it will get very
upset if you don't have them and you need them.

And =indirect() has an option to use RC reference style.

I'd try something like:
=INDIRECT("'" & $A$3 & "'!R"&ROW()&"c"&COLUMN(),FALSE)

But I'm afraid I don't understand what the offset's should be.

======
Second, you may want to consider using a different formula (like =vlookup() or
=index(match()).

You'll have to make sure there's enough unique information to match on, but
these kinds of formulas are much more robust -- especially if you change that
table (insert/delete a row/column or even sort the data).

 

[Dave Peterson]

RC reference style is really R1C1 reference style (if you're looking up what
that means in Excel's help).