Excel Question - never ran into this before

[Controller]

I have a large spreadsheet (about 17,000 rows of data).

 

I need to hide the rows that have no value in a certain cell (some rows have a sell price in the cell in column C, some don't - I want to hide the rows that have no sell price) The reason is: if there is no value, then another cell with a formula in it that refers to the value in col. C shows an error. So I either need to hide the row, or at least put a zero in the cell in col. C.

 

I tried using an IF statement in a blank column, looking at the value in col. C and if it was not zero, copy it , otherwise put a zero. When it was copied down all 17,000 rows, I would just copy & paste special-values.

 

But the IF statment only worked on the first 1300 or so rows, then it stopped working. Is there a limitation? Looked it up in my Excel Bible and it doesn't mention a limit.

 

Thanks Eggheads.

[gbpfan1231]

I just created a simple spreadssheet with the same number in column A down 25,000 times and then in column B did an If statement saying if A1=X then YES else NO and copied it down 25,000 rows and it wokred. I went into cell A18,000 and changed the number and B18000 said NO.

 

Feel free to PM me and I will look at your spreadsheet.

[Gonkis]

The Huddle... bringing Raider and Packer fan together!!!

[jelap1]

I think controller is talking about nested if statements. The limit in Excel is 7. I think you need to use a VLOOKUP to do what you want.

 

Check out the example here: http://www.cpearson.com/excel/nested.htm

 

or here: http://spreadsheetpage.com/index.php/tip/a...d_if_functions/

[westvirginia]

I'm guessing there's a formatting issue somewhere... I too can look at it for you but that would probably be a violation worthy of you getting fired, so :shurg:

[Controller]

GBP thanks for testing my theory. Sounds like you proved that you can have as many IF stmts as you want.

 

Therefore, I think WV may be right - there may be some funky formatting issues. I will see if I can find the problem that way.

 

In the meantime, I came up with a "low tech" solution - I sorted the data on the column in question, which put all of the rows with no data in col. C, then hid all those rows, and re-sorted. That kinda worked.

 

Any other ideas always appreciated.

 

BTW, when I was 8 years old I asked Santa Claus for a football uniform for Christmas...a Green Bay Packers uniform. Just about killed my Dad, who was an original Raiders season ticket holder from day 1. If I hadn't mentioned it here, it woulda been a perfect false clue for Mystery Huddler some day.

[Big Country]

Probably formatting from the description as it sounds like you are not using a nested IF statement.

 

The other option is to set up some form of error catching on the formula that uses the C column for it's formula so that if the C column is blank, the formula returns a set value (0 or whatever is appropriate) and if it has a value it performs the calculation.

 

Another option is to use the find/replace function on column C and replace all of the blank cells with 0

 

I guess it really depends on if your preference is to display the data (one of the solutions above) or to hide any row with the blank value in column C (your current low tech solution, though there may be code out there to automate it)

[Controller]

Another option is to use the find/replace function on column C and replace all of the blank cells with 0

 

Two questions about this:

 

1) Is there a way to do a find/replace isolating just one column?

 

2) How do you search for blank cells? I tried this by hitting the space bar once and searching for that, and it didn't work.

[Big Country]

Two questions about this:

 

1) Is there a way to do a find/replace isolating just one column?

 

2) How do you search for blank cells? I tried this by hitting the space bar once and searching for that, and it didn't work.

 

1. Yes, just highlight the area you want to do find/replace on. now, as the default value is a blank, you would want to highlight your work area only otherwise all of the blanks going down 65000 lines would be changed as well. just highlight the C1 cell, drag the scroll bar to the bottom of your work area, press shift and click on the C15000 cell or whatever is the last row of your dataset (faster than clicking and dropping, and you may already know to do this, just covering all bases)

 

2. Just don't put anything in the find criteria. Leave the cell completely blank. In the replace tab, put the 0 in the replace with entry and voila.

Edited October 16, 2008 by Big Country

[Gonkis]

BTW, when I was 8 years old I asked Santa Claus for a football uniform for Christmas...a Green Bay Packers uniform. Just about killed my Dad, who was an original Raiders season ticket holder from day 1. If I hadn't mentioned it here, it woulda been a perfect false clue for Mystery Huddler some day.

 

Great story. My Mom and Dad were raised as hardcore Buckeye fans... naturally, I'm a Wolverine fan.