Ask Experts Questions for FREE Help !
Ask
    kiepre's Avatar
    kiepre Posts: 2, Reputation: 1
    New Member
    		  
    #1

    Mar 31, 2009, 08:35 AM
    Excel If function using 2 worksheets
    I am using Excel 2007. I have created a spreadsheet1 with a list of names and dates. The names and dates can be repeated. I have another spreadsheet 2 with names and formulas. Here the names are not repeated. The formulas adjust the dates for aging purposes. If the name on Spreadsheet 1 equals name on spreadsheet 2 I want the formula on Spreadsheet 2 to be used on Spreadsheet 1. The formulas are simple like E2+0
    E2 being date 3/19/2009. Thank you for your help
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #2

    Mar 31, 2009, 10:28 PM
    Post up an an excerpt from both books and show us exactly what you mean.

    Click on GO ADVANCED and use the paperclip icon to attach the sample workbooks.
    kiepre's Avatar
    kiepre Posts: 2, Reputation: 1
    New Member
    		  
    #3

    Apr 1, 2009, 06:05 AM


    I have attached a sample of both spreadsheets. The aging sheet changes daily. The customer sheet does not change it just has the formulas that apply to the customers for aging dates. I currently copy the formulas to the aging by customer name and number. I would like to somehow create a lookup that says if the name and customer number matches the list then use these formulas in column H-K on the aging sheet. Thanks for any help with this.

    Note: I was not able to upload the file as a .xlsx file.
    Attached Files
  1. File Type: xls Aging test.xls (23.0 KB, 209 views)
  2. File Type: xls customer.xls (87.0 KB, 252 views)
    3. ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
    		  
    #4

    Apr 1, 2009, 07:08 AM

    Your sheets and explanation are confusing. But what I think you are trying to say is that each customer has an aging factor. That factor is then added to the AsOf date to produce an aging date.

    If that's what you want then, in your customer table, just add the factor. Then in your aging table use a formula like:

    =E2+Vlookup(B2,Customers,x)

    B2 is customer #, Customers is a name assigned to the Customers table, an x is the number of the column that contains the factor.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #5

    Apr 1, 2009, 10:11 AM

    I wonder what you are gaining with these =+E2+0 formulas?

    That's not the same thing as =E2 ?

    =IF(+F2+0=0,"",+F2+0) ----> =IF(F20=0,"",F2)

    Secondly, as Scott notes, your explanation and terms don't match. We've all done Aging reports, very common task. But your formulas don't appear to age anything, they just compare dates and hide/show them.

    Aging means "How many months has this client been active?" Or, it means "How many months behind in payments is this client?"

    Your sheet doesn't do either thing in the least. Confusing is the word.
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Saving files/excel worksheets to USB memory sticks [ 17 Answers ]

Hello Friends, For some time now as well as saving my work on the "C" drive I have also saved it on my USB memory sticks with no problems up to now. Lately when I attempt to save my work I get a message telling me that "I can not save file as the location I want to save to is right protected"....

Using one Excel list table in multiple worksheets [ 2 Answers ]

I have a workbook containing 13 worksheets. Sheet 13 contains two list tables. I need to link sheets 1 through 12 to the list tables in sheet 13 so that only one set of lists has to be updated when changes occur. Please help me...

Excel 2003 linking worksheets [ 6 Answers ]

Hi, I am not sure if I am in the right area but I need to ask about excel 2003. I have a simple question which I do believe there is an answer but I have tried various options to no avail. Does anyone know where I can ask expert advise about excel 2003. Many thanks for any advise given.:)

Excel function [ 1 Answers ]

I am looking for a formula that allows me to have a specific text if the value in the cell selected is between 19xx and 19yy. I thought of using IF but can't figure it out... :( If the value is greater than 1967 and less than 1977 value = 30s

Excel Worksheets [ 1 Answers ]

I have several worksheets in an Excel spreadsheet. Is there a way that I can print a list of the names of each of the worksheets in the spreadsheet?

View more questions Search