# Complicated Pasting for QIF format

Posted by Justin McLain on September 29, 2001 6:51 PM

I am entering in several years of financial information into quicken. My statments are in paperform and the bank does not offer them electronically. I have decided that it would be much easier to perform the data entry in Excel versus directly into Quicken.

This being the case, I have analyzed the make-up of the QIF file format and have written formulas that will malipulate my 5 column table into the appropriate QIF format. The QIF format required the data to exist as tab seperated text in a single column. The data is formated in 7 rows equaling a record (the 5 formated specially columns from my table, a 6th row that contains information derived from my 5 column table, and a "^" to deliminate records) For Example;

[Sheet1] Data Source WorkSheet

A B C D E

1 Date Amount Payee Type Method

2 9/28 $500.00 IRS Tax EFT

3 9/29 $45.00 Shell Fuel ChkCrd

Is formated as

[Sheet2] Data Result WorkSheet

A

1 !BankNum

2 D09/28' 0

3 U-500.00

4 T-500.00

5 NEFT

6 PIRS

7 LTax

8 ^

9 D09/29' 0

10 U-45.00

11 T-45.00

12 NChkCrd

13 PShell

14 LFuel

15 ^

I've tested it an works perfectly, but the pasting additional records is very tedious. Pasting advances the row numbers in my formula's by 7. Although, I am pasting (or dragging) 7 rows at a time in my results table, I want it only to advance the row number in my formulas by 1. Does anyonw know a way to paste or fill in a manner that let me paste 7 rows at a time, but only advance the row numbers in my formulas by 1? See the Examples Below to illustrate my question.

(Example Result of Paste/Fill, Rows 2-8 are copied and pasted into rows 9-22])

A

1 !BankNum

2 =CONCATENATE("D",Data!A2,"' 0")

3 =CONCATENATE("U-",Data!B2,)

4 =CONCATENATE("T-",Data!B2,)

5 =IF( LEN(Data!D2) <> 0, CONCATENATE("N",Data!D2,), CONCATENATE("N", "ChkCrd"))

6 =CONCATENATE("P",Data!C2,)

7 =CONCATENATE("L",Data!E2,)

8 ^

9 =CONCATENATE("D",Data!A9,"' 0")

10 =CONCATENATE("U-",Data!B9,)

11 =CONCATENATE("T-",Data!B9,)

12 =IF( LEN(Data!D9) <> 0, CONCATENATE("N",Data!D9,), CONCATENATE("N", "ChkCrd"))

13 =CONCATENATE("P",Data!C9,)

14 =CONCATENATE("L",Data!E9,)

15 ^

16 =CONCATENATE("D",Data!A16,"' 0")

17 =CONCATENATE("U-",Data!B16,)

18 =CONCATENATE("T-",Data!B16,)

19 =IF( LEN(Data!D16) <> 0, CONCATENATE("N",Data!D16,), CONCATENATE("N", "ChkCrd"))

20 =CONCATENATE("P",Data!C16,)

21 =CONCATENATE("L",Data!E16,)

22 ^

Continues for N Records / Rows of Source Data....

(DESIRED Results, Rows 2-8 are copied and pasted into rows 9-22])

A

1 !BankNum

2 =CONCATENATE("D",Data!A2,"' 0")

3 =CONCATENATE("U-",Data!B2,)

4 =CONCATENATE("T-",Data!B2,)

5 =IF( LEN(Data!D2) <> 0, CONCATENATE("N",Data!D2,), CONCATENATE("N", "ChkCrd"))

6 =CONCATENATE("P",Data!C2,)

7 =CONCATENATE("L",Data!E2,)

8 ^

9 =CONCATENATE("D",Data!A3,"' 0")

10 =CONCATENATE("U-",Data!B3,)

11 =CONCATENATE("T-",Data!B9,)

12 =IF( LEN(Data!D3) <> 0, CONCATENATE("N",Data!D3,), CONCATENATE("N", "ChkCrd"))

13 =CONCATENATE("P",Data!C3,)

14 =CONCATENATE("L",Data!E3,)

15 ^

16 =CONCATENATE("D",Data!A4,"' 0")

17 =CONCATENATE("U-",Data!B4,)

18 =CONCATENATE("T-",Data!B4,)

19 =IF( LEN(Data!D4) <> 0, CONCATENATE("N",Data!D4,), CONCATENATE("N", "ChkCrd"))

20 =CONCATENATE("P",Data!C4,)

21 =CONCATENATE("L",Data!E4,)

22 ^

Continues for N Records / Rows of Source Data....

Thank you.

Regards,

Justin