Writing Nested IF Statements to refer to cell in another sheet if target cell is blank

katwalk · Dec 21, 2010

Excel experts and friends.I cannot find any posts to help with my problem. I export a spreadsheet on a monthly basis and copy to excel in various tabs. January numbers are sheet 1, Feb numbers are sheet 2, March, sheet 3 etc. There is a master file I use to derive metrics from the sheets. I am trying to create an IF Statement in a cell in the master file that says to show the latest information in the target cell, in this case sheet 12 (dec). If the cell is empty, next look a the same cell in sheet 11(nov), if Nov is blank as well then look in sheet 10 (Oct) etc all the way until Jan if all of those are empty or 0. Here is what I have managed to create for the Decmeber cell

=IF('Dec Calculations'!Q149=0,IF('Nov Calculations'!Q149=0,IF('Oct Calculations'!Q149=0,IF('Sep Calculations'!Q149=0,IF('Aug Calculations'!Q149=0,IF('Jul Calculations'!Q149=0,IF('Jun Calculations'!Q149=0,IF('May Calculations'!Q149=0,IF('Apr Calculations'!Q149=0,IF('Mar Calculations'!Q149=0,IF('Feb Calculations'!Q149=0,'Jan Calculations'!Q149)))))))))))

but it gives me an answer of "false".

Answering this question is my only wish this holiday season. I am willing to use any Excel Command, I am running Excel 2007. Windows Vista.

Smitty · Dec 21, 2010

Welcome to the Board!

I'd probably go with INDIRECT, which will let you reference the sheet name based on what's in the target cell.

Let's say you have a Data Validation drop-down for the month selection, then you can use something like this to return data from the relevant sheet:

Excel Workbook

A

B

1

Month Selection

Value in A1

2

Dec

125

Sheet1

Excel 2010

Cell Formulas
Range	Formula
B2	=INDIRECT("'"&A2&" Calculations'!A1")

HTH,

Gday Bruce · Dec 21, 2010

There are two issues with your IF statement.

The False answer is being given because there are no actions to perform when a value <> 0
Correct the IF argument to;
=IF('Dec Calculations'!Q149=0,IF('Nov Calculations'!Q149=0,IF('Oct Calculations'!Q149=0,IF('Sep Calculations'!Q149=0,IF('Aug Calculations'!Q149=0,IF('Jul Calculations'!Q149=0,IF('Jun Calculations'!Q149=0,IF('May Calculations'!Q149=0,IF('Apr Calculations'!Q149=0,IF('Mar Calculations'!Q149=0,IF('Feb Calculations'!Q149=0,'Jan Calculations'!Q149,'Feb Calculations'!Q149),'Mar Calculations'!Q149),'Apr Calculations'!Q149),'May Calculations'!Q149),'Jun Calculations'!Q149),'Jul Calculations'!Q149),'Aug Calculations'!Q149),'Sep Calculations'!Q149),'Oct Calculations'!Q149),'Nov Calculations'!Q149),'Dec Calculations'!Q149)

While this solves that issue it raises a greater problem.

Excel will not accept more than (i think) 7 nested IF statements so the formula will cause a calculation error.
To solve your initial query, create a function along the lines of ;

Code:

Function Latest_Value(cell_Address)

For Each wsheet In Array("Dec", "Nov", "Oct", "Sep", "Aug", "Jul", "Jun", "May", "Apr", "Mar", "Feb", "Jan")
    If Sheets(wsheet).Range(cell_Address).Value <> 0 Then
        Latest_Value = Sheets(wsheet).Range(cell_Address).Value
        Exit Function
    End If
Next wsheet

End Function

Your formula then becomes =Latest_Value("Q149"), or if the answer is in the same cell (on a different sheet), =Latest_Value(ADDRESS(ROW(),COLUMN()) can be copied to all relevant cells.

Writing Nested IF Statements to refer to cell in another sheet if target cell is blank

katwalk

New Member

Excel Facts

Smitty

Legend

Gday Bruce

Board Regular

Similar threads

Forum statistics

Share this page

Writing Nested IF Statements to refer to cell in another sheet if target cell is blank

katwalk

New Member

Excel Facts

Smitty

Legend

Gday Bruce

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock