"indirect" formula reference

  • Thread starter Thread starter ORLANDO VAZQUEZ
  • Start date Start date
O

ORLANDO VAZQUEZ

Hi,

How can I re-write this formula so that when I copy it down 1 row the
ref_text portion !d483 changes to !d484 ?

=+INDIRECT("'"&'ACCOUNT MASTER'!$AH$6&"'!d483")

Please help.

Thanks,
Orlando
 
There are quite a few ways. Here's one:

=INDIRECT("'"&'ACCOUNT MASTER'!$AH$6&"'!D" & Row(A483))

HTH,
Bernie
MS Excel MVP
 
Try the below

=+INDIRECT("'"&'ACCOUNT MASTER'!$AH$6&"'!d" & ROW(A483))

If this post helps click Yes
 
Can we make the column changeable too ?


Bernie Deitrick said:
There are quite a few ways. Here's one:

=INDIRECT("'"&'ACCOUNT MASTER'!$AH$6&"'!D" & Row(A483))

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Can we make the column changeable too ?


Jacob Skaria said:
Try the below

=+INDIRECT("'"&'ACCOUNT MASTER'!$AH$6&"'!d" & ROW(A483))

If this post helps click Yes
Click to expand...
 
=INDIRECT("'"&'ACCOUNT MASTER'!$AH$6&"'!" & ADDRESS(Row(A483),COLUMN(D1)))


HTH,
Bernie
MS Excel MVP
 
Hi Bernie,

How can I make this formula adjust for column movement too ?
I tried to do the same as you did with Row by using COL but it doesn't work
that way.

Thanks for your help.

Orlando
 
Jacob,

That worked great. Can I make the column likewise changeable so that if I
move it down one and right one it also changes column ref ?
 
Bernie,

Is there a way to shorten the formula below. All that is changing one digit
in the indirect ref text. i.e., AH6 becomes AH7 .....AH7 becomes AH8...and
so on.

Orlando


=INDIRECT("'"&'ACCOUNT MASTER'!$AH$6&"'!" & ADDRESS(ROW(D483),COLUMN(D483)))
+INDIRECT("'"&'ACCOUNT MASTER'!$AH$7&"'!" & ADDRESS(ROW(D483),COLUMN(D483)))
+INDIRECT("'"&'ACCOUNT MASTER'!$AH$8&"'!" & ADDRESS(ROW(D483),COLUMN(D483)))
+INDIRECT("'"&'ACCOUNT MASTER'!$AH$9&"'!" & ADDRESS(ROW(D483),COLUMN(D483)))
+INDIRECT("'"&'ACCOUNT MASTER'!$AH$10&"'!" & ADDRESS(ROW(D483),COLUMN(D483)))
+INDIRECT("'"&'ACCOUNT MASTER'!$AH$11&"'!" & ADDRESS(ROW(D483),COLUMN(D483)))
 
Orlando,

AFAIK, you cannot use INDIRECT with 3 D ranges, so there is no way to shorten this.

HTH,
Bernie
MS Excel MVP
 
Back
Top