Assemble contents of two tables into one

Thanks for the reply, Quinn.

As far as layout, the document is in landscape, and I made a deposit slip at one end, and then two tables in the remaining space. This way I can print it, cut off the deposit slip, and keep the other for records. Our records need to have payments from patients separate from payments from insurance (i.e. put Check #100 from John Smith in table A, Check #28484824 from Blue Cross in table B.) The deposit slip would include everything, in no particular order, which is what I mean by unsorted. If I had 15 lines in each source table, and thirty or so on the deposit slip, that would be enough for this sheet. The secretary would fill in the tables as checks arrive throughout the week, and then print this at the end of the week for a Friday deposit.

I could directly link the cells, but that would mean that entries on Table A would be items 1-15 on the slip, and entries on Table B would be 16-30, even if there were empty lines above. I'm trying to avoid that.

I figured it out myself. Thanks. Bye!

No, just kidding. Can you imagine?

Okay, here is what I came up with. First, a prose explanation of the functions I used. This formula is in the deposit slip. Remember, Table A is insurance checks, Table B is patient checks.

1. Use COUNTIF on the entries in Table A, to find how many items are in Table A.

2. Get my row number (actually, my row number minus 5 because I don't start listing checks until line 5 of the deposit slip)

3. IF(my row is less than or equal to the number of items in table A) then (use the value from table A in the same row as mine) else (use the value from table B in the same row as mine minus the number of items in table A)

So, if my row number is two, and there are at least two items in table A, then use the value from table A row two. If I am row seven, and there are three items in table A, use table B row 4.

I then wrapped another IF statement around it to change "0" to a blank, if it returned zero, and an IFERROR around all of that, because the references eventually got out of range.

Here it is:

IFERROR(IF(IF((ROW(B5)−5)<(COUNTIF(Checks from Insurance::$E$2:$E$31,">0")),Checks from Insurance::E2,(INDEX(Checks from Patients::$E$2:$E$20,(ROW(B5)−4)−(COUNTIF(Checks from Insurance::$E$2:$E$31,">0")))))=0,"",IF((ROW(B5)−5)<(COUNTIF(Checks from Insurance::$E$2:$E$31,">0")),Checks from Insurance::E2,(INDEX(Checks from Patients::$E$2:$E$20,(ROW(B5)−4)−(COUNTIF(Checks from Insurance::$E$2:$E$31,">0")))))),"")

Here is the function without the zero catching and error catching statement:

IF((ROW()−5)<(COUNTIF(Checks from Insurance::$E$2:$E$31,">0")),Checks from Insurance::B2,(INDEX(Checks from Patients::$B$2:$B$20,(ROW()−4)−(COUNTIF(Checks from Insurance::$E$2:$E$31,">0")))))

I also wrote a conditional statement so that the "Total number of items" box counts the checks, but counts currency and coins as only one item.

Here is a screenshot.

If you delete that BlueCross entry, John and Betty would immediately move up a row.

EDIT: I just altered it, to use INDEX to find the right cell in Table A as well as Table B. I like uniformity.

=IFERROR(IF(IF((ROW()−5)<(COUNTIF(Checks from Insurance::$E$2:$E$31,">0")),INDEX(Checks from Insurance::$E$2:$E$31,ROW()−4),(INDEX(Checks from Patients::$E$2:$E$20,(ROW()−4)−(COUNTIF(Checks from Insurance::$E$2:$E$31,">0")))))=0,"",IF((ROW()−5)<(COUNTIF(Checks from Insurance::$E$2:$E$31,">0")),INDEX(Checks from Insurance::$E$2:$E$31,ROW()−4),(INDEX(Checks from Patients::$E$2:$E$20,(ROW()−4)−(COUNTIF(Checks from Insurance::$E$2:$E$31,">0")))))),"")