Last filled row Function behaves strangely based on column it's in Announcing the arrival of...
If Windows 7 doesn't support WSL, then what is "Subsystem for UNIX-based Applications"?
Do I really need to have a message in a novel to appeal to readers?
What's the point of the test set?
Flash light on something
Why does 14 CFR have skipped subparts in my ASA 2019 FAR/AIM book?
Google .dev domain strangely redirects to https
How to write capital alpha?
Crossing US/Canada Border for less than 24 hours
Can a Beast Master ranger change beast companions?
One-one communication
What is the meaning of 'breadth' in breadth first search?
Draw 4 of the same figure in the same tikzpicture
Amount of permutations on an NxNxN Rubik's Cube
Drawing spherical mirrors
Why weren't discrete x86 CPUs ever used in game hardware?
Tannaka duality for semisimple groups
Is multiple magic items in one inherently imbalanced?
Putting class ranking in CV, but against dept guidelines
How does light 'choose' between wave and particle behaviour?
Has negative voting ever been officially implemented in elections, or seriously proposed, or even studied?
Did Mueller's report provide an evidentiary basis for the claim of Russian govt election interference via social media?
How can I set the aperture on my DSLR when it's attached to a telescope instead of a lens?
AppleTVs create a chatty alternate WiFi network
Dyck paths with extra diagonals from valleys (Laser construction)
Last filled row Function behaves strangely based on column it's in
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern)How do I get the last value of a column in an Excel spreadsheet?Collapsing rows of duplicate dates in a column leaving one row with a subtotal?Excel lookup value not on same rowExcel - find MAX value in multiple row ranges based on several criteriaHow can I combine VLOOKUP within an IF statement where the column index of the VLOOKUP function is a number extrapolated from a text?Excel formula to get first non-empty value and then return value other rowHow to find the last filled-in cell in a row?Excel - using a dynamic address in a formulaExcel - Date detection/comparison in mixed format cellsPivotTable Calculated Item IF Statement Issue
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
Function is acting very strange depending on column it's in.
Data start at row 7 (headers in row 6)
Column A has item name B Max Qty. C and D have simple functions and everything afterwards is the manually entered remaining Qty on certain dates.
Column D has
=((LOOKUP(2,1/(NOT(ISBLANK(7:7))),7:7))/B7)
Column C has
=100%-D7
Looks fine here:
What this should do is we enter a value in E onward and it calculated % based on the LAST filled row of what's left and what's used. Works great EXCEPT when there is nothing after column G. It does not register/see/run when last value is in E:G.
Now when I delete Item column A and Max becomes the new A the problem, it works fine.
We did not realize this till we started a new 2019 sheet, we used it all 2018 but we had data from earlier dates that filled the problem columns.
Can someone please explain this and offer a solution?
We need the item column.
microsoft-excel worksheet-function office365 microsoft-excel-365
edited 3 hours ago
Alex M
605314
asked 6 hours ago
AmommyAmommy
62
New contributor
Amommy 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 |
Function is acting very strange depending on column it's in.
Data start at row 7 (headers in row 6)
Column A has item name B Max Qty. C and D have simple functions and everything afterwards is the manually entered remaining Qty on certain dates.
Column D has
=((LOOKUP(2,1/(NOT(ISBLANK(7:7))),7:7))/B7)
Column C has
=100%-D7
Looks fine here:
What this should do is we enter a value in E onward and it calculated % based on the LAST filled row of what's left and what's used. Works great EXCEPT when there is nothing after column G. It does not register/see/run when last value is in E:G.
Now when I delete Item column A and Max becomes the new A the problem, it works fine.
We did not realize this till we started a new 2019 sheet, we used it all 2018 but we had data from earlier dates that filled the problem columns.
Can someone please explain this and offer a solution?
We need the item column.
microsoft-excel worksheet-function office365 microsoft-excel-365
edited 3 hours ago
Alex M
605314
asked 6 hours ago
AmommyAmommy
62
New contributor
Amommy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
You have a circular reference since you're usingLOOKUPagainst the entire row, including the cell theLOOKUPis in. This is causing you problems. Just changing the range of theLOOKUPdoes seem to solve the problem; replace formula inD7with:=((LOOKUP(2,1/(NOT(ISBLANK(E7:ABC7))),E7:ABC7))/B7), for example, and it seems to work as expected.
– Alex M
6 hours ago
I tried it with columns e7:7,obviously didn't work and quit after that. I didn't even realize I could do multicolumn abc:7. That worked thanks
– Amommy
5 hours ago
ABC7is actually a single cell reference.ABCis the 731st column in an Excel worksheet. I just wanted an arbitrarily long range and my fingers went 'abc'. A more realistic range might beE7:Z7, all you need to do is ensure that it starts fromE7and extends past any realistic limit to the number of columns you might use for data entry.E7:7is the same concept, unfortunately it's just not a syntax that Excel accepts.
– Alex M
5 hours ago
The cleanest solution would be something along the lines of replacing the target range in that formula with anINDIRECTcell reference formula using another version ofLOOKUPthat returns the max non-blank column number in the row (which you can find at the same exceljet page that you got yourLOOKUPformula from in the first place) to dynamically calculate the target range so you never have to worry about updating manually entered cell references (or writing them with arbitrarily long ranges in the first place). But, you know, who's got time for that
– Alex M
5 hours ago
add a comment |
Function is acting very strange depending on column it's in.
Data start at row 7 (headers in row 6)
Column A has item name B Max Qty. C and D have simple functions and everything afterwards is the manually entered remaining Qty on certain dates.
Column D has
=((LOOKUP(2,1/(NOT(ISBLANK(7:7))),7:7))/B7)
Column C has
=100%-D7
Looks fine here:
What this should do is we enter a value in E onward and it calculated % based on the LAST filled row of what's left and what's used. Works great EXCEPT when there is nothing after column G. It does not register/see/run when last value is in E:G.
Now when I delete Item column A and Max becomes the new A the problem, it works fine.
We did not realize this till we started a new 2019 sheet, we used it all 2018 but we had data from earlier dates that filled the problem columns.
Can someone please explain this and offer a solution?
We need the item column.
microsoft-excel worksheet-function office365 microsoft-excel-365
edited 3 hours ago
Alex M
605314
asked 6 hours ago
AmommyAmommy
62
New contributor
Amommy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Function is acting very strange depending on column it's in.
Data start at row 7 (headers in row 6)
Column A has item name B Max Qty. C and D have simple functions and everything afterwards is the manually entered remaining Qty on certain dates.
Column D has
=((LOOKUP(2,1/(NOT(ISBLANK(7:7))),7:7))/B7)
Column C has
=100%-D7
Looks fine here:
What this should do is we enter a value in E onward and it calculated % based on the LAST filled row of what's left and what's used. Works great EXCEPT when there is nothing after column G. It does not register/see/run when last value is in E:G.
Now when I delete Item column A and Max becomes the new A the problem, it works fine.
We did not realize this till we started a new 2019 sheet, we used it all 2018 but we had data from earlier dates that filled the problem columns.
Can someone please explain this and offer a solution?
We need the item column.
microsoft-excel worksheet-function office365 microsoft-excel-365
microsoft-excel worksheet-function office365 microsoft-excel-365
edited 3 hours ago
Alex M
605314
asked 6 hours ago
AmommyAmommy
62
New contributor
Amommy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 3 hours ago
Alex M
605314
asked 6 hours ago
AmommyAmommy
62
New contributor
Amommy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 3 hours ago
Alex M
605314
edited 3 hours ago
Alex M
605314
edited 3 hours ago
Alex M
605314
605314
asked 6 hours ago
AmommyAmommy
62
New contributor
Amommy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 6 hours ago
AmommyAmommy
62
asked 6 hours ago
AmommyAmommy
62
62
New contributor
Amommy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Amommy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Amommy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
You have a circular reference since you're usingLOOKUPagainst the entire row, including the cell theLOOKUPis in. This is causing you problems. Just changing the range of theLOOKUPdoes seem to solve the problem; replace formula inD7with:=((LOOKUP(2,1/(NOT(ISBLANK(E7:ABC7))),E7:ABC7))/B7), for example, and it seems to work as expected.
– Alex M
6 hours ago
I tried it with columns e7:7,obviously didn't work and quit after that. I didn't even realize I could do multicolumn abc:7. That worked thanks
– Amommy
5 hours ago
ABC7is actually a single cell reference.ABCis the 731st column in an Excel worksheet. I just wanted an arbitrarily long range and my fingers went 'abc'. A more realistic range might beE7:Z7, all you need to do is ensure that it starts fromE7and extends past any realistic limit to the number of columns you might use for data entry.E7:7is the same concept, unfortunately it's just not a syntax that Excel accepts.
– Alex M
5 hours ago
The cleanest solution would be something along the lines of replacing the target range in that formula with anINDIRECTcell reference formula using another version ofLOOKUPthat returns the max non-blank column number in the row (which you can find at the same exceljet page that you got yourLOOKUPformula from in the first place) to dynamically calculate the target range so you never have to worry about updating manually entered cell references (or writing them with arbitrarily long ranges in the first place). But, you know, who's got time for that
– Alex M
5 hours ago
add a comment |
You have a circular reference since you're usingLOOKUPagainst the entire row, including the cell theLOOKUPis in. This is causing you problems. Just changing the range of theLOOKUPdoes seem to solve the problem; replace formula inD7with:=((LOOKUP(2,1/(NOT(ISBLANK(E7:ABC7))),E7:ABC7))/B7), for example, and it seems to work as expected.
– Alex M
6 hours ago
I tried it with columns e7:7,obviously didn't work and quit after that. I didn't even realize I could do multicolumn abc:7. That worked thanks
– Amommy
5 hours ago
ABC7is actually a single cell reference.ABCis the 731st column in an Excel worksheet. I just wanted an arbitrarily long range and my fingers went 'abc'. A more realistic range might beE7:Z7, all you need to do is ensure that it starts fromE7and extends past any realistic limit to the number of columns you might use for data entry.E7:7is the same concept, unfortunately it's just not a syntax that Excel accepts.
– Alex M
5 hours ago
The cleanest solution would be something along the lines of replacing the target range in that formula with anINDIRECTcell reference formula using another version ofLOOKUPthat returns the max non-blank column number in the row (which you can find at the same exceljet page that you got yourLOOKUPformula from in the first place) to dynamically calculate the target range so you never have to worry about updating manually entered cell references (or writing them with arbitrarily long ranges in the first place). But, you know, who's got time for that
– Alex M
5 hours ago
You have a circular reference since you're using
LOOKUP against the entire row, including the cell the LOOKUP is in. This is causing you problems. Just changing the range of the LOOKUP does seem to solve the problem; replace formula in D7 with: =((LOOKUP(2,1/(NOT(ISBLANK(E7:ABC7))),E7:ABC7))/B7), for example, and it seems to work as expected.– Alex M
6 hours ago
You have a circular reference since you're using
LOOKUP against the entire row, including the cell the LOOKUP is in. This is causing you problems. Just changing the range of the LOOKUP does seem to solve the problem; replace formula in D7 with: =((LOOKUP(2,1/(NOT(ISBLANK(E7:ABC7))),E7:ABC7))/B7), for example, and it seems to work as expected.– Alex M
6 hours ago
I tried it with columns e7:7,obviously didn't work and quit after that. I didn't even realize I could do multicolumn abc:7. That worked thanks
– Amommy
5 hours ago
I tried it with columns e7:7,obviously didn't work and quit after that. I didn't even realize I could do multicolumn abc:7. That worked thanks
– Amommy
5 hours ago
ABC7 is actually a single cell reference. ABC is the 731st column in an Excel worksheet. I just wanted an arbitrarily long range and my fingers went 'abc'. A more realistic range might be E7:Z7, all you need to do is ensure that it starts from E7 and extends past any realistic limit to the number of columns you might use for data entry. E7:7 is the same concept, unfortunately it's just not a syntax that Excel accepts.– Alex M
5 hours ago
ABC7 is actually a single cell reference. ABC is the 731st column in an Excel worksheet. I just wanted an arbitrarily long range and my fingers went 'abc'. A more realistic range might be E7:Z7, all you need to do is ensure that it starts from E7 and extends past any realistic limit to the number of columns you might use for data entry. E7:7 is the same concept, unfortunately it's just not a syntax that Excel accepts.– Alex M
5 hours ago
The cleanest solution would be something along the lines of replacing the target range in that formula with an
INDIRECT cell reference formula using another version of LOOKUP that returns the max non-blank column number in the row (which you can find at the same exceljet page that you got your LOOKUP formula from in the first place) to dynamically calculate the target range so you never have to worry about updating manually entered cell references (or writing them with arbitrarily long ranges in the first place). But, you know, who's got time for that– Alex M
5 hours ago
The cleanest solution would be something along the lines of replacing the target range in that formula with an
INDIRECT cell reference formula using another version of LOOKUP that returns the max non-blank column number in the row (which you can find at the same exceljet page that you got your LOOKUP formula from in the first place) to dynamically calculate the target range so you never have to worry about updating manually entered cell references (or writing them with arbitrarily long ranges in the first place). But, you know, who's got time for that– Alex M
5 hours ago
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Amommy 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%2fsuperuser.com%2fquestions%2f1427454%2flast-filled-row-function-behaves-strangely-based-on-column-its-in%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Amommy is a new contributor. Be nice, and check out our Code of Conduct.
Amommy is a new contributor. Be nice, and check out our Code of Conduct.
Amommy is a new contributor. Be nice, and check out our Code of Conduct.
Amommy is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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%2f1427454%2flast-filled-row-function-behaves-strangely-based-on-column-its-in%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
You have a circular reference since you're using
LOOKUPagainst the entire row, including the cell theLOOKUPis in. This is causing you problems. Just changing the range of theLOOKUPdoes seem to solve the problem; replace formula inD7with:=((LOOKUP(2,1/(NOT(ISBLANK(E7:ABC7))),E7:ABC7))/B7), for example, and it seems to work as expected.– Alex M
6 hours ago
I tried it with columns e7:7,obviously didn't work and quit after that. I didn't even realize I could do multicolumn abc:7. That worked thanks
– Amommy
5 hours ago
ABC7is actually a single cell reference.ABCis the 731st column in an Excel worksheet. I just wanted an arbitrarily long range and my fingers went 'abc'. A more realistic range might beE7:Z7, all you need to do is ensure that it starts fromE7and extends past any realistic limit to the number of columns you might use for data entry.E7:7is the same concept, unfortunately it's just not a syntax that Excel accepts.– Alex M
5 hours ago
The cleanest solution would be something along the lines of replacing the target range in that formula with an
INDIRECTcell reference formula using another version ofLOOKUPthat returns the max non-blank column number in the row (which you can find at the same exceljet page that you got yourLOOKUPformula from in the first place) to dynamically calculate the target range so you never have to worry about updating manually entered cell references (or writing them with arbitrarily long ranges in the first place). But, you know, who's got time for that– Alex M
5 hours ago