help - urgent

[daroc]

hey,

I have to 2 files.

I want to retrieve information from file B and insert it into file A.

File A has empty rows after each row (i.e. A1 filled, A2 EMPTY, A3 filled)

Now I want the information that is in file B to be placed into A2, A4 etc respectively. Please help on this. Thanks

[vezerid]

daroc,

how are the data in file B organized? In consecutive rows? If so, you
can fill column B:B in file A with the following formula:

=IF(MOD(ROW(),2)=0,OFFSET('[fileB]Sheet1'!$A$1, ROW()/2,0),"")

Then you fill column C:C with the following formula:
=IF(A1<>"",A1,B1)

And copy down as far as necessary.

HTH
Kostis Vezerides

[daroc]

hey verezid,


the information in SHEET 2 is not consecutive. let me know please thank you very much

[vezerid]

OK, I am running out of inspiration for a formula-based solution (at
least one that will use only one column). Instead you can use the
following VBA macro, which will transfer the data from file B to your
target sheet. This macro should be installed in File A and it requires
that File B is open when it runs:

Sub TransferData()
outrow = 2
For inrow = 1 To 10000
If (Workbooks("File B.xls").Sheets("Sheet1").Cells(inrow, "A") <>
"") Then
Cells(outrow, "B") = Cells(inrow, "A")
outrow = outrow + 2
End If
Next inrow
End Sub

What to modify:
-the number 10000 should be changed to the highest number row expected
to contain data in File B
-change the text constants inside the quotes ("File B.xls", "Sheet1")
to the names of the input workbook and sheet respectively.
-Cells(inrow,"A") supposes the data in the input file are in column
"A". Modify to suit. Same for Cells(outrow,"B") for the output file.

How to install:
Alt+F11 to go to the VBA editor.
Menu Insert|Module
Paste the above code after modifications.

To run:
Go to the sheet where you want the data transferred.
Alt+F8 to show the macros dialog box
Select the macro TransferData and Run.

Once the data are imported in column B:B you can use the remainder of
my first post. In column C:C:

=IF(A1<>"",A1,B1)

Does this help?

Kostis Vezerides