How can I combine 2 rows from 2 different sheets?

[Mp3Girl]

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]

 

[Ron Morris]

Hi,

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

Ron

 

[Mp3Girl]

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]

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]

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]

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]

Hi there,

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

=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:

=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]

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]

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

Take care

Ron