Formula too long - character limit reached - will VBA work?

Question

I'm creating a tool that summarizes certain data that we put into Excel workbooks. So essentially I'm using the "indirect" function to pull from the workbooks after we enter their names into the summary tool. Generally, this idea:

IFERROR(INDIRECT("'[" & Refs!$B$4 & "]" & $A$1 & "'!" & EmplRefs!$C91),0)=C$76,1,0)

Where $B$4 is the location of the first file name, then I would do +IFERROR(INDIRECT ... $B$5 etc. etc. This works fine. However, I need to set it up so it can accommodate up to 70 files. This also works fine EXCEPT in a particular formula where it's an IF/AND statement and it makes the formula exceed the character limit for formulas/cells.

If I put it into VBA, will VBA allow a super long formula, or is it the same limit?

My understanding of adding a formula to a cell:

Sub voccomplete()
    Worksheets("Employment").Range("E83").Formula = "=[giant formula here]"
End Sub

Other ideas of how to get this information welcome (thought I'm not really familiar with VBA, I can usually follow instructions)... This is the formula in question (accommodating only 25 files):

IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$4& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$4& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$5& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$5& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$6& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$6& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$7& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$7& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$8& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$8& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$9& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$9& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$10& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$10& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$11& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$11& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$12& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$12& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$13& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$13& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$14& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$14& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$15& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$15& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$16& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$16& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$17& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$17& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$18& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$18& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$19& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$19& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$20& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$20& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$21& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$21& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$22& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$22& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$23& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$23& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$24& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$24& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$25& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$25& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$26& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$26& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$27& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$27& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$28& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$28& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)

Computer info: Windows 7 Professional, Excel 2016

Instead of such a long formula (which you likely don't want to actually use, that's way too hard to understand/troubleshoot/manage), why not use a helper column. Next to each reference `B[row]`, just put your formula to see if it's "Completed" Then you can use that to summarize/filter? — BruceWayne, May 31 '18 at 14:18
`INDIRECT` won't work on closed workbooks. Do you really have 70 workbooks open? — Olly, May 31 '18 at 14:20
Also, there is a character limit on what you can put in the formula for a cell; so you probably aught to find another method, if possible. — Mistella, May 31 '18 at 14:29
What's your idea here, just to check if various files have a certain value in a certain cell? — BruceWayne, May 31 '18 at 14:49
@Rachel To answer your question directly rather than providing you with a solution: VBA doesn't do anything special to/with forumlae, so if you try to put that formula into a cell you will have the same problem (but get a different error because of VBA). It would be best to do what you're looking to do in VBA as in Harrassed Dad's answer. — Taelsin, May 31 '18 at 15:38
@Taelsin thanks for the info! This is what I needed to know. — Rachel, May 31 '18 at 15:46

score 2 · Answer 1 · answered May 31 '18 at 15:32

So you're looping through workbooks looking at the sheet defined in A1 and in that sheet looking at the cell defined in C91 and comparing that to the value held in b76 - if it's equal and the cell defined in B93 is also equal to "Completed" then you increment by one.

You need a User Defined Function that takes the range where the workbooks to look at is defined (B4:B74 ?), and the contents of ranges C91 and B93 and "B76" as addresses

 Public Function NumberCompleted(WorkbooksToLookAt as range, S1 as string, S2 as string,s3 as string) as long
 Dim wb as workbook
 Dim ws as worksheet
 Dim r as range
 dim c as range
 dim counter as long
 For each r in Workbookstolookat
     set wb = workbooks(r)
     set ws = wb.worksheets(range("a1").text)
     set c = ws.range(s1)
     if c = ws.range(s2) then
           if ws.range(s3)="Completed" then
                 counter = counter + 1
           end if
      end if
    Next r
    NumberCompleted = counter
    End Function

Which you would use in your spreadsheet as

  =NumberCompleted(B4:B74,C91,"B76",B93)

NB: I'm on my phone so I can't run this so there may be syntax errors in it

As long as the workbooks are open, this looks like it will work. If they're not open, will require another method to query data from the closed workbooks. — David Zemens, May 31 '18 at 15:39
@BruceWayne Yes, essentially I'm checking all the same cell in the open workbooks and if it's "Yes" (or "No" etc) increment by one for a full count. Also - thank you for the idea! That could work. — Rachel, May 31 '18 at 15:42
@harassed-dad Thank you for the response - this is definitely above my level but I will comb through it to see if ti could work for my purposes. It seems like it would be a lot easier than what I currently have! — Rachel, May 31 '18 at 15:45

Formula too long - character limit reached - will VBA work?

1 Answers1