IF and COUNTA formula problem

[dh42891]

I've got a formula that references another spreadsheet. Basically, it goes down a row starting at, say, D3 and checks to see if anything has been entered. If it has, I want some text to show up; if D3 is empty, I want it to check C3, then B3, etc.

Normally D3, C3, etc. will have an 'x' in it, but occassionly someone misses the theme and types in their intials. I've been using something similar to

=IF(D3="x","D3 Complete", IF(C3="x", "C3 Complete", ""))

Only it's longer. However, when I add the COUNTA function to account for initials or other words, I can't get excel to accept the last COUNTA. Meaning, I get a 'bad formula' error and excel highlights the last instance of COUNTA. The actual formula I'm using is below. Does anyone see a problem with it? I can't seem to find an error, and if I retype the formula without the COUNTA, it works fine. Thanks!

=if(COUNTA('[lds_model_setup_progress_FRENCH_BROAD.xls]Sandymush Creek'!K3=1),"(" & '[lds_model_setup_progress_FRENCH_BROAD.xls]Sandymush Creek'!L3 & ")", IF(COUNTA('[lds_model_setup_progress_FRENCH_BROAD.xls]Sandymush Creek'!J3=1),"Hydramax completed", IF(COUNTA('[lds_model_setup_progress_FRENCH_BROAD.xls]Sandymush Creek'!I3=1),"Hydramax started", IF(COUNTA('[lds_model_setup_progress_FRENCH_BROAD.xls]Sandymush Creek'!H3=1), "Takeoffs complete", IF(COUNTA('[lds_model_setup_progress_FRENCH_BROAD.xls]Sandymush Creek'!G3=1), "n-values complete", IF(COUNTA('[lds_model_setup_progress_FRENCH_BROAD.xls]Sandymush Creek'!F3=1), "Structures complete", IF(COUNTA('[lds_model_setup_progress_FRENCH_BROAD.xls]Sandymush Creek'!E3=1), "XSs cut", IF(COUNTA('[lds_model_setup_progress_FRENCH_BROAD.xls]Sandymush Creek'!D3 =1), "CL exported","")))))))) & IF(COUNTA('[lds_model_setup_progress_FRENCH_BROAD.xls]Sandymush Creek'!O3=1)," Check GIS notes!", "")

The COUNTA(...!D3) function is the instance that Excel does not like. The very last COUNTA instance checks a 'notes' section and attaches a warning. That part seems to be working. Thanks again, and sorry if this isn't very clear.

dylan

 

[bluedragon2]

=IF(ISBLANK(D3),IF(ISBLANK(C3),"","C3 Complete","D3 Complete"))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[dh42891]

I knew there had to be a better way, but I've never heard of that formula. Thanks, I see what I can do with that one.

dylan

 

[AnotherHiggins]

The reason Excel is balking is because there is a limit of 7 nested functions. Lookup Worksheet and workbook specifications in Excel's help file for more info.

With all due respect to [blue]Blue[/blue], his formula is incorrect - it has too many arguments in it. Something like this will work:
[COLOR=blue white]=if(not(IsBlank(d3)),"D3 Complete",if(not(IsBlank(C3)),"C3 Complete"))[/color]

Or, if you don't need to account for numbers as well as text, then you can just use:
[COLOR=blue white]=if(IsText(D3),"D3 Complete", If(IsText(C3),"C3 Complete"))[/color]

But neither of those methods will get you around Excel's limit of 7 nested IFs.

To get around that, you can use something like this:
=IF(ISBLANK(D3),"D3 Not Complete","") & IF(ISBLANK(C3),"C3 Not Complete","")

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[bluedragon2]

Your right John:

=IF(ISBLANK(D3),IF(ISBLANK(C3),"","C3 Complete"),"D3 Complete")



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[dh42891]

Well, I just came back to say I was having the same problem with ISBLANK. I worked out what Blue was getting at, but it looks like I'll try what you're suggesting. Is there a more programmatically-effecient method of doing this? Is that why there's a limit of just 7 nested IFs? Seems like there must be if Excels limits it at what to me seems to be a low number.

Regardless, thanks for the suggestion, I'll work with that for a bit as I can.

dylan

 

[AnotherHiggins]

->Is there a more programmatically-effecient method of doing this?
Probably, but it is hard to say without knowing a little more about your sheet structure.

From the looks of the example in your first post, I assume that the "x" or initials in row 3 will always be contiguous - that is to say there will never be blank cells between two marked cells. It also looks like D3 will only be marked if C3 is also marked, and E3 will only be marked if both C3 & D3 are marked, etc.

If all those assumptions are correct, then you could use something like this:

[COLOR=blue white]=OFFSET(C4,0,COUNTA(C3:IV3))[/color]

For your example, that should look something like this:
[COLOR=blue white]=OFFSET('[lds_model_setup_progress_FRENCH_BROAD.xls]Sandymush Creek'!L3,0,COUNTA(C3:IV3))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[dh42891]

John,

Your assumptions are generally correct. There is the rare occasion where, say, C3 will not be filled in, but A3, B3, D3, and E3 will be. But yes, 99% of the time D3 won’t be filled in unless C3 is, and C3 won’t be filled in unless B3 is, and so on.

I’m not entirely sure what OFFSET is doing. Briefly looking at the Help only confirmed my ignorance. It’s returning the value(s) of a cell(s) that is so many rows and columns from the reference cell? I’m obviously missing something because I don’t quite understand how to use OFFSET for what I’m trying to do. Thanks for the help and patience; I’m a novice Excel user making the jump to the pseudo-intermediate user.

dylan

 

[AnotherHiggins]

-> It’s returning the value(s) of a cell(s) that is so many rows and columns from the reference cell?

'zactly.

The reference cell is like an anchor point. The next two arguments are rows down and columns over.

In this case, you want to go zero rows down if your anchor is on the same row and the values you want to return.

In the third argument, columns over, I am counting how many x's (or initials or numbers) are on the current sheet in row C. If there are 3 X's, then count over 3 columns from the anchor.

[attn]BUT[/attn]

Those 'rare occasions' where D3 is filled in but C3 is not kind of blows that whole OFFSET approach. It is the exceptions that you have to look out for.

If you can explain, in plain english, the logic behind what you are trying to accomplish - including caveats - then someone here can almost definitely help you come up with a better formula structure than umpteen nested IFs.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[dh42891]

Essentially, we have two teams working on a project. Team #1 does the set-up and Team #2 does the real work- creating digital models. The set-up has a lot of little steps so I created a spreadsheet for them to go down a row and check off each step they do. Once the setup is complete, Team #2 takes over. Team #2's work also has a lot of small steps. To help separate and organize the process, we have a second spreadsheet for them.

I want to report the set-up progress from Team #1 to the modeling progress spreadsheet. I have a working spreadsheet that reports when the set-up is complete, but I would like to be able to report which step Team #1 is on because before too long, Team #1 will move on to something else and Team #2 needs to be able to quickly see what has already been done, without having to open other spreadsheet.

So, there is a cell in the second spreadsheet that should display some text based on the furthest cell in row 3 that has a mark in it. There is a tiny chance that the row will not be marked consecutively (meaning possibly D3 will be marked, but not C3), but in almost all occasions, A3 will be marked, then B3, then C3, etc. Honestly, it most cases they will all be filled in at once.

At the beginning of the thread, I was using embedded IF(COUNTA...)'s but need more than 7 embedded IF statements.

So, any ideas on how to do this effeciently? Thanks a lot!

dylan