How can I combine 2 rows from 2 different sheets?

Mp3Girl

New Member
Joined
Jan 18, 2005
Messages
4
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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Ron Morris

Active Member
Joined
Oct 18, 2004
Messages
430
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
Joined
Jan 18, 2005
Messages
4
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
Joined
Oct 18, 2004
Messages
430
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
Joined
Jan 18, 2005
Messages
848

ADVERTISEMENT

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
Joined
Jan 18, 2005
Messages
4
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 :cry:
 

howzat

Board Regular
Joined
Oct 13, 2003
Messages
144

ADVERTISEMENT

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
Joined
Jan 18, 2005
Messages
4
Yay! :biggrin: It worked! Thanks to all of you (Ron Morris, Chiello, Howzat)

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

Ron Morris

Active Member
Joined
Oct 18, 2004
Messages
430
Sorry I wasn't much help but I am glad Howzat and Chiello were able to provide the solution.

Take care

Ron
 

Forum statistics

Threads
1,147,846
Messages
5,743,518
Members
423,801
Latest member
paulj4177

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?

Disable AdBlock

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
Top