Mixing
Excel: Drop down list with dependent length changes reference spontaneously
up vote
0
down vote
favorite
In an excel file I am using a drop down list with variable length dependent on a user selection. The length is given by following formula that counts all the rows in the list and offers a drop down containing all the values in it.
=SQL_Structure!T5:INDEX(SQL_Structure!$T:$T,COUNTA(SQL_Structure!$T:$T)+3)
When I save and close the reference in the name manager, the formula spontaneously changes to the different cell instead of reference to cell T5. The modification is seemingly random. I tried several times and the cell reference differed each time. When I run the macro that modifies the list the reference is lost completely as below.
=SQL_Structure!#REF:INDEX(SQL_Structure!$T:$T,COUNTA(SQL_Structure!$T:$T)+3)
Any idea on what might be causing it?
excel vba drop-down-menu
asked 2 days ago
Pavel Chmelík
12
New contributor
Pavel Chmelík is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
0
down vote
favorite
In an excel file I am using a drop down list with variable length dependent on a user selection. The length is given by following formula that counts all the rows in the list and offers a drop down containing all the values in it.
=SQL_Structure!T5:INDEX(SQL_Structure!$T:$T,COUNTA(SQL_Structure!$T:$T)+3)
When I save and close the reference in the name manager, the formula spontaneously changes to the different cell instead of reference to cell T5. The modification is seemingly random. I tried several times and the cell reference differed each time. When I run the macro that modifies the list the reference is lost completely as below.
=SQL_Structure!#REF:INDEX(SQL_Structure!$T:$T,COUNTA(SQL_Structure!$T:$T)+3)
Any idea on what might be causing it?
excel vba drop-down-menu
asked 2 days ago
Pavel Chmelík
12
New contributor
Pavel Chmelík is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
In an excel file I am using a drop down list with variable length dependent on a user selection. The length is given by following formula that counts all the rows in the list and offers a drop down containing all the values in it.
=SQL_Structure!T5:INDEX(SQL_Structure!$T:$T,COUNTA(SQL_Structure!$T:$T)+3)
When I save and close the reference in the name manager, the formula spontaneously changes to the different cell instead of reference to cell T5. The modification is seemingly random. I tried several times and the cell reference differed each time. When I run the macro that modifies the list the reference is lost completely as below.
=SQL_Structure!#REF:INDEX(SQL_Structure!$T:$T,COUNTA(SQL_Structure!$T:$T)+3)
Any idea on what might be causing it?
excel vba drop-down-menu
asked 2 days ago
Pavel Chmelík
12
New contributor
Pavel Chmelík is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
In an excel file I am using a drop down list with variable length dependent on a user selection. The length is given by following formula that counts all the rows in the list and offers a drop down containing all the values in it.
=SQL_Structure!T5:INDEX(SQL_Structure!$T:$T,COUNTA(SQL_Structure!$T:$T)+3)
When I save and close the reference in the name manager, the formula spontaneously changes to the different cell instead of reference to cell T5. The modification is seemingly random. I tried several times and the cell reference differed each time. When I run the macro that modifies the list the reference is lost completely as below.
=SQL_Structure!#REF:INDEX(SQL_Structure!$T:$T,COUNTA(SQL_Structure!$T:$T)+3)
Any idea on what might be causing it?
excel vba drop-down-menu
excel vba drop-down-menu
asked 2 days ago
Pavel Chmelík
12
New contributor
Pavel Chmelík is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
Pavel Chmelík
12
New contributor
Pavel Chmelík is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
Pavel Chmelík
12
New contributor
Pavel Chmelík is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
Pavel Chmelík
12
asked 2 days ago
Pavel Chmelík
12
12
New contributor
Pavel Chmelík is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Pavel Chmelík is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Pavel Chmelík is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
It should be good. Try like: SQL_Structure!$T$5
Almantas
answered 2 days ago
Almantas Bendoraitis
112
New contributor
Almantas Bendoraitis is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Hi Almantas,Thank you. Fixing the cell works until I run the macro that modifies the content of table the cell T5 belongs into. I have found that once the data in the table are renewed the formula breaks. Now I was able to think of 2 solutions: 1. To start indexing the cell above which works but includes also the header in the list. That I would like o avoid. 2. To format the data not as a table. That is also not optimal as the table format is essential for other macro I run in the report.
– Pavel Chmelík
2 days ago
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
It should be good. Try like: SQL_Structure!$T$5
Almantas
answered 2 days ago
Almantas Bendoraitis
112
New contributor
Almantas Bendoraitis is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Hi Almantas,Thank you. Fixing the cell works until I run the macro that modifies the content of table the cell T5 belongs into. I have found that once the data in the table are renewed the formula breaks. Now I was able to think of 2 solutions: 1. To start indexing the cell above which works but includes also the header in the list. That I would like o avoid. 2. To format the data not as a table. That is also not optimal as the table format is essential for other macro I run in the report.
– Pavel Chmelík
2 days ago
add a comment |
up vote
0
down vote
It should be good. Try like: SQL_Structure!$T$5
Almantas
answered 2 days ago
Almantas Bendoraitis
112
New contributor
Almantas Bendoraitis is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Hi Almantas,Thank you. Fixing the cell works until I run the macro that modifies the content of table the cell T5 belongs into. I have found that once the data in the table are renewed the formula breaks. Now I was able to think of 2 solutions: 1. To start indexing the cell above which works but includes also the header in the list. That I would like o avoid. 2. To format the data not as a table. That is also not optimal as the table format is essential for other macro I run in the report.
– Pavel Chmelík
2 days ago
add a comment |
up vote
0
down vote
up vote
0
down vote
It should be good. Try like: SQL_Structure!$T$5
Almantas
answered 2 days ago
Almantas Bendoraitis
112
New contributor
Almantas Bendoraitis is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
It should be good. Try like: SQL_Structure!$T$5
Almantas
answered 2 days ago
Almantas Bendoraitis
112
New contributor
Almantas Bendoraitis is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 2 days ago
Almantas Bendoraitis
112
New contributor
Almantas Bendoraitis is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 2 days ago
Almantas Bendoraitis
112
answered 2 days ago
Almantas Bendoraitis
112
112
New contributor
Almantas Bendoraitis is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Almantas Bendoraitis is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Almantas Bendoraitis is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Hi Almantas,Thank you. Fixing the cell works until I run the macro that modifies the content of table the cell T5 belongs into. I have found that once the data in the table are renewed the formula breaks. Now I was able to think of 2 solutions: 1. To start indexing the cell above which works but includes also the header in the list. That I would like o avoid. 2. To format the data not as a table. That is also not optimal as the table format is essential for other macro I run in the report.
– Pavel Chmelík
2 days ago
add a comment |
Hi Almantas,Thank you. Fixing the cell works until I run the macro that modifies the content of table the cell T5 belongs into. I have found that once the data in the table are renewed the formula breaks. Now I was able to think of 2 solutions: 1. To start indexing the cell above which works but includes also the header in the list. That I would like o avoid. 2. To format the data not as a table. That is also not optimal as the table format is essential for other macro I run in the report.
– Pavel Chmelík
2 days ago
Hi Almantas,Thank you. Fixing the cell works until I run the macro that modifies the content of table the cell T5 belongs into. I have found that once the data in the table are renewed the formula breaks. Now I was able to think of 2 solutions: 1. To start indexing the cell above which works but includes also the header in the list. That I would like o avoid. 2. To format the data not as a table. That is also not optimal as the table format is essential for other macro I run in the report.
– Pavel Chmelík
2 days ago
Hi Almantas,Thank you. Fixing the cell works until I run the macro that modifies the content of table the cell T5 belongs into. I have found that once the data in the table are renewed the formula breaks. Now I was able to think of 2 solutions: 1. To start indexing the cell above which works but includes also the header in the list. That I would like o avoid. 2. To format the data not as a table. That is also not optimal as the table format is essential for other macro I run in the report.
– Pavel Chmelík
2 days ago
add a comment |
Pavel Chmelík is a new contributor. Be nice, and check out our Code of Conduct.
Pavel Chmelík is a new contributor. Be nice, and check out our Code of Conduct.
Pavel Chmelík is a new contributor. Be nice, and check out our Code of Conduct.
Pavel Chmelík is a new contributor. Be nice, and check out our Code of Conduct.
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%2fstackoverflow.com%2fquestions%2f53373161%2fexcel-drop-down-list-with-dependent-length-changes-reference-spontaneously%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
