In reply to Aladin post..
I have used the formula you listed but changed the references etc to suit my
sheet, i have 4 sheets were date is linked with several formula, i was
pleased to see how it worked first time, however my problem is that it has
now slowed down the working of my sheet, E.g when i enter date into some of
the cells it takes 2 whole seconds before i can work in another cell.. i can
only put it down to the number of rows and columns i'm using? I have approx
1600 rows of data most of which are empty but do have formula spread over 10
columns. My aim is to list on a seperate sheet all the rows with data without
the blank rows. As i say everything works fine but sadly slow when entering
data. Any suggestions or ways around it?
"Aladin Akyurek" wrote:
>
> w1 (source)
>
> Insert 1 row before the data such that A2:D21 houses the data.
>
> In E1 enter: 0
> In E2 enter & copy down:
>
> =IF(C2<>"",LOOKUP(9.99999999999999E+307,$E$1:E1)+1,"")
>
> w2 (destination)
>
> In A1 enter:
>
> =LOOKUP(9.9999999999999E+307,Sheet1!E2:E21)
>
> In A2 enter & copy down:
>
> =IF(ROW()-ROW(A$2)+1<=$A$1,INDEX(Sheet1!$C$2:$C$21,MATCH(ROW()-ROW(A$2)+1,Sheet1!$E$2:$E$21)),"")
>
> Shooter Wrote:
> > My worksheet (w1) has values in cells A1:D20. All cells have a formula
> > or
> > link to another worksheet. Some of the cells are blank (but still have
> > a
> > formula in the cell). All of the cells change periodically as I change
> > the
> > values in other worksheets. In worksheet 2 (w2) A1:a20, I would like
> > to list
> > all of the values of w1 C1:c20 but without any blank rows. I would like
> > a
> > formula that will do this automatically. Thanks for your suggestions.
> > --
> > Shooter
>
>
> --
> Aladin Akyurek
> ------------------------------------------------------------------------
> Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
> View this thread: http://www.excelforum.com/showthread...hreadid=276804
>
>
Bookmarks