I am trying to automate a process of pulling data contained in a table in a
pdf file into an Excel file. I found a tool to convert the pdf to a Word doc
and from that I can copy/paste into a worksheet. In the spreadsheet, I have
a second worksheet that uses formulas to contruct a table in the format I
need but I am having a problem copying the formulas to subsequent rows.

The source data (the worksheet into which I paste the source data) has the
information for one logical record spread accross two rows. For example,
cell A1 contains a team name while cells B1 through B26 contain various
scores. In the resulting worksheet I want to have each set of team data on a
single row so I use formulas like the following in the summary worksheet:
A1 - =Source!A1
A2 - =Source!B1
A3 - =Source!B2
.....
A27 - =Source!B26

That works fine, but if I then copy the formulas in row A of the summary
worksheet to row B they become values like:
B1 = Source!B2 (I want C2)
B2 - Source!C1 (I want D1)
etc.

How can I accomplish this? There will be 125 rows and that is way to much
typing for me. I suspect the answer is a macro. I've never written xcel
macros but I am comfortable with regular VB.

Any suggestions/examples appreciated.

Wayne

See example http://cjoint.com/?ctxZrBvpFB with formulas based on INDEX you
can copy right and down
I hope the formulas translate into english, since I'm using french Excel.
If not, please notice me.

HTH
--
AP

"Wayne Wengert" a écrit dans le message de
...
A1 - =Source!A1
A2 - =Source!B1
A3 - =Source!B2
....
A27 - =Source!B26

That works fine, but if I then copy the formulas in row A of the summary
worksheet to row B they become values like:
B1 = Source!B2 (I want C2)
B2 - Source!C1 (I want D1)
etc.

How can I accomplish this? There will be 125 rows and that is way to much
typing for me. I suspect the answer is a macro. I've never written xcel
macros but I am comfortable with regular VB.

Any suggestions/examples appreciated.

Wayne

Thank you. That seems to do exactly what I need. I'll study it and see if I
can apply that approach.

ayne

"Ardus Petus" wrote in message
...
See example http://cjoint.com/?ctxZrBvpFB with formulas based on INDEX you
can copy right and down
I hope the formulas translate into english, since I'm using french Excel.
If not, please notice me.

HTH
--
AP

"Wayne Wengert" a écrit dans le message de
...
A1 - =Source!A1
A2 - =Source!B1
A3 - =Source!B2
....
A27 - =Source!B26

That works fine, but if I then copy the formulas in row A of the summary
worksheet to row B they become values like:
B1 = Source!B2 (I want C2)
B2 - Source!C1 (I want D1)
etc.

How can I accomplish this? There will be 125 rows and that is way to much
typing for me. I suspect the answer is a macro. I've never written xcel
macros but I am comfortable with regular VB.

Any suggestions/examples appreciated.

Wayne

When I use the formula "=INDEX(Source!A$18:A$86,2*ROW()-1,1)" It does the
job but for cases where the source row is empty it returns a result of zero.
Since the column is defined as varchar, that causes errors when I import
that data into a DB via DTS. I want to modify the formula, if possible, to
leave the cell empty when the source is empty. I tried several approaches,
none of which work. Does anyone know how to accomplish this?

Wayne

"Wayne Wengert" wrote in message
...
I am trying to automate a process of pulling data contained in a table in a
pdf file into an Excel file. I found a tool to convert the pdf to a Word
doc and from that I can copy/paste into a worksheet. In the spreadsheet, I
have a second worksheet that uses formulas to contruct a table in the
format I need but I am having a problem copying the formulas to subsequent
rows.

The source data (the worksheet into which I paste the source data) has the
information for one logical record spread accross two rows. For example,
cell A1 contains a team name while cells B1 through B26 contain various
scores. In the resulting worksheet I want to have each set of team data on
a single row so I use formulas like the following in the summary
worksheet:
A1 - =Source!A1
A2 - =Source!B1
A3 - =Source!B2
....
A27 - =Source!B26

That works fine, but if I then copy the formulas in row A of the summary
worksheet to row B they become values like:
B1 = Source!B2 (I want C2)
B2 - Source!C1 (I want D1)
etc.

How can I accomplish this? There will be 125 rows and that is way to much
typing for me. I suspect the answer is a macro. I've never written xcel
macros but I am comfortable with regular VB.

Any suggestions/examples appreciated.

Wayne

Corrected version: http://cjoint.com/?cvqWIF1FIK

If performance is critical, I can make a VBA version

HTH,
--
AP

"Wayne Wengert" a écrit dans le message de
...
When I use the formula "=INDEX(Source!A$18:A$86,2*ROW()-1,1)" It does the
job but for cases where the source row is empty it returns a result of
zero.
Since the column is defined as varchar, that causes errors when I import
that data into a DB via DTS. I want to modify the formula, if possible, to
leave the cell empty when the source is empty. I tried several approaches,
none of which work. Does anyone know how to accomplish this?

Wayne

"Wayne Wengert" wrote in message
...
I am trying to automate a process of pulling data contained in a table in
a
pdf file into an Excel file. I found a tool to convert the pdf to a Word
doc and from that I can copy/paste into a worksheet. In the spreadsheet,
I
have a second worksheet that uses formulas to contruct a table in the
format I need but I am having a problem copying the formulas to
subsequent
rows.

The source data (the worksheet into which I paste the source data) has
the
information for one logical record spread accross two rows. For example,
cell A1 contains a team name while cells B1 through B26 contain various
scores. In the resulting worksheet I want to have each set of team data
on
a single row so I use formulas like the following in the summary
worksheet:
A1 - =Source!A1
A2 - =Source!B1
A3 - =Source!B2
....
A27 - =Source!B26

That works fine, but if I then copy the formulas in row A of the summary
worksheet to row B they become values like:
B1 = Source!B2 (I want C2)
B2 - Source!C1 (I want D1)
etc.

How can I accomplish this? There will be 125 rows and that is way to
much
typing for me. I suspect the answer is a macro. I've never written xcel
macros but I am comfortable with regular VB.

Any suggestions/examples appreciated.

Wayne

Thank you very much. 'll try that out.

Wayne

"Ardus Petus" wrote in message
...
Corrected version: http://cjoint.com/?cvqWIF1FIK

If performance is critical, I can make a VBA version

HTH,
--
AP

"Wayne Wengert" a écrit dans le message de
...
When I use the formula "=INDEX(Source!A$18:A$86,2*ROW()-1,1)" It does the
job but for cases where the source row is empty it returns a result of
zero.
Since the column is defined as varchar, that causes errors when I import
that data into a DB via DTS. I want to modify the formula, if possible,
to
leave the cell empty when the source is empty. I tried several
approaches,
none of which work. Does anyone know how to accomplish this?

Wayne

"Wayne Wengert" wrote in message
...
I am trying to automate a process of pulling data contained in a table
in
a
pdf file into an Excel file. I found a tool to convert the pdf to a Word
doc and from that I can copy/paste into a worksheet. In the spreadsheet,
I
have a second worksheet that uses formulas to contruct a table in the
format I need but I am having a problem copying the formulas to
subsequent
rows.

The source data (the worksheet into which I paste the source data) has
the
information for one logical record spread accross two rows. For
example,
cell A1 contains a team name while cells B1 through B26 contain various
scores. In the resulting worksheet I want to have each set of team data
on
a single row so I use formulas like the following in the summary
worksheet:
A1 - =Source!A1
A2 - =Source!B1
A3 - =Source!B2
....
A27 - =Source!B26

That works fine, but if I then copy the formulas in row A of the
summary
worksheet to row B they become values like:
B1 = Source!B2 (I want C2)
B2 - Source!C1 (I want D1)
etc.

How can I accomplish this? There will be 125 rows and that is way to
much
typing for me. I suspect the answer is a macro. I've never written xcel
macros but I am comfortable with regular VB.

Any suggestions/examples appreciated.

Wayne