# How can I combine 2 rows from 2 different sheets?

#### Mp3Girl

##### New Member
Hello...I have a bit of a problem. I need to combine 2 different rows from 2 different sheets without the information switching everytime a sheet is re listed.

Ex.

Sheet 1:
A B C D E
1 Name Due Date Amount Due Paid Owes
2 John Jan 15 \$50 \$45 \$5
3 Mike Jan 13 \$60 \$59 \$1

sheet 2:
A B C D E
1 Name Due Date Amount Due Paid Owes
2 John Jan 15 \$50 \$45 \$5 <---Problem
3Mike Jan 13 \$60 \$59 \$1 <---Problem

I want to add the previous money owed, I was able to add a formula wich will automatically calculate Past Credit by entering: =(Sheet1!E2+Sheet2!(D2-C2))...but if I were to be looking on sheet 1 and wanted to sort by name, due date, amount due, paid, or owes...my info on sheet 2 will change as well, making the info incorrect...How can I combin the 2 rows and lock the information? When the I sort, everyones information gets mixed.
[/u]

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### Ron Morris

##### Active Member
Hi,

Will the name appear in both sheets and will there be last names? Does a name appear more than once?

Ron

#### Mp3Girl

##### New Member
Yes, the name will appear in both sheets and there will be last names. Each name will only appear once in each sheet. Is there a solution?

#### Ron Morris

##### Active Member
Hi,

I believe there is but I just realized I need a little bit more info.

The info in your example for what is in sheet 1 and in sheet 2 is exactly the same. Does the information in sheet2 (all rows and columns) automatically get fed to sheet2?

Are you wanting the formula in column F?

Ron

#### chiello

##### Well-known Member
Type the following formula in cell E2 of Sheet2

=C2-D2+INDEX(Sheet1!\$E\$2:\$E\$3;MATCH(A2;Sheet1!\$A\$2:\$A\$3;0))

and then use autofill functionality to copy formula in cells below.

Obviously, use the following

=C2-D2+INDEX(Sheet1!\$E\$2:\$E\$11;MATCH(A2;Sheet1!\$A\$2:\$A\$11;0))

if your list is made by 10 rows

Ciao Ciao

#### Mp3Girl

##### New Member
Hello Again....

Ron Moris - No, the information in sheet1 doesn't automatically get fed into sheet2. You see...each sheet represents a month...Within each month I add the name of a customer, the date their payment is due, how much is due, how much they paid, and how much they owe...What I wanted to do was to automatically add in how much they owe not just for that month but for previous months if they paid to little...or if they paid extra...but my problem is locking in the information...every time I sort by name, due date, amount paid, etc...the information changes and meses up everyones info....

Chiello - Thanks for the formula, but I wasn't able to use it, it gave me an error sign

#### howzat

##### Board Regular
Hi there,

You probably got an error message because Chiello uses colons as apposed to commas in the formula, this may work for you:

Code:
``=C2-D2+INDEX(Sheet1!\$E\$2:\$E\$11,MATCH(A2,Sheet1!\$A\$2:\$A\$11,0))``

However, if the customer does not appear on other sheets, you will receive a #N/A error message.

In that case the sumif formula would be more robust:

Code:
``=C2-D2+SUMIF(Sheet1!\$A\$2:\$A\$11,A2,Sheet1!\$E\$2:\$E\$11)``

However, you would still need to change the formula each time you copy it to a new sheet, in order to pick up the previous sheet, not necessarily always "Sheet1"

#### Mp3Girl

##### New Member
Yay! It worked! Thanks to all of you (Ron Morris, Chiello, Howzat)

Howzat you are the love of God & a life saver too! LoL

#### Ron Morris

##### Active Member
Sorry I wasn't much help but I am glad Howzat and Chiello were able to provide the solution.

Take care

Ron

Replies
14
Views
596
Replies
3
Views
172
Replies
5
Views
831
Replies
1
Views
175
Replies
8
Views
293

1,181,819
Messages
5,932,232
Members
436,826
Latest member
Rsavary

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back