How to do this?

  • Thread starter Thread starter Wayne Wengert
  • Start date Start date
W

Wayne Wengert

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. That seems to do exactly what I need. I'll study it and see if I
can apply that approach.

ayne
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Excel - Select a value from one cell in Excel to update a SQL table 1
Delete rows takes forever 4
Copy/Transpose into a matrix of merged cells. Use Offset formula orVBA ? 2
Copy worksheet, trying to paste to new workbook error assistance. 1
MS Excel VBA Worksheets group tables in one table. 1
Code to copy the last row formulas and paste it in the bottom row 3
Vba Help with Information Extraction 0
Neat code for pasting filtered data to a separate worksheet 10

Back
Top