Force cell references to go in order when dealing with hidden rows

[jh]

Greetings,


Hopefully there is a secret to this. I have a spreadsheet where I have hidden rows based on criteria. For example Columns 7920,31520,34153 etc. are shown, the rest are not. I am trying to link another column of data on a different spreadsheet but when I copy the formula, instead of it being ='Sheet1'!B1 and then 'Sheet1'!B2 and so on. It goes from ='Sheet1'!B1 to then ='Sheet1'!B23600 because that is the difference between the shown rows.


Is there anyway to force the formula to go from B1,B2,B3,B4 regardless of what row I'm actually on?


Thank you very much.


Signed,

JH

 

[SirJB7]

Hi, jh!

Better later than never... I just happen to read this topic and the first thing that caught my attention was "...Columns 7920,31520,34153". Any trouble with 7920, but 31520 and 34153 can't be column numbers. Let assume that their row numbers.

Even you have hidden rows and despite they contain data or not, they still should be referenced by their actual row and column numbers, not by their displayed or not condition.

So I'm afraid that any reference on same or other worksheets within same workbook or on other workbooks, couldn't be transformed to displayed order row numbers.

And of course the same considerations are valid for column numbers.

Regards!

PS: Just in case, Excel 2007+ has 1048576 rows and 16384 columns.

 

[Luke M]

Here's one idea that uses a helper column. One the worksheet with hidden rows, have a column with this formula:

=SUBTOTAL(103,$A2)*ROW()

Where A2 is the cell with all your data. Now, assuming data you want is in col A, and helper col is col D, formula to extract from visible rows only:

=INDEX(A:A,SMALL(D:D,ROW(A2)))

copy down as far as needed. If you want to suppress error messages, can do:

=IF(ROWS(A$2:A2)>COUNTIF(D:D,">0"),"",INDEX(A:A,SMALL(D:D,ROW(A2))))

 

[SirJB7]

@Luke M

Hi!

Nice approach, I never think about SUBTOTAL function. Good catch.

Regards!

 

[Luke M]

@SirJB7,

No worries mate. I'll consider us even after you found out about detecting a paste to a user form. =P

 

[SirJB7]

@Luke M

Hi!

Touché, monsieur... but what more exciting (if we discard women, Carlsberg, and a lot more things) than trying to look for odds when we're even and for evens when we're odd?

Regards!