PDF Spreadsheet paste to Excel....

[Bravo2003]

Hi All,
Complicated one this...

I have a PDF copy of a spreadsheet, and need to copy this back into an Excel file...
Normally I would do a copy/paste action and then would need to 'text to columns' convert....
BUT... on this occasion it seems to be pasting the detail in a long column..

I.E. each cell is appearing just one under the other in column A...


Does that make sense?

Now, is there a way to help this situation?

I am trying to do a cell = option, but seems a long winded way to do this:
bear in mind row 1-20 needs to actually appear in row 1 - A to T
then 21-40 row 2 A to T, and so on and so on...
instead of manually writing =$A2 =$A3, etc.. in each cell how do i make cell B/C/D, etc... take the detail from the cell below the previous ref in column A...?
THEN - need row 2 to read from 21-40, row 3 41-60, etc... etc..


Does any of that make sense? I can't do screen images i'm afraid..

 

[Bravo2003]

Is there a way to add 20 to the Axxx:Bxxx number each row this is dragged down by?

A30 '=VLOOKUP(A$1,Sheet2!$A$561:$B$580,2,FALSE)'
need A31 to be '=VLOOKUP(A$1,Sheet2!$A$581:$B$600,2,FALSE)

Is there a way to do this with out manually having to change the detail each time?

 

[Joe4]

You can calculate the rows numbers, and use the INDIRECT function to build that range, i.e.

If you are on row 30, and want to return A561-A580, and want to return A581-A600 for row 31, etc.

Lower range row number: =ROW()*20-39
(literally means take the current row number, multiply by 20 and subtract 39)
Upper range row number: =ROW()*20-20

So, to build your range, you could use this formula (which is dependent on whatever row the formula is placed in):
="Sheet2!$A$" & ROW()*20-39 & ":$B$" & ROW()*20-20

Now, to apply it in your VLOOKUP formula, use the INDIRECT function (see Excel's built-in help files for details and examples).

So, your final VLOOKUP would look like this:

=VLOOKUP(A$1,INDIRECT("Sheet2!$A$" & ROW()*20-39 & ":$B$" & ROW()*20-20),2,FALSE)

 

[Bravo2003]

You can calculate the rows numbers, and use the INDIRECT function to build that range, i.e.

If you are on row 30, and want to return A561-A580, and want to return A581-A600 for row 31, etc.

Lower range row number: =ROW()*20-39
(literally means take the current row number, multiply by 20 and subtract 39)
Upper range row number: =ROW()*20-20

So, to build your range, you could use this formula (which is dependent on whatever row the formula is placed in):
="Sheet2!$A$" & ROW()*20-39 & ":$B$" & ROW()*20-20

Now, to apply it in your VLOOKUP formula, use the INDIRECT function (see Excel's built-in help files for details and examples).

So, your final VLOOKUP would look like this:

=VLOOKUP(A$1,INDIRECT("Sheet2!$A$" & ROW()*20-39 & ":$B$" & ROW()*20-20),2,FALSE)

Spot on... thank you, that has saved me so much time..