Heelychat, the forum for heelys, the shoes with wheels - View topic

OK, so I am building a spread sheet using Excel 2003 (probably the only thing useful Microsoft has created Razz

)

Within this workbook I have a column of numbers with blanks.

I need a formula that will copy the list exactly how it is right now, and then eliminate the blanks so I can put it in a chart.

I would like to do it without macros if possible because this workbook has to go out to everyone in the world, and some people have really tight security on their computers.

Also, I already know that I can put a filter on it and eliminate all the blanks, but the column is updated daily and I would like to not have to worry about it, you know

Anyways, any help would be appreciated

P.S.

Currently I am using the following

=IF(ROWS(C$1:C4)<=COUNT(B$1:B$1000)+COUNTIF(B$1:B$1000,"*?"),INDEX(B$1:B$1000,SMALL(IF(B$1:B$1000<>"",ROW(B$1:B$1000)-ROW(B$1)+1),ROWS(C$1:C4))),"")

this works if I change all the B1000 to B12 lol. Anything more and it messes up... Sad

*********EDIT*********

I figured out my issue. I was getting the reference error because it was pointing to another cell with a reference error (having several thousand rows can do that to you Razz

)

Anyways, I was going to just click delete, but I figured that if anyone wanted it they can have it because trust me, the internet was near impossible to find a god formula

Joined: 15 Jun 2008 Posts: 1391 Location: UK

I'm pretty nifty on Excel 2003 and can usually get to do whatever I want so I'd be willing to have a crack at it. You can do stuff like protect the formula cells if you're worried about end-users bollocksing it up. Anyway, sounds like you're ok now, and I can't promise I'll be able to get it exactly how you want, but you know where I am if you need me to try matey Smile

heh, well I figured it out, but now my issue is that I wanted to put that data into a chart. The problem is, every time I add data to the one worksheet it is updating the the other work sheet which the new formula is referencing. That formula has to be copied into EVERY cell all the way down (as it is updated almost every 15 minutes with new information).

My ultimate goal was to put it in a graph, but there are only 2 possible ways to do so:

1. Hide the Cells:
They want me to hide every friggin' cell that I will not be using for the chart, meaning everything from the new data down. It is possible, but I have to reconfigure it daily which can be a pain and can cause for further issue

2. N/A errors:

Graphs won't graph a N/A error, but because i have a formula in every cell, I can't make it display N/A UNTIL it has a number, you know

So basically all that thinking and dicking around with Excel has made me realize that I can't have the cake and the icing, so I just have to update it daily.

BTW, I'll have to send it to you, its a pretty nifty booklet