Results 1 to 4 of 4
  1. #1
    robjd
    Guest

    Pasting? in Excel 97

    I have set up a spreadsheet with a monthly "log" area and a summary area. The summary area uses VLOOKUPs to find the relevant details for a given month.

    I have set up one set of VLOOKUPs and want to copy them to look for other values. When I pasted the formulas to other ranges and the "log" range being looked up is blank the results come back as #N/A. In the original the results were "0".

    What can I do to fix this? I'm not sure I want to go back through and add IF functions to 100+ formulas.

    Note: I cannot see any differences in the cell attributes and I was using the regular Paste feature so everything should be coming along.

    Thanks in advance for any and all help.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pasting? in Excel 97

    Does the Look_Up value in the second range include matches for the Look_Up Table, as it seems that the values that you are looking up cannot be found in the new range. If VLOOKUP cannot find a value it returns #N/A. You need to ensure that the value you are looking up exists in the range you are looking in.

    It is possible to have that error if the value you are looking up is numeric and the equivalent value in the lookup table is text, or vice versa, e.g looking up the number = 100 against a text string = 100.

    Hope this is of help

  3. #3
    robjd
    Guest

    Re: Pasting? in Excel 97

    You are right that the values cannot be found in the new range. The strange part is that the formulas are pointing to the same blank ranges. In the cell where the original formulas were set up the value returned is "0". In the pasted location all the formulas are returning a #N/A. I would assume that they should all be returning #N/A. When there is data the original and pasted versions report the correct numbers.

    Instead of trying to figure this out is there a way of getting a sum function to ignore all the #N/A's in the rows. I need to do some conditional formatting of cells based on totals that are dependent on the lookup values returned. Is there a way of doing this without changing the formulas as each set of data is entered in the "log" ranges?

    Thanks

    Rob

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pasting? in Excel 97

    Maybe if you do a conditional lookup, which if the value cannot be found, inserts 0. If your current formula is =VLOOKUP(A2,Log,2,FALSE)
    repalce it with =IF(ISNA(VLOOKUP(A2,Log,2,FALSE)),0,VLOOKUP(A2,Log ,2,FALSE)). (If you are not looking for exact matches you omit the FALSE).

    Hope that makes sense

    Regards

Posting Permissions

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