Need help in shortening formula.


New Member
Nov 1, 2014
*** 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)
Last edited:


New Member
Nov 1, 2014
This formula compares the current level of a student and goes through a set of complex variables to see if they will be promoted or demoted to another level.

I realize that the help I'm going to receive is on the general side, but that's fine. I just don't know how else to word long formulas like this since I'm not an advanced user. I'm reading-up on Named Formulas at the moment, but not having much success. Is there a way of using helper columns or additional tables to simplify things? Any pointing in a possible direction would be helpful.


Active Member
Aug 29, 2014
What are the criteria for promotion or demotion? For instance, Student Timmy should be promoted if column B is greater than 2 and column C is equal to Red, but not if Column D is equal to "F", etc.

What value should the formula return? Promote, Demote, No Action?

It may be complicated to describe, but it's even more complicated to solve without a description I'm afraid...

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...