When updating data from text file to Excel, my vlookup formula changes
up vote
0
down vote
favorite
I have a data connection from A:J. In Columns K:L, I have a vlookup formula on one of the A:J columns. When I refresh with a new set of data, if the # of rows changes, the formula loses count of which row it's in. For example, vlookup in Row 109 would change to 130. It's as if the rows containing formulas in between (110-129) were deleted. How do I prevent that?
I thought adding $ might help but can't find a way to do it easily so that every row has $ before the consecutive numbers.
microsoft-excel
asked Nov 18 at 19:43
user2781658
1
add a comment |
up vote
0
down vote
favorite
I have a data connection from A:J. In Columns K:L, I have a vlookup formula on one of the A:J columns. When I refresh with a new set of data, if the # of rows changes, the formula loses count of which row it's in. For example, vlookup in Row 109 would change to 130. It's as if the rows containing formulas in between (110-129) were deleted. How do I prevent that?
I thought adding $ might help but can't find a way to do it easily so that every row has $ before the consecutive numbers.
microsoft-excel
asked Nov 18 at 19:43
user2781658
1
For people who have never seen your spreadsheet (i.e., everyone reading this), it would be a lot less abstract and ambiguous, and easier to understand, if we could see your spreadsheet. Can you add a screenshot or mockup? What's "a data connection from A:J"? What is the vlookup formula? Where are you adding rows? If you add rows and Excel adjusts the affected formulas to reflect their new locations, that's basically how it's supposed to work. Help us understand why you need to to work differently.
– fixer1234
Nov 18 at 22:07
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a data connection from A:J. In Columns K:L, I have a vlookup formula on one of the A:J columns. When I refresh with a new set of data, if the # of rows changes, the formula loses count of which row it's in. For example, vlookup in Row 109 would change to 130. It's as if the rows containing formulas in between (110-129) were deleted. How do I prevent that?
I thought adding $ might help but can't find a way to do it easily so that every row has $ before the consecutive numbers.
microsoft-excel
asked Nov 18 at 19:43
user2781658
1
I have a data connection from A:J. In Columns K:L, I have a vlookup formula on one of the A:J columns. When I refresh with a new set of data, if the # of rows changes, the formula loses count of which row it's in. For example, vlookup in Row 109 would change to 130. It's as if the rows containing formulas in between (110-129) were deleted. How do I prevent that?
I thought adding $ might help but can't find a way to do it easily so that every row has $ before the consecutive numbers.
microsoft-excel
microsoft-excel
asked Nov 18 at 19:43
user2781658
1
asked Nov 18 at 19:43
user2781658
1
asked Nov 18 at 19:43
user2781658
1
asked Nov 18 at 19:43
user2781658
1
asked Nov 18 at 19:43
user2781658
1
1
For people who have never seen your spreadsheet (i.e., everyone reading this), it would be a lot less abstract and ambiguous, and easier to understand, if we could see your spreadsheet. Can you add a screenshot or mockup? What's "a data connection from A:J"? What is the vlookup formula? Where are you adding rows? If you add rows and Excel adjusts the affected formulas to reflect their new locations, that's basically how it's supposed to work. Help us understand why you need to to work differently.
– fixer1234
Nov 18 at 22:07
add a comment |
For people who have never seen your spreadsheet (i.e., everyone reading this), it would be a lot less abstract and ambiguous, and easier to understand, if we could see your spreadsheet. Can you add a screenshot or mockup? What's "a data connection from A:J"? What is the vlookup formula? Where are you adding rows? If you add rows and Excel adjusts the affected formulas to reflect their new locations, that's basically how it's supposed to work. Help us understand why you need to to work differently.
– fixer1234
Nov 18 at 22:07
For people who have never seen your spreadsheet (i.e., everyone reading this), it would be a lot less abstract and ambiguous, and easier to understand, if we could see your spreadsheet. Can you add a screenshot or mockup? What's "a data connection from A:J"? What is the vlookup formula? Where are you adding rows? If you add rows and Excel adjusts the affected formulas to reflect their new locations, that's basically how it's supposed to work. Help us understand why you need to to work differently.
– fixer1234
Nov 18 at 22:07
For people who have never seen your spreadsheet (i.e., everyone reading this), it would be a lot less abstract and ambiguous, and easier to understand, if we could see your spreadsheet. Can you add a screenshot or mockup? What's "a data connection from A:J"? What is the vlookup formula? Where are you adding rows? If you add rows and Excel adjusts the affected formulas to reflect their new locations, that's basically how it's supposed to work. Help us understand why you need to to work differently.
– fixer1234
Nov 18 at 22:07
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
By data connection from A:J, I meant the data from my data connection was populating in these cells. The solution is fairly generic:
Click into where the text file data is being populated, click Properties, then check "Overwrite old cells" and "Fill down adjacent columns with formula". The other way I was doing was causing the cells adjacent to the data to be deleted when the data connection refreshed.
answered Nov 18 at 23:27
user2781658
1
1
Perhaps you intended to post this as a reply to fixer1234's comment. If so, please delete your answer and post the contents as a comment to your original question. Otherwise, people may not look at your question anymore since it has already got an "answer" posted in response
– Bharat Anand
Nov 19 at 2:38
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
By data connection from A:J, I meant the data from my data connection was populating in these cells. The solution is fairly generic:
Click into where the text file data is being populated, click Properties, then check "Overwrite old cells" and "Fill down adjacent columns with formula". The other way I was doing was causing the cells adjacent to the data to be deleted when the data connection refreshed.
answered Nov 18 at 23:27
user2781658
1
1
Perhaps you intended to post this as a reply to fixer1234's comment. If so, please delete your answer and post the contents as a comment to your original question. Otherwise, people may not look at your question anymore since it has already got an "answer" posted in response
– Bharat Anand
Nov 19 at 2:38
add a comment |
up vote
0
down vote
By data connection from A:J, I meant the data from my data connection was populating in these cells. The solution is fairly generic:
Click into where the text file data is being populated, click Properties, then check "Overwrite old cells" and "Fill down adjacent columns with formula". The other way I was doing was causing the cells adjacent to the data to be deleted when the data connection refreshed.
answered Nov 18 at 23:27
user2781658
1
1
Perhaps you intended to post this as a reply to fixer1234's comment. If so, please delete your answer and post the contents as a comment to your original question. Otherwise, people may not look at your question anymore since it has already got an "answer" posted in response
– Bharat Anand
Nov 19 at 2:38
add a comment |
up vote
0
down vote
up vote
0
down vote
By data connection from A:J, I meant the data from my data connection was populating in these cells. The solution is fairly generic:
Click into where the text file data is being populated, click Properties, then check "Overwrite old cells" and "Fill down adjacent columns with formula". The other way I was doing was causing the cells adjacent to the data to be deleted when the data connection refreshed.
answered Nov 18 at 23:27
user2781658
1
By data connection from A:J, I meant the data from my data connection was populating in these cells. The solution is fairly generic:
Click into where the text file data is being populated, click Properties, then check "Overwrite old cells" and "Fill down adjacent columns with formula". The other way I was doing was causing the cells adjacent to the data to be deleted when the data connection refreshed.
answered Nov 18 at 23:27
user2781658
1
answered Nov 18 at 23:27
user2781658
1
answered Nov 18 at 23:27
user2781658
1
answered Nov 18 at 23:27
user2781658
1
1
1
Perhaps you intended to post this as a reply to fixer1234's comment. If so, please delete your answer and post the contents as a comment to your original question. Otherwise, people may not look at your question anymore since it has already got an "answer" posted in response
– Bharat Anand
Nov 19 at 2:38
add a comment |
1
Perhaps you intended to post this as a reply to fixer1234's comment. If so, please delete your answer and post the contents as a comment to your original question. Otherwise, people may not look at your question anymore since it has already got an "answer" posted in response
– Bharat Anand
Nov 19 at 2:38
1
1
Perhaps you intended to post this as a reply to fixer1234's comment. If so, please delete your answer and post the contents as a comment to your original question. Otherwise, people may not look at your question anymore since it has already got an "answer" posted in response
– Bharat Anand
Nov 19 at 2:38
Perhaps you intended to post this as a reply to fixer1234's comment. If so, please delete your answer and post the contents as a comment to your original question. Otherwise, people may not look at your question anymore since it has already got an "answer" posted in response
– Bharat Anand
Nov 19 at 2:38
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%2f1376512%2fwhen-updating-data-from-text-file-to-excel-my-vlookup-formula-changes%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
For people who have never seen your spreadsheet (i.e., everyone reading this), it would be a lot less abstract and ambiguous, and easier to understand, if we could see your spreadsheet. Can you add a screenshot or mockup? What's "a data connection from A:J"? What is the vlookup formula? Where are you adding rows? If you add rows and Excel adjusts the affected formulas to reflect their new locations, that's basically how it's supposed to work. Help us understand why you need to to work differently.
– fixer1234
Nov 18 at 22:07