Referencing a table with blank rows without creating extra rows ?

[ZMAN]

Hi,
here is something that should be much simpler to accomplish but somehow
is very difficult to figure out.

Basically, for simplicity's sake, let's say I have a simple spreadsheet
shown in TABLE 1 below which has Mon-Fri in one column and dollar of
sales in the second column. But the catch is that each row is
separated by two blank rows. So say I would like to reference these
cells below but without skipping two rows and instead have it one after
another (Monday in first row, Tuesday in second, etc.) just like in
TABLE 2. Unfortunately, this is not possible by saying =A1 in the
first row and then logically saying go down 3 columns each time as if
to tell Excel [= A1 + "go down 3 columns"]. So how would I do this?
It's very puzzling because there should be a simple way to do it. I
thought that maybe OFFSET should be used but I couldn't figure it out
yet.


---TABLE 1---
Monday $100


Tuesday $80


Wednesday $120


Thursday $90


Friday $110





---TABLE 2---
Monday $100
Tuesday $80
Wednesday $120
Thursday $90
Friday $110

 

[Guest]

Try something like this:

With
Table_1 in cells A1:B20

Table_2
A25: =INDEX($A$1:$B$20,ROWS($25:25)*3-2,COLUMNS($A:A))
Copy that formula down and right thru B29

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Ken Wright]

=OFFSET($A$1,ROW()*3-3,0)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Ryan.Chowdhury]

Offset or Index would work as well.

Assuming the entire source Table 1 is named "SourceTable", you need a
mapping as follows:
1=1
4=2
7=3
10=4

The function that would accomplish this would be (3n-2) where n goes
from 1 to 65,536. Now the question is how do you generate "n"s. You
have two options, create a "helper column" next to your output table
that increments by 1 or use the row() formula.

Method 1: assumes you have a "helper columns" beginning in B1
=index(SourceTable,3*$B1-2)

Method 2: assumes your output table starts in C1
=index(SourceTable,3*row()-2)

 

[Ken Wright]

Assuming you start in Row 1 with this of course, else make the necessary
adjustments.

Regards
Ken....................

=OFFSET($A$1,ROW()*3-3,0)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

Hi,
here is something that should be much simpler to accomplish but somehow
is very difficult to figure out.

Basically, for simplicity's sake, let's say I have a simple spreadsheet
shown in TABLE 1 below which has Mon-Fri in one column and dollar of
sales in the second column. But the catch is that each row is
separated by two blank rows. So say I would like to reference these
cells below but without skipping two rows and instead have it one after
another (Monday in first row, Tuesday in second, etc.) just like in
TABLE 2. Unfortunately, this is not possible by saying =A1 in the
first row and then logically saying go down 3 columns each time as if
to tell Excel [= A1 + "go down 3 columns"]. So how would I do this?
It's very puzzling because there should be a simple way to do it. I
thought that maybe OFFSET should be used but I couldn't figure it out
yet.


---TABLE 1---
Monday $100


Tuesday $80


Wednesday $120


Thursday $90


Friday $110





---TABLE 2---
Monday $100
Tuesday $80
Wednesday $120
Thursday $90
Friday $110