BigMoe
New Member
- Joined
- Nov 1, 2014
- Messages
- 18
*** I'm using Excel 2003 ***
This is more of a general question because I don't have the whole formula 100% figured-out, but I would appreciate some direction on how to either make the formula below shorter (I've run into the Excel limit) or go about it in a more efficient way (without VBA please). I'm a bit of a self-taught Excel weekend warrior, so I'm open to using a completely different formula if need-be.
=IF(SHEETOFFSET(-1,$D$169)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0), if(and(vlookup(sheetoffset(-1,$h$164),tables!$m$21:$n$36,2,0)<4,sheetoffset(-1,$d$170)="">0,SHEETOFFSET(-1,$H$175)>=1, SHEETOFFSET(-1,$D$168)>=SHEETOFFSET(-1,$H$168)),VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$A$1:$B$16,2,0), IF(AND(VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$M$21:$N$36,2,0)=4,SHEETOFFSET(-1,$H$165)="A",SHEETOFFSET(-1,$D$165)>0, SHEETOFFSET(-1,$H$175)>=1,SHEETOFFSET(-1,$D$168)>=SHEETOFFSET(-1,$H$168),SHEETOFFSET(-1,$D$170)>0), VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$A$1:$B$16,2,0),IF(AND(OR(VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$M$21:$N$36,2,0)=5, VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$M$21:$N$36,2,0)=6,VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$M$21:$N$36,2,0)=7), SHEETOFFSET(-1,$H$165)="A",SHEETOFFSET(-1,$D$165)>0,SHEETOFFSET(-1,$D$168)>=SHEETOFFSET(-1,$H$168),SHEETOFFSET(-1,$D$170)>0), VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$P$21:$Q$36,2,0),SHEETOFFSET(-1,$H$164)))))
I've read-up on "Named Formulas", but having some trouble putting it in practice. For example, I tried naming the following parts of the formula, but end up getting errors:
Trial #1 (SHEETOFFSET(-1,$D$169)...<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)
Trial #2 SHEETOFFSET(-1,$D$169)...<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)
Trial #3 IF(SHEETOFFSET(-1,$D$169)...<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)
Any tips, advice, or examples using the formula above would be of great help, thanks. </sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)
</sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)
</sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)
</sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0),>
This is more of a general question because I don't have the whole formula 100% figured-out, but I would appreciate some direction on how to either make the formula below shorter (I've run into the Excel limit) or go about it in a more efficient way (without VBA please). I'm a bit of a self-taught Excel weekend warrior, so I'm open to using a completely different formula if need-be.
=IF(SHEETOFFSET(-1,$D$169)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0), if(and(vlookup(sheetoffset(-1,$h$164),tables!$m$21:$n$36,2,0)<4,sheetoffset(-1,$d$170)="">0,SHEETOFFSET(-1,$H$175)>=1, SHEETOFFSET(-1,$D$168)>=SHEETOFFSET(-1,$H$168)),VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$A$1:$B$16,2,0), IF(AND(VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$M$21:$N$36,2,0)=4,SHEETOFFSET(-1,$H$165)="A",SHEETOFFSET(-1,$D$165)>0, SHEETOFFSET(-1,$H$175)>=1,SHEETOFFSET(-1,$D$168)>=SHEETOFFSET(-1,$H$168),SHEETOFFSET(-1,$D$170)>0), VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$A$1:$B$16,2,0),IF(AND(OR(VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$M$21:$N$36,2,0)=5, VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$M$21:$N$36,2,0)=6,VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$M$21:$N$36,2,0)=7), SHEETOFFSET(-1,$H$165)="A",SHEETOFFSET(-1,$D$165)>0,SHEETOFFSET(-1,$D$168)>=SHEETOFFSET(-1,$H$168),SHEETOFFSET(-1,$D$170)>0), VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$P$21:$Q$36,2,0),SHEETOFFSET(-1,$H$164)))))
I've read-up on "Named Formulas", but having some trouble putting it in practice. For example, I tried naming the following parts of the formula, but end up getting errors:
Trial #1 (SHEETOFFSET(-1,$D$169)...<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)
Trial #2 SHEETOFFSET(-1,$D$169)...<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)
Trial #3 IF(SHEETOFFSET(-1,$D$169)...<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)
Any tips, advice, or examples using the formula above would be of great help, thanks. </sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)
</sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)
</sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)
</sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0),>
Last edited: