+ Reply to Thread
Results 1 to 14 of 14

Excel 2007 If function using 2 worksheets returning formulas

  1. #1
    Registered User
    Join Date
    03-31-2009
    Location
    Fort Worth, Texas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Excel 2007 If function using 2 worksheets returning formulas

    I have spreadsheet1 (Aging) with names and dates, Names can appear more than once. Spreadsheet2 (Customers) has a list of customers and formulas to calculate aging date. Customers appear only once. I want to match Aging names with Customer list then apply formula to caluculate aging date. Exp:Aging shows Customer Smith with a date of 3/01/09, Customer list shows Smith with a formula of B2+10. Column B is the date col. Answer would be 3/11/09. I have used VLookup. It returns the formula not the result.
    Any help for this novice would be appreciated.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Excel 2007 If function using 2 worksheets returning formulas

    Is something like this what you used?

    =Vlookup(A2,Aging!A:B,2,0)+10
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    03-31-2009
    Location
    Fort Worth, Texas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel 2007 If function using 2 worksheets returning formulas

    I did not use the same formula. Each Customer has a different aging formula. Some are +10, +30, +0 etc. In my formula I just selected the column # the formulas are in. This unfortunately does not work.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Excel 2007 If function using 2 worksheets returning formulas

    Please attach a sample workbook showing the issue.

  5. #5
    Registered User
    Join Date
    03-31-2009
    Location
    Fort Worth, Texas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel 2007 If function using 2 worksheets returning formulas

    I have attached examples of the two workbooks. I want to match the aging with the customer formulas.
    Thanks again for your interest in helping.
    Attached Files Attached Files

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Excel 2007 If function using 2 worksheets returning formulas

    So can you give some examples of what you need now and specify exactly where the data comes from and where it should go.

  7. #7
    Registered User
    Join Date
    03-31-2009
    Location
    Fort Worth, Texas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel 2007 If function using 2 worksheets returning formulas

    I match names from the aging list to the customer list, then find on the same row the correct formula on the customer list for dates. If the name is listed more that once on the customer list then I check the customer number. currently I am copying the formula to every line for that customer on the aging list. That is why I would like to find a formula to do this lookup and match. There are 4 date fields on the spreadsheets. You can see that some customers have just 2 date formulas and some have 4. Exp: Customer Belin column H date formulas is E+0. E being "as of date". The other formulas are triggered by what dates are in the other columns.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Excel 2007 If function using 2 worksheets returning formulas

    I'm sorry but i am still confused about which column on which sheet you want filled in and with what data from what sheet(s)?

  9. #9
    Registered User
    Join Date
    03-31-2009
    Location
    Fort Worth, Texas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel 2007 If function using 2 worksheets returning formulas

    I apologize. It is my inexperience that is causing the problem.
    On the example spreadsheet called Aging I want to fill in columns H,I,J & K with formulas found on the spreadsheet called Customer, sheet called formula.
    The "customer" spreadsheet is a list of all customers names & #'s. The formulas are used to age the A/R balances found on the aging spreadsheet. The aging spreadsheet changes, the customer spreadsheet does not change. It is just a reference list.
    In a perfect world I would use a formula to lookup the name and possibly the number of a customer off the aging spreadsheet and match it to the customer list. Then return the formula on the customer list in columns H,I,J & K and place the formulas on the aging spreadsheet. If the formula in col. H on the customer list for customer Smith is take the date in col E, row 2 and add 10 to the date I want the same formula on the aging spreadsheet.
    Thanks for your help

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Excel 2007 If function using 2 worksheets returning formulas

    This is the formula I put in H2 of Aging Text.xlsx

    Please Login or Register  to view this content.
    and copied down and across to column K.

    Then I went to Tools|Options and deselected Zero Values from the View tab.

    Then I formatted the 4 columns as Date.

    I hope this is what you were needing.

    This matches both the name and Entry type and returns the value in corresponding rows and columns.

  11. #11
    Registered User
    Join Date
    03-31-2009
    Location
    Fort Worth, Texas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel 2007 If function using 2 worksheets returning formulas

    I appreciate the try but this doesn't work for me. I do not want the value found in the spreadsheet "customer' but the formulas. In my example the answer on spreadsheet "Aging" would be blank or 3/29/2009 or unapplied. I have filled in some of the formulas on the aging spreadsheet and attached it as an example. The customer spreadsheet is just a list of current customers and the aging formulas used per their contract. Currently I copy and paste the formulas to the correct customer on the aging. I was hoping to automate the process by matching the name.
    I really do thank you for the help.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    03-31-2009
    Location
    Fort Worth, Texas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel 2007 If function using 2 worksheets returning formulas

    I thought of another way to explain that might be better. If the name on Aging column C and Number on Aging column B matches name on Customer column C and Number on customer Column B then use formula found on Customer column H. (E2+0).

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Excel 2007 If function using 2 worksheets returning formulas

    You can't copy formulas and have them work... you have to copy the results. Why can't the results work for you? If they are/should be the same in both sheets?

  14. #14
    Registered User
    Join Date
    03-31-2009
    Location
    Fort Worth, Texas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel 2007 If function using 2 worksheets returning formulas

    The aging sheet changes everyday. The customer sheet never changes. It is used only to house the formulas that appy to the customer. Thanks for the noble try. This must be one that can't be done an easy way.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1