Solved

Problem with nested IF loop in Excel

Posted on 2011-02-15
6
414 Views
Last Modified: 2012-05-11
Hi all

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 search
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
0
Comment
Question by:Massimo Scola
  • 3
  • 2
6 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 34902511
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.
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 250 total points
ID: 34902651
Perhaps this custom function will help? So you can type, e.g =BegInv(A2,B2) in C2 of inventory sheet. Worked for your example file.
Function BegInv(vProd As Variant, vDesc As Variant) As Variant
  
Dim rFind As Range, ws As Worksheet, sAddr As String

With Sheets("Inventory")
    For Each ws In Worksheets
        If ws.Name <> "Inventory" Then
            Set rFind = ws.Columns(1).Find(What:=vProd, After:=ws.Range("A1"), _
                                           LookAt:=xlWhole, SearchDirection:=xlNext, _
                                           MatchCase:=False, SearchFormat:=False)
            If Not rFind Is Nothing Then
                sAddr = rFind.Address
                Do
                    If rFind.Offset(, 1) = vDesc Then
                        BegInv = rFind.Offset(, 2)
                        Exit For
                    Else
                        Set rFind = ws.Columns(1).Find(What:=vProd, After:=rFind)
                    End If
                Loop While rFind.Address <> sAddr
            End If
        End If
    Next ws
End With
     
End Function

Open in new window

0
 
LVL 24

Expert Comment

by:StephenJR
ID: 34902665
Slight refinement, change line 16 to
Exit Function

Open in new window

and add at line 25:
BegInv=""

Open in new window

0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 250 total points
ID: 34902683
I recommend the making a summary of each month on the inventory sheet. Then you just need to find the first non-zero month. You can hide the month cells if you wish. The formula would look like this
=INDEX(C2:H2,MATCH(TRUE,C2:H2>0,0))
Note: It's an array formula so you need to hit ctrl+shift+enter when entering it.

Then I would do the following to speed it up and make it more robust
1: Use SUMPRODUCT to find the counts for each month instead of lookup(2/...) it's much much faster. It would look something like this
=SUMPRODUCT((January!$A$2:$A$300)=$A2)*(January!$B$2:$B$300)=$B2)*January!$C$2:$C$300)
2: To do all the months without having to retype the formula, just use INDIRECT and the column header
=SUMPRODUCT((INDIRECT(C$1&"!$A$2:$A$300)=$A2)*(INDIRECT(C$1&"!$B$2:$B$300)=$B2)*INDIRECT(C$1&"!$C$2:$C$300))
3: Wait, what if there are more than 300? Put a =counta(A:A) somewhere (like J1) and use it
=SUMPRODUCT((INDIRECT(C$1&"!$A$2:$A$"&$J$1)=$A2)*(INDIRECT(C$1&"!$B$2:$B$"&$J$1)=$B2)*INDIRECT(C$1&"!$C$2:$C$"&$J$1))

Hide any columns you would like

Posting example file.
Inventory-Q.xls
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34902690
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.
0
 

Author Closing Comment

by:Massimo Scola
ID: 34987845
fantastic
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question