Hello, I am in Excel 2003 and have a worksheet consisting of data in row 1
and row 2 that go together. An example would be
A1 (employee name) A2 (Employee address A3 (Employee city/state/zip)
B1 (employee SSN) B2 (Wages owed to employee) B3 (Interest owed to employee)

Each of these cells are linked to another worksheet that the data is entered
into but each person only has one row of data. The worksheet that I'm
working in is a protected worksheet where it retrieves this data from the
worksheet and displays it in a report view.

My problem: I have space for 134 names now (total 268 rows as two rows are
used for one employee) and need to add 166 additional names to the
worksheet. That means 332 rows that have formulas increasing the link to
worksheet 1 by 1.

Example: a1 (=#1workbook!A1) a2 ((=#1workbook!A2) a3 (=#1workbook!A3) b1
(=#1workbook!A4) b2(=#1workbook!A5) b3(=#1workbook!A6) ** next group
c1 (=#1workbook!b1) c2 ((=#1workbook!b2) c3
(=#1workbook!b3) d1 (=#1workbook!b4) d2(=#1workbook!b5)
d3(=#1workbook!b6) and continues on.

Can anyone help me with a macro to add copy the values in the last example
in both rows and paste them with an automatic increase of the values by
one....

I have the worksheet unprotected and for some reason can't get the paste
function to work either by keyboard or by pull down, any ideas on this????

Hope this is clear enough if not let me know and thanks in advance
Bob Reynolds

Bob,

Something is wrong about your description. If you are actually using two columns to display each of
the rows from your table, you would have run out of columns (Excel only has 256 columns - so you
could only display 128 sets of data). I think you mean that you have three columns, using two rows
to show each of the rows from your table.

If that is the case, put this into cell A1:

=INDEX('#1workbook'!$A$1:$F$400,INT((ROW()-ROW($A$1))/2)+1,MOD((ROW()-ROW($A$1)),2)*3+COLUMN()-COLUMN($A$1)+1)

and copy to B1:C1, then copy A1:C1 down as far as you need.

If I'm wrong (which I am often) put this formula into cell A1

=INDEX('#1workbook'!$A$1:$F$400,INT((COLUMN()-COLUMN($A$1))/2)+1,MOD((COLUMN()-COLUMN($A$1)),2)*3+ROW()-ROW($A$1)+1)

and copy to A2:A3, then copy A1:A3 across until you run out of columns.

HTH,
Bernie
MS Excel MVP


"rjr" wrote in message
.. .
Hello, I am in Excel 2003 and have a worksheet consisting of data in row 1 and row 2 that go
together. An example would be
A1 (employee name) A2 (Employee address A3 (Employee city/state/zip)
B1 (employee SSN) B2 (Wages owed to employee) B3 (Interest owed to employee)

Each of these cells are linked to another worksheet that the data is entered into but each person
only has one row of data. The worksheet that I'm working in is a protected worksheet where it
retrieves this data from the worksheet and displays it in a report view.

My problem: I have space for 134 names now (total 268 rows as two rows are used for one employee)
and need to add 166 additional names to the worksheet. That means 332 rows that have formulas
increasing the link to worksheet 1 by 1.

Example: a1 (=#1workbook!A1) a2 ((=#1workbook!A2) a3 (=#1workbook!A3) b1 (=#1workbook!A4)
b2(=#1workbook!A5) b3(=#1workbook!A6) ** next group
c1 (=#1workbook!b1) c2 ((=#1workbook!b2) c3 (=#1workbook!b3) d1
(=#1workbook!b4) d2(=#1workbook!b5) d3(=#1workbook!b6) and continues on.

Can anyone help me with a macro to add copy the values in the last example in both rows and paste
them with an automatic increase of the values by one....

I have the worksheet unprotected and for some reason can't get the paste function to work either
by keyboard or by pull down, any ideas on this????

Hope this is clear enough if not let me know and thanks in advance
Bob Reynolds

All of my examples show rows of data and not columns. On the data sheet I
have one column for each piece of information entered on one row for each
person. On the finished worksheet I have that information displayed in two
rows with formulas relating that back to them, if this helpls.

Bob

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Bob,

Something is wrong about your description. If you are actually using two
columns to display each of the rows from your table, you would have run
out of columns (Excel only has 256 columns - so you could only display 128
sets of data). I think you mean that you have three columns, using two
rows to show each of the rows from your table.

If that is the case, put this into cell A1:

=INDEX('#1workbook'!$A$1:$F$400,INT((ROW()-ROW($A$1))/2)+1,MOD((ROW()-ROW($A$1)),2)*3+COLUMN()-COLUMN($A$1)+1)

and copy to B1:C1, then copy A1:C1 down as far as you need.

If I'm wrong (which I am often) put this formula into cell A1

=INDEX('#1workbook'!$A$1:$F$400,INT((COLUMN()-COLUMN($A$1))/2)+1,MOD((COLUMN()-COLUMN($A$1)),2)*3+ROW()-ROW($A$1)+1)

and copy to A2:A3, then copy A1:A3 across until you run out of columns.

HTH,
Bernie
MS Excel MVP


"rjr" wrote in message
.. .
Hello, I am in Excel 2003 and have a worksheet consisting of data in row
1 and row 2 that go together. An example would be
A1 (employee name) A2 (Employee address A3 (Employee city/state/zip)
B1 (employee SSN) B2 (Wages owed to employee) B3 (Interest owed to
employee)

Each of these cells are linked to another worksheet that the data is
entered into but each person only has one row of data. The worksheet that
I'm working in is a protected worksheet where it retrieves this data from
the worksheet and displays it in a report view.

My problem: I have space for 134 names now (total 268 rows as two rows
are used for one employee) and need to add 166 additional names to the
worksheet. That means 332 rows that have formulas increasing the link to
worksheet 1 by 1.

Example: a1 (=#1workbook!A1) a2 ((=#1workbook!A2) a3 (=#1workbook!A3)
b1 (=#1workbook!A4) b2(=#1workbook!A5) b3(=#1workbook!A6) ** next
group
c1 (=#1workbook!b1) c2 ((=#1workbook!b2) c3
(=#1workbook!b3) d1 (=#1workbook!b4) d2(=#1workbook!b5)
d3(=#1workbook!b6) and continues on.

Can anyone help me with a macro to add copy the values in the last
example in both rows and paste them with an automatic increase of the
values by one....

I have the worksheet unprotected and for some reason can't get the paste
function to work either by keyboard or by pull down, any ideas on
this????

Hope this is clear enough if not let me know and thanks in advance
Bob Reynolds

after fully reading the post I'm going to try and see. I think one of them
will work and post back. Thanks for the help.
BOB

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Bob,

Something is wrong about your description. If you are actually using two
columns to display each of the rows from your table, you would have run
out of columns (Excel only has 256 columns - so you could only display 128
sets of data). I think you mean that you have three columns, using two
rows to show each of the rows from your table.

If that is the case, put this into cell A1:

=INDEX('#1workbook'!$A$1:$F$400,INT((ROW()-ROW($A$1))/2)+1,MOD((ROW()-ROW($A$1)),2)*3+COLUMN()-COLUMN($A$1)+1)

and copy to B1:C1, then copy A1:C1 down as far as you need.

If I'm wrong (which I am often) put this formula into cell A1

=INDEX('#1workbook'!$A$1:$F$400,INT((COLUMN()-COLUMN($A$1))/2)+1,MOD((COLUMN()-COLUMN($A$1)),2)*3+ROW()-ROW($A$1)+1)

and copy to A2:A3, then copy A1:A3 across until you run out of columns.

HTH,
Bernie
MS Excel MVP


"rjr" wrote in message
.. .
Hello, I am in Excel 2003 and have a worksheet consisting of data in row
1 and row 2 that go together. An example would be
A1 (employee name) A2 (Employee address A3 (Employee city/state/zip)
B1 (employee SSN) B2 (Wages owed to employee) B3 (Interest owed to
employee)

Each of these cells are linked to another worksheet that the data is
entered into but each person only has one row of data. The worksheet that
I'm working in is a protected worksheet where it retrieves this data from
the worksheet and displays it in a report view.

My problem: I have space for 134 names now (total 268 rows as two rows
are used for one employee) and need to add 166 additional names to the
worksheet. That means 332 rows that have formulas increasing the link to
worksheet 1 by 1.

Example: a1 (=#1workbook!A1) a2 ((=#1workbook!A2) a3 (=#1workbook!A3)
b1 (=#1workbook!A4) b2(=#1workbook!A5) b3(=#1workbook!A6) ** next
group
c1 (=#1workbook!b1) c2 ((=#1workbook!b2) c3
(=#1workbook!b3) d1 (=#1workbook!b4) d2(=#1workbook!b5)
d3(=#1workbook!b6) and continues on.

Can anyone help me with a macro to add copy the values in the last
example in both rows and paste them with an automatic increase of the
values by one....

I have the worksheet unprotected and for some reason can't get the paste
function to work either by keyboard or by pull down, any ideas on
this????

Hope this is clear enough if not let me know and thanks in advance
Bob Reynolds

This is the formula that I came up with following your example. A little
different but got me on the righ path.
This displays in the final worksheet from A1 to J1 data and then goes down
one line and enters data from col 6-7-&8 from the data sheet and places it
in row 2 on the final worksheet.

Now is there a way to have this formual in rows 1 and 2 and then make them
selfupdating so that when I paste it into Rows 3 and 4 the data from row 2
on the data source will be displayed and so on and so on???

I've tried to add 1+1 like your original assistance suggested but I would
have to go through that for each one and very time consuming. I would like
to have it update automatically as I paste it. Hope this is clear enough.

In cell A1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,2,3,1) --
In cell C1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,2,5,1) --
In Cell F1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,1,9,1) --
In Cell G1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,1,10,1) --
In Cell H1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,1,17,1) --
Inc Cell I1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,1,19,1) --
In Cell J1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,1,20,1) --
In Cell C2 =INDEX('DataSheet & Calcs'!$A$13:$T$422,1,6,1) --
In Cell D2 =INDEX('DataSheet & Calcs'!$A$13:$T$422,1,7,1) --
In Cell E2 =INDEX('DataSheet & Calcs'!$A$13:$T$422,1,8,1)

Thanks so much.
Bob Reynolds


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Bob,

Something is wrong about your description. If you are actually using two
columns to display each of the rows from your table, you would have run
out of columns (Excel only has 256 columns - so you could only display 128
sets of data). I think you mean that you have three columns, using two
rows to show each of the rows from your table.

If that is the case, put this into cell A1:

=INDEX('#1workbook'!$A$1:$F$400,INT((ROW()-ROW($A$1))/2)+1,MOD((ROW()-ROW($A$1)),2)*3+COLUMN()-COLUMN($A$1)+1)

and copy to B1:C1, then copy A1:C1 down as far as you need.

If I'm wrong (which I am often) put this formula into cell A1

=INDEX('#1workbook'!$A$1:$F$400,INT((COLUMN()-COLUMN($A$1))/2)+1,MOD((COLUMN()-COLUMN($A$1)),2)*3+ROW()-ROW($A$1)+1)

and copy to A2:A3, then copy A1:A3 across until you run out of columns.

HTH,
Bernie
MS Excel MVP


"rjr" wrote in message
.. .
Hello, I am in Excel 2003 and have a worksheet consisting of data in row
1 and row 2 that go together. An example would be
A1 (employee name) A2 (Employee address A3 (Employee city/state/zip)
B1 (employee SSN) B2 (Wages owed to employee) B3 (Interest owed to
employee)

Each of these cells are linked to another worksheet that the data is
entered into but each person only has one row of data. The worksheet that
I'm working in is a protected worksheet where it retrieves this data from
the worksheet and displays it in a report view.

My problem: I have space for 134 names now (total 268 rows as two rows
are used for one employee) and need to add 166 additional names to the
worksheet. That means 332 rows that have formulas increasing the link to
worksheet 1 by 1.

Example: a1 (=#1workbook!A1) a2 ((=#1workbook!A2) a3 (=#1workbook!A3)
b1 (=#1workbook!A4) b2(=#1workbook!A5) b3(=#1workbook!A6) ** next
group
c1 (=#1workbook!b1) c2 ((=#1workbook!b2) c3
(=#1workbook!b3) d1 (=#1workbook!b4) d2(=#1workbook!b5)
d3(=#1workbook!b6) and continues on.

Can anyone help me with a macro to add copy the values in the last
example in both rows and paste them with an automatic increase of the
values by one....

I have the worksheet unprotected and for some reason can't get the paste
function to work either by keyboard or by pull down, any ideas on
this????

Hope this is clear enough if not let me know and thanks in advance
Bob Reynolds

Bob,

A1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,INT((ROW()-ROW())/2)+2,3,1)
C1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,INT((ROW()-ROW($A$1))/2)+2,5,1)
F1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,INT((ROW()-ROW($A$1))/2)+1,9,1)
G1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,INT((ROW()-ROW($A$1))/2)+1,10,1)
H1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,INT((ROW()-ROW($A$1))/2)+1,17,1)
I1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,INT((ROW()-ROW($A$1))/2)+1,19,1)
J1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,INT((ROW()-ROW($A$1))/2)+1,20,1)
C2 =INDEX('DataSheet & Calcs'!$A$13:$T$422,INT((ROW()-ROW($A$1))/2)+1,6,1)
D2 =INDEX('DataSheet & Calcs'!$A$13:$T$422,INT((ROW()-ROW($A$1))/2)+1,7,1)
E2 =INDEX('DataSheet & Calcs'!$A$13:$T$422,INT((ROW()-ROW($A$1))/2)+1,8,1)

Then copy A1:J2 and paste down to A3:J8:18.... At least, I think.. The above should at least help
you with the pattern that you want...

HTH,
Bernie
MS Excel MVP


"rjr" wrote in message
...
This is the formula that I came up with following your example. A little different but got me on
the righ path.
This displays in the final worksheet from A1 to J1 data and then goes down one line and enters
data from col 6-7-&8 from the data sheet and places it in row 2 on the final worksheet.

Now is there a way to have this formual in rows 1 and 2 and then make them selfupdating so that
when I paste it into Rows 3 and 4 the data from row 2 on the data source will be displayed and so
on and so on???

I've tried to add 1+1 like your original assistance suggested but I would have to go through that
for each one and very time consuming. I would like to have it update automatically as I paste it.
Hope this is clear enough.

In cell A1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,2,3,1) --
In cell C1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,2,5,1) --
In Cell F1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,1,9,1) --
In Cell G1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,1,10,1) --
In Cell H1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,1,17,1) --
Inc Cell I1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,1,19,1) --
In Cell J1 =INDEX('DataSheet & Calcs'!$A$13:$T$422,1,20,1) --
In Cell C2 =INDEX('DataSheet & Calcs'!$A$13:$T$422,1,6,1) --
In Cell D2 =INDEX('DataSheet & Calcs'!$A$13:$T$422,1,7,1) --
In Cell E2 =INDEX('DataSheet & Calcs'!$A$13:$T$422,1,8,1)

Thanks so much.
Bob Reynolds


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Bob,

Something is wrong about your description. If you are actually using two columns to display each
of the rows from your table, you would have run out of columns (Excel only has 256 columns - so
you could only display 128 sets of data). I think you mean that you have three columns, using
two rows to show each of the rows from your table.

If that is the case, put this into cell A1:

=INDEX('#1workbook'!$A$1:$F$400,INT((ROW()-ROW($A$1))/2)+1,MOD((ROW()-ROW($A$1)),2)*3+COLUMN()-COLUMN($A$1)+1)

and copy to B1:C1, then copy A1:C1 down as far as you need.

If I'm wrong (which I am often) put this formula into cell A1

=INDEX('#1workbook'!$A$1:$F$400,INT((COLUMN()-COLUMN($A$1))/2)+1,MOD((COLUMN()-COLUMN($A$1)),2)*3+ROW()-ROW($A$1)+1)

and copy to A2:A3, then copy A1:A3 across until you run out of columns.

HTH,
Bernie
MS Excel MVP


"rjr" wrote in message
.. .
Hello, I am in Excel 2003 and have a worksheet consisting of data in row 1 and row 2 that go
together. An example would be
A1 (employee name) A2 (Employee address A3 (Employee city/state/zip)
B1 (employee SSN) B2 (Wages owed to employee) B3 (Interest owed to employee)

Each of these cells are linked to another worksheet that the data is entered into but each
person only has one row of data. The worksheet that I'm working in is a protected worksheet
where it retrieves this data from the worksheet and displays it in a report view.

My problem: I have space for 134 names now (total 268 rows as two rows are used for one
employee) and need to add 166 additional names to the worksheet. That means 332 rows that have
formulas increasing the link to worksheet 1 by 1.

Example: a1 (=#1workbook!A1) a2 ((=#1workbook!A2) a3 (=#1workbook!A3) b1 (=#1workbook!A4)
b2(=#1workbook!A5) b3(=#1workbook!A6) ** next group
c1 (=#1workbook!b1) c2 ((=#1workbook!b2) c3 (=#1workbook!b3) d1
(=#1workbook!b4) d2(=#1workbook!b5) d3(=#1workbook!b6) and continues on.

Can anyone help me with a macro to add copy the values in the last example in both rows and
paste them with an automatic increase of the values by one....

I have the worksheet unprotected and for some reason can't get the paste function to work either
by keyboard or by pull down, any ideas on this????

Hope this is clear enough if not let me know and thanks in advance
Bob Reynolds