ExcelBanter - "indirect" formula reference

ExcelBanter

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - "indirect" formula reference (https://www.excelbanter.com/excel-discussion-misc-queries/244276-indirect-formula-reference.html)

ORLANDO VAZQUEZ[_2_]

"indirect" formula reference

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

Bernie Deitrick

"indirect" formula reference

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

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

HTH,
Bernie
MS Excel MVP

"ORLANDO VAZQUEZ" wrote in message
...
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

Jacob Skaria

"indirect" formula reference

Try the below

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

If this post helps click Yes
---------------
Jacob Skaria

"ORLANDO VAZQUEZ" wrote:

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

ORLANDO VAZQUEZ[_2_]

"indirect" formula reference

That works fantastically. Can we also make the column changeable too ?

"ORLANDO VAZQUEZ" wrote:

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

ORLANDO VAZQUEZ[_2_]

"indirect" formula reference

Can we make the column changeable too ?

"Bernie Deitrick" wrote:

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

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

HTH,
Bernie
MS Excel MVP

"ORLANDO VAZQUEZ" wrote in message
...
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

ORLANDO VAZQUEZ[_2_]

"indirect" formula reference

Can we make the column changeable too ?

"Jacob Skaria" wrote:

Try the below

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

If this post helps click Yes
---------------
Jacob Skaria

"ORLANDO VAZQUEZ" wrote:

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

Bernie Deitrick

"indirect" formula reference

=INDIRECT("'"&'ACCOUNT MASTER'!$AH$6&"'!" & ADDRESS(Row(A483),COLUMN(D1)))

HTH,
Bernie
MS Excel MVP

"ORLANDO VAZQUEZ" wrote in message
...
Can we make the column changeable too ?

"Bernie Deitrick" wrote:

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

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

HTH,
Bernie
MS Excel MVP

"ORLANDO VAZQUEZ" wrote in message
...
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

ORLANDO VAZQUEZ[_2_]

"indirect" formula reference

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

"Bernie Deitrick" wrote:

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

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

HTH,
Bernie
MS Excel MVP

"ORLANDO VAZQUEZ" wrote in message
...
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

ORLANDO VAZQUEZ[_2_]

"indirect" formula reference

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 ?

"Jacob Skaria" wrote:

Try the below

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

If this post helps click Yes
---------------
Jacob Skaria

"ORLANDO VAZQUEZ" wrote:

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

ORLANDO VAZQUEZ[_2_]

"indirect" formula reference

Worked great! Thank you.

"Bernie Deitrick" wrote:

=INDIRECT("'"&'ACCOUNT MASTER'!$AH$6&"'!" & ADDRESS(Row(A483),COLUMN(D1)))

HTH,
Bernie
MS Excel MVP

"ORLANDO VAZQUEZ" wrote in message
...
Can we make the column changeable too ?

"Bernie Deitrick" wrote:

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

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

HTH,
Bernie
MS Excel MVP

"ORLANDO VAZQUEZ" wrote in message
...
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

ORLANDO VAZQUEZ[_2_]

"indirect" formula reference

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)))

"Bernie Deitrick" wrote:

=INDIRECT("'"&'ACCOUNT MASTER'!$AH$6&"'!" & ADDRESS(Row(A483),COLUMN(D1)))

HTH,
Bernie
MS Excel MVP

"ORLANDO VAZQUEZ" wrote in message
...
Can we make the column changeable too ?

"Bernie Deitrick" wrote:

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

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

HTH,
Bernie
MS Excel MVP

"ORLANDO VAZQUEZ" wrote in message
...
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

Bernie Deitrick

"indirect" formula reference

Orlando,

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

HTH,
Bernie
MS Excel MVP

"ORLANDO VAZQUEZ" wrote in message
...
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)))

"Bernie Deitrick" wrote:

=INDIRECT("'"&'ACCOUNT MASTER'!$AH$6&"'!" & ADDRESS(Row(A483),COLUMN(D1)))

HTH,
Bernie
MS Excel MVP

"ORLANDO VAZQUEZ" wrote in message
...
Can we make the column changeable too ?

"Bernie Deitrick" wrote:

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

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

HTH,
Bernie
MS Excel MVP

"ORLANDO VAZQUEZ" wrote in message
...
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

All times are GMT +1. The time now is 10:59 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com