CODEWITHSUNDEEP
excelloopsfor-loopwhile-loopexcel-formula
Parker
Parker

Reputation: 11

Creating something like an Excel "Loop" without VBA

I am trying to build a formula that simulates a "loop" in excel. The spreadsheet pulls data from a database into four spreadsheets. The formula needs to iterate through the sheets, moving onto the next sheet if the value found is #N/A. The current set up uses index(match()) to find the value in the table on the sheet. To move between sheets, I am using choose() to drive an indirect() to change to the next sheet, but I'm not sure how to simulate the loop... the problem seems to be that I would need to refer the function back to itself and then break out of the loop.

I'm imagining the solution would be something like 

=IFNA(CHOOSE( [the next number], INDEX(Sheet[the next number]!A1:Z500, MATCH(G1, Sheet[the next number]!A1:Z1), MATCH(A250, Sheet[the next number]!A1:A500)

The sheet cannot use VBA.

Upvotes: 1

Views: 10613

Answers (2)

Greedo
Greedo

Reputation: 5543

Further to my comment: Iterative calculations could be used, similar to this setup -

  • A1: =INDEX(B1:B4,A2)*1

  • A2: IF(ISERR(A1),A2+1,A2)

  • B1:B4: lookup values

When Index in A1 encounters a letter in B1:B4 it throws an error (from *1)

When A2 sees an error in A1, it increments

Since A2 is the row value that A1 is indexing, A1 looks at the next row, which may or may not cause an error

When A1 is not an error, A2 stops incrementing. See the below image

A2 iterates until A1 is not an error

A similar setup could be used to iterate through the sheets looking for an error in the index formula using ISNA, I would have used that as the example, but I'm not 100% sure what your formula is doing (it has too many open brackets!)

Notes

To access the iterative formulae option, go File->Options->Formulas->Check Enable iterative calculation. Set the value of maximum iterations to 1 if you want to hit F9 and increment through the sheets one at a time manually, otherwise set it to the number of sheets you are looking at (4?). That way you will loop your helper cell 4 times, and it will stop incrementing on whatever occasion it does not detect an error.

Also, I like to wrap my circular reference formula, A2, in an extra IF(A3 = "rst",1,...) so that I can reset the value without retyping the formula

Iterative calculations are great as they allow you effectively to save data in Excel mid calculation, but they must be used with caution to avoid infinite loops and huge calculation times - I use VBA because it's safer for that

Upvotes: 1

Charles Williams
Charles Williams

Reputation: 23550

It would be simpler to split the formula in to 5 different formulas:
the first 4 do the lookups on the 4 sheets
then the 5th formula uses nested If (or IFS if you are on Office 365 Excel 2016) to select the lookup with a valid result from the 4 formulas.

Upvotes: 0

Related Questions