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
0
Natasha (16)
5/8/2009 7:58:09 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
430 Views

Similar Articles

[PageSpeed] 34

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
0
Natasha (16)
5/8/2009 8:19:01 AM
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" <Natasha@discussions.microsoft.com> wrote in message 
news:9D24CFD5-E18E-402A-8888-EC1831ED2AFA@microsoft.com...
> 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 

0
Roger
5/8/2009 8:23:59 AM
Reply:

Similar Artilces:

How do I set up a daily average of unit sales formula
More info required. -- HTH RP (remove nothere from the email address if mailing direct) "jim m" <jim m@discussions.microsoft.com> wrote in message news:7E6D4510-97C1-42D4-A402-5590201C6065@microsoft.com... > ...

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

Tracking shared records
I need to create a report that shows which records are shared and with whom. Can someone guide me in the right direction? I can't seem to find it anywhere online. thank you Sharing is recorded in the PrincipalObjectAccess table in the SQL database. Advanced find does not allow you to access this so you will have to create a report on the directly on the database. -- Patrick Verbeeten (MCPD) Lead Developer Aviva IT Extended Entity and Plug-in browser: http://www.patrickverbeeten.com/maps/CrmTool.aspx "Bernardina" wrote: > I need to create a report that shows whi...

double worksheets?
A co-worker was working in a regularly-used workbook. Suddenly a second, seemingly identical workbook opened; the two were named *.xls:1 and *.xls:2 respectively. A week ago, a similar situation happened with the same workbook, except the duplication occured upon opening, rather than after it was already open. At that time, when we closed one of the "versions" the colon-number additional extention disappeared. But after closing the remaining document and reopening, the duplication appeared again upon opening. Can anyone tell us what this means? Did she accidentally hit a comman...

Worksheet Auto update
I need to find a way to automate a process. Is there a way to automatically replace the content of a worksheet with the content of another one? Every morning I get a sales report in excel for the previous day sales. I save it in a folder and then I do a pivot table on this sheet to determine sales by product category for example. The following day, I open the previous day file, replace the sales report with the new one and then refresh my pivot table. Is there a way to have my sales report update anytime I get a new sales report? To be more clear I have a workbook with two tabs: Pivot...

Reference Book
I'm a Win32 programmer, and I'm trying to get into some MFC. I've read a little about MFC, but the books lack a lot of quality and descriptive text about the structure of MFC. Can I ask whose book is the Charles Petzold of MFC? Thank you I haven't seen a bad MFC book in a long time, but then again, I haven't bought a new MFC book in many years. My presonal perference are the MFC books published by Wrox Press http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y&isbn=1861000855&itm=31 You can't go wrong with the Microsoft Press ones either. Al...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

How do I copy the result of a check box into another check box?
I'm trying to copy the result of a "check box form field" into another check box. For example: if I check (or uncheck) one box in a form, another box later in the protected form will also be checked (or unchecked) , much like the ref + F9 command for the text form field. Is this possible? Thanks. This cannot be done without macros. If your project will allow macros - see http://word.mvps.org/faqs/tblsfldsfms/ExclusiveFmFldChbxs.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> G...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

Duplicate record in RM tables
We experienced an issue in Apply Sales Document that may have caused a duplicate record somewhere. We found this when running Paid Sales Transaction Removal and received this message: Violation of PRIMARY KEY contraint PKRM3101. Cannot insert duplicate key in object RM30101. I ran the RM duplicate tool found in the automated help area of this website and found the following: --- Begin copy here ---- Duplicates between RM Open and RM History Document #: 07-003021-17 Customer #: 079100 RMDTYPAL #: 7 --- End copy --- It looks like the duplicte tool also logs the qu...

How to track ActiveControl.Name when switching records in form with multiple subforms
I need to have a global variable always contain the name of the current form field. This bit of code is attached to the GotFocus event of all fields and the Enter event of all subforms: gxCurrentField = Me.ActiveControl.Name However it doesn't work properly when changing records in a subform. My parent form contains two subforms in a many-to-many relationship. The above variable usually ends up containing the name of the first field in the second subform when switching records in the first subform. How to correctly code this? Or is there some native variable I'm not aware of? I...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

creating a spredsheet and log the info into another spredsheet
hello, I have a excel sheet that is printed out and a cashier manual enters information, invoice number, invoice amount, cash amount, check amount, amex amount.... I would like to have the cashier input this information on her PC and print a copy to go along with the deposit and at the same time log the information into a google excel document I created. Any ideas how this can be done? ...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

Missing Reference
Argh! There was a missing reference, " Microsft DAO2.5/3.5 Compact ..." I unselected it and backed out - now, every date field is filled with today's date and several other fields have gone to the 'dark side'. The $ field shows $0.00 for everyone and another field is blank. I went back into the dialog box and I can't find that reference in the list to reselect it. Thank goodness I experimented using a copy, eh? >-----Original Message----- >Your references are probably messed up. >If any of the selected references have "MISSING:" in front ...

how can I do a lookup into another sheet?
how can I do a lookup into another sheet? thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Try something like =VLOOKUP(123,Sheet2!A1:B10,2,FALSE) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "melawaisi" <melawaisi.ws67m@excelforum-nospam.com> wrote in message news:melawaisi.ws67m@excelforum-nospam.com... > > how can I do a lookup into another sheet? > thanks > > > ------...

Duplicating one Field from One table to Another
Hi - I have two tables - one position, one personnel - which has a 1-to-many relationship (1 position record to many personnel records). The department had a new requirement which made it necessary to change some coding (I inherited this). I'm using tab forms so that when a position is pulled up, you can click on the tab that has the personnel information (if there is any). There is a button on the Personnel form that allows the user to add a new Personnel record. Since I am using an Auto-number field in the Position table (which doubles as the PK) the functionality is fine. Wh...

Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

Textbox fomatting value based on another textbox
I have two text boxes on a form. One is a value that can be changed by the user. The second is the value 1 - textbox1. I need everthing to be in %. For example, in textbox1 the user could type 75 and it would automatically be recognized as 75% and textbox2's value would calculate to be 25%. Everytime I try textbox1's value is = to say 7500%. Any help is appreciated. Cheers, Job Maybe something like: Option Explicit Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim TB1Val As Double With Me.TextBox1 If IsNumeric(.Value) Then TB1Va...

Formula to display nearest following Thursday in mm/dd/yyyy format
Hello, I have been reading and trying different suggestions here to no avail. What I need is a formula to calculate the nearest following thursday, and display it in mm/dd/yyyy format. To be clear, I have a column of varying dates. I need a formula to return the next thursday for each of those dates. To illustrate, say I have 05/22/2010, 05/23/201, 05/24/2010, & 05/26/2010 in cells A1 through A4. In cells B1 through B4, I would like to see 05/27/2010, 05/27/2010, 05/27/2010, & 05/27/2010 representing the following thursday. Thank you for your help! BW T...

Find tab in worksheet
I have a workbook with many tabs & many users and would like to create a 'Go to / find' function that finds a particular tab when opening workbook, so that user will enter tab in text box and will then go directly to tab Try any one of these macros.. You can try out the below macro. If you are new to macros.. --Set the Security level to low/medium in (Tools|Macro|Security). --From workbook launch VBE using short-key Alt+F11. --From menu 'Insert' a module and paste the below code. --Get back to Workbook. --Run macro from Tools|Macro|Run <selected mac...

Data validation list from another worksheet?
Is it possible that the value list for data validation be populated fro another worksheet? Puneet Aror -- puneetarora_1 ----------------------------------------------------------------------- puneetarora_12's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1840 View this thread: http://www.excelforum.com/showthread.php?threadid=38572 Sure is! Use a named range as described here: http://www.officearticles.com/excel/drop-down_using_data_validation_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "punee...