Formula to reference another worksheet, locate data, then record i

[Natasha]

Hi All,

It has been over 10 years since I did my Excel studies and I've
unfortunately forgotten everything I haven't used regularly. My office has
recently upgraded to Office 2007 (upgrade not being the descriptor I'd have
chosen!) and I'm struggling with Excel. I've found my way around most issues,
but I'm REALLY stuck now and suffering Friday-itis on top of it all!

Essentially I have a multi-sheet workbook for my debtors ledger. Each page
has separate columns for the customer numbers, names, total debts in each age
range (7 days, 14 days etc), totals and lastly contact notes. I've added
another three columns intending to show the difference in 14 days on sheet
30/04/09 and 14 days on sheet 08/05/09, but relating to a specific customer.

So the formula would need to look at the name in cell B7 on sheet 08/05/09
(for example) and find the same name in sheet 30/04/09 (possibly cell B20).
It would then check the value shown in cell E7 on sheet 08/05/09 and the
value shown in cell E20 on sheet 30/04/09 and show the dollar difference in
cell I7 on sheet 08/05/09.

Can anyone help with this? I would be eternally grateful and would worship
the very ground you walk upon!

 

[Natasha]

Sorry, I should mention that I'd use the formula to calculate 14 days, 21
days and total, not just 14 days. I can work out the formula to compare the
debts, just not how to make it find the correct cells on both sheets when Joe
Bloggs could be at line 7, 20 or 123!

Off home now to drown my sorrows at my ineptitude... I look forward to some
genius setting me straight!

 

[Roger Govier]

Hi Natasha

Adjust the following generalisation to match your sheet names and the
columns containing Customer Name and Value
=INDEX(Sheet1!B:B,MATCH(Sheet2!A1,Sheet1!A:A,0))-Sheet2!B1

Where in this example, Column A on both sheets contains Customer Name and
column B contains Value