Increase the Row referenced in INDIRECT when dragging a formula down
up vote
0
down vote
favorite
Just wondering if anyone knows how to increase the row referenced in the INDIRECT when dragging a formula down a column.
To elaborate the formula is below, i'd like the cell referenced i.e. B5, C5 & D5 to increase the row referenced by +1 each time when i use drag to copy the formula e.g. so it'd go
B5, C5 & D5
B6, C6 & D6
B7, C7 & D7
etc.
As opposed to having alter the formula on each row.
=IF(SUM(INDIRECT("'"&A2&"'!b5")+INDIRECT("'"&A2&"'!C5")+INDIRECT("'"&A2&"'!D5"))>=1,"Yes","No")
I'm sure it's just something like the ROW function that'll do it but I can't seem to crack it.
Any help would be much appreciated and thanks in advance.
Cheers,
Danny
P.S. The INDIRECT is being used to reference a different sheet as determined by what is in cell A2
microsoft-excel worksheet-function microsoft-excel-2010
edited Nov 21 at 9:04
PeterH
3,13932245
asked Feb 6 '17 at 13:19
Danny
32
add a comment |
up vote
0
down vote
favorite
Just wondering if anyone knows how to increase the row referenced in the INDIRECT when dragging a formula down a column.
To elaborate the formula is below, i'd like the cell referenced i.e. B5, C5 & D5 to increase the row referenced by +1 each time when i use drag to copy the formula e.g. so it'd go
B5, C5 & D5
B6, C6 & D6
B7, C7 & D7
etc.
As opposed to having alter the formula on each row.
=IF(SUM(INDIRECT("'"&A2&"'!b5")+INDIRECT("'"&A2&"'!C5")+INDIRECT("'"&A2&"'!D5"))>=1,"Yes","No")
I'm sure it's just something like the ROW function that'll do it but I can't seem to crack it.
Any help would be much appreciated and thanks in advance.
Cheers,
Danny
P.S. The INDIRECT is being used to reference a different sheet as determined by what is in cell A2
microsoft-excel worksheet-function microsoft-excel-2010
edited Nov 21 at 9:04
PeterH
3,13932245
asked Feb 6 '17 at 13:19
Danny
32
The A2 ref. should also be locked i.e. $A$2 forgot i'd took those out for a second before pasting
– Danny
Feb 6 '17 at 13:30
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
Just wondering if anyone knows how to increase the row referenced in the INDIRECT when dragging a formula down a column.
To elaborate the formula is below, i'd like the cell referenced i.e. B5, C5 & D5 to increase the row referenced by +1 each time when i use drag to copy the formula e.g. so it'd go
B5, C5 & D5
B6, C6 & D6
B7, C7 & D7
etc.
As opposed to having alter the formula on each row.
=IF(SUM(INDIRECT("'"&A2&"'!b5")+INDIRECT("'"&A2&"'!C5")+INDIRECT("'"&A2&"'!D5"))>=1,"Yes","No")
I'm sure it's just something like the ROW function that'll do it but I can't seem to crack it.
Any help would be much appreciated and thanks in advance.
Cheers,
Danny
P.S. The INDIRECT is being used to reference a different sheet as determined by what is in cell A2
microsoft-excel worksheet-function microsoft-excel-2010
edited Nov 21 at 9:04
PeterH
3,13932245
asked Feb 6 '17 at 13:19
Danny
32
Just wondering if anyone knows how to increase the row referenced in the INDIRECT when dragging a formula down a column.
To elaborate the formula is below, i'd like the cell referenced i.e. B5, C5 & D5 to increase the row referenced by +1 each time when i use drag to copy the formula e.g. so it'd go
B5, C5 & D5
B6, C6 & D6
B7, C7 & D7
etc.
As opposed to having alter the formula on each row.
=IF(SUM(INDIRECT("'"&A2&"'!b5")+INDIRECT("'"&A2&"'!C5")+INDIRECT("'"&A2&"'!D5"))>=1,"Yes","No")
I'm sure it's just something like the ROW function that'll do it but I can't seem to crack it.
Any help would be much appreciated and thanks in advance.
Cheers,
Danny
P.S. The INDIRECT is being used to reference a different sheet as determined by what is in cell A2
microsoft-excel worksheet-function microsoft-excel-2010
microsoft-excel worksheet-function microsoft-excel-2010
edited Nov 21 at 9:04
PeterH
3,13932245
asked Feb 6 '17 at 13:19
Danny
32
edited Nov 21 at 9:04
PeterH
3,13932245
asked Feb 6 '17 at 13:19
Danny
32
edited Nov 21 at 9:04
PeterH
3,13932245
edited Nov 21 at 9:04
PeterH
3,13932245
edited Nov 21 at 9:04
PeterH
3,13932245
3,13932245
asked Feb 6 '17 at 13:19
Danny
32
asked Feb 6 '17 at 13:19
Danny
32
asked Feb 6 '17 at 13:19
Danny
32
32
The A2 ref. should also be locked i.e. $A$2 forgot i'd took those out for a second before pasting
– Danny
Feb 6 '17 at 13:30
add a comment |
The A2 ref. should also be locked i.e. $A$2 forgot i'd took those out for a second before pasting
– Danny
Feb 6 '17 at 13:30
The A2 ref. should also be locked i.e. $A$2 forgot i'd took those out for a second before pasting
– Danny
Feb 6 '17 at 13:30
The A2 ref. should also be locked i.e. $A$2 forgot i'd took those out for a second before pasting
– Danny
Feb 6 '17 at 13:30
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
Pull the row number out of the quotes and use ROW():
=IF(SUM(INDIRECT("'"&$A$2&"'!B" & ROW(5:5))+INDIRECT("'"&$A$2&"'!C" & ROW(5:5))+INDIRECT("'"&$A$2&"'!D" & ROW(5:5)))>=1,"Yes","No")
Now it will iterate as the formula is dragged/copied down.
answered Feb 6 '17 at 13:40
Scott Craner
10.7k1814
Brilliant thanks for that!
– Danny
Feb 6 '17 at 13:56
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
Pull the row number out of the quotes and use ROW():
=IF(SUM(INDIRECT("'"&$A$2&"'!B" & ROW(5:5))+INDIRECT("'"&$A$2&"'!C" & ROW(5:5))+INDIRECT("'"&$A$2&"'!D" & ROW(5:5)))>=1,"Yes","No")
Now it will iterate as the formula is dragged/copied down.
answered Feb 6 '17 at 13:40
Scott Craner
10.7k1814
Brilliant thanks for that!
– Danny
Feb 6 '17 at 13:56
add a comment |
up vote
1
down vote
accepted
Pull the row number out of the quotes and use ROW():
=IF(SUM(INDIRECT("'"&$A$2&"'!B" & ROW(5:5))+INDIRECT("'"&$A$2&"'!C" & ROW(5:5))+INDIRECT("'"&$A$2&"'!D" & ROW(5:5)))>=1,"Yes","No")
Now it will iterate as the formula is dragged/copied down.
answered Feb 6 '17 at 13:40
Scott Craner
10.7k1814
Brilliant thanks for that!
– Danny
Feb 6 '17 at 13:56
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
Pull the row number out of the quotes and use ROW():
=IF(SUM(INDIRECT("'"&$A$2&"'!B" & ROW(5:5))+INDIRECT("'"&$A$2&"'!C" & ROW(5:5))+INDIRECT("'"&$A$2&"'!D" & ROW(5:5)))>=1,"Yes","No")
Now it will iterate as the formula is dragged/copied down.
answered Feb 6 '17 at 13:40
Scott Craner
10.7k1814
Pull the row number out of the quotes and use ROW():
=IF(SUM(INDIRECT("'"&$A$2&"'!B" & ROW(5:5))+INDIRECT("'"&$A$2&"'!C" & ROW(5:5))+INDIRECT("'"&$A$2&"'!D" & ROW(5:5)))>=1,"Yes","No")
Now it will iterate as the formula is dragged/copied down.
answered Feb 6 '17 at 13:40
Scott Craner
10.7k1814
edited Feb 6 '17 at 13:49
answered Feb 6 '17 at 13:40
Scott Craner
10.7k1814
answered Feb 6 '17 at 13:40
Scott Craner
10.7k1814
answered Feb 6 '17 at 13:40
Scott Craner
10.7k1814
10.7k1814
Brilliant thanks for that!
– Danny
Feb 6 '17 at 13:56
add a comment |
Brilliant thanks for that!
– Danny
Feb 6 '17 at 13:56
Brilliant thanks for that!
– Danny
Feb 6 '17 at 13:56
Brilliant thanks for that!
– Danny
Feb 6 '17 at 13:56
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1175800%2fincrease-the-row-referenced-in-indirect-when-dragging-a-formula-down%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
The A2 ref. should also be locked i.e. $A$2 forgot i'd took those out for a second before pasting
– Danny
Feb 6 '17 at 13:30