Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula to reference another worksheet, locate data, then record i
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula to reference another worksheet, locate data, then record i
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! -- Natasha "Natasha" wrote: 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula to reference another worksheet, locate data, then record i
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 -- Regards Roger Govier "Natasha" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a record in one worksheet from the data in another. | Excel Worksheet Functions | |||
How do I locate a circular reference error? | Excel Worksheet Functions | |||
How can I create a formula to locate data between two dates? | Excel Worksheet Functions | |||
how do I locate a cell that provides data to a formula ? | Excel Worksheet Functions | |||
how do I locate a cell that provides data to a formula ? | Excel Worksheet Functions |