Massimo Scola

asked on

# Problem with nested IF loop in Excel

Hi all

I am learning Excel and I can sometimes apply what I have learned at work.

I wrote a formula with several loops and was told by Excel that the formula is too long. I later read that there cannot be more than six IFs in a formula.

Is there any other way to solve this problem? I read that I could use SUMPRODUCT() but that did not work. If this cannot be done with a formula, would it be possible to write a custom function? I have written basic VBA code before but I have never custom functions.

I really appreciate your help as I can learn and have learned new ways to write my formulas.

Massimo

Untitled-1.gif

Inventory-Question.xls

I am learning Excel and I can sometimes apply what I have learned at work.

__Here is my problem__:I wrote a formula with several loops and was told by Excel that the formula is too long. I later read that there cannot be more than six IFs in a formula.

**Please let me explain what I would like to accomplish:**
The spreadsheet is a simple inventory spreadsheet.The formula in the last sheet will be used in the original inventory file.

There is one sheet for each month, the last sheet being the annual overview.

The last sheet should display the beginning inventory: The item might be added in January or sometime during the year.

The items will not be added by me to the spreadsheet. There might be empty rows and the items might not be ordered. Short: It will look like a mess.

There might be as many as 200 rows.

__This is my idea:__## 1

I went to the sheet "Inventory"## 2

In cell C2 I set a formula with IF() and LOOKUP() to search for the Products (A and B) in the month of January## 3

I used ISNA() to check for a match.## 4

If there is a match, return the value in column C (the inital stock)## 5

Otherwise, go to the next month (February, March etc).and do the same searchIs there any other way to solve this problem? I read that I could use SUMPRODUCT() but that did not work. If this cannot be done with a formula, would it be possible to write a custom function? I have written basic VBA code before but I have never custom functions.

I really appreciate your help as I can learn and have learned new ways to write my formulas.

Massimo

Untitled-1.gif

Inventory-Question.xls

One could write some vba to run through a list on the inventory sheet and find the first entry in the other sheets for each item. Does that sound useful? Btw Excel 2007 allows many more levels of nested formulae.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Slight refinement, change line 16 to

`Exit Function`

and add at line 25:`BegInv=""`

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Note that even though I added columns, my file is 30% smaller than yours. This may matter if the sheets get large. The computation will also be a lot faster and you can add months simply by dragging the formulas over.

ASKER

fantastic