Very puzzling - how do I reference cells without the blank 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. Unfottunately, this is not possible by saying =A1 in the
first row and then logically saying go down 3 columns each time as if
to say [=A1 + "3 columns down"]. So how would I do this then? 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

 

[Bob Phillips]

=INDEX($A$1:$A$13,(ROW(A1)-1)*3+1)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Richard Buttrey]

One way would be to use Indirect. i.e with Table 1 in A1:B13
Start table 2 in A21 with

=INDIRECT("A"&(ROW()-20)*3-2)

and copy down

Copy A21 to B21 and substiture "A" with "B"

Adjust the row references as necessary for your conditions,

HTH

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. Unfottunately, this is not possible by saying =A1 in the
first row and then logically saying go down 3 columns each time as if
to say [=A1 + "3 columns down"]. So how would I do this then? 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

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Ken Wright]

Please don't post the same question separately to multiple groups - It
simply fragments answers, and means duplication of effort.

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

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

 

[ZMAN]

OK, I understand. Thank you for your response also!

Please don't post the same question separately to multiple groups - It
simply fragments answers, and means duplication of effort.

--
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. Unfottunately, this is not possible by saying =A1 in the
first row and then logically saying go down 3 columns each time as if
to say [=A1 + "3 columns down"]. So how would I do this then? 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

 

[Ken Wright]

No problem


OK, I understand. Thank you for your response also!

Please don't post the same question separately to multiple groups - It
simply fragments answers, and means duplication of effort.

--
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. Unfottunately, this is not possible by saying =A1 in the
first row and then logically saying go down 3 columns each time as if
to say [=A1 + "3 columns down"]. So how would I do this then? 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