|
Very puzzling - how do I reference cells without the blank rows???
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 |
Very puzzling - how do I reference cells without the blank rows???
=INDEX($A$1:$A$13,(ROW(A1)-1)*3+1)
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "ZMAN" wrote in message oups.com... 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 |
Very puzzling - how do I reference cells without the blank rows???
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 On 28 Aug 2006 05:13:18 -0700, "ZMAN" wrote: 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 __________________________ |
Very puzzling - how do I reference cells without the blank rows???
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" wrote in message oups.com... 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 |
Very puzzling - how do I reference cells without the blank rows???
OK, I understand. Thank you for your response also!
Ken Wright wrote: 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" wrote in message oups.com... 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 |
Very puzzling - how do I reference cells without the blank rows???
No problem :-)
"ZMAN" wrote in message ups.com... OK, I understand. Thank you for your response also! Ken Wright wrote: 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" wrote in message oups.com... 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 |
| All times are GMT +1. The time now is 02:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com