Page 4 of 6 FirstFirst ... 2 3 4 5 6 LastLast
Results 31 to 40 of 51

Thread: Specification Comparison Table from Different Worksheets

  1. #31
    Acolyte Polignac's Avatar
    Join Date
    Apr 2021
    Posts
    23
    Articles
    0
    Excel Version
    Office 365


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Yea I'm looking through the worksheet. The list down to trigger different models are fine but i'm not quite sure why some of the rows trigger #N/A. Is there a way to view excel cells to see the formatting of individual cells? Else it's so close yet so far!! Argh!

  2. #32
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,671
    Articles
    0
    Excel Version
    Office 365 Subscription
    It will be fixable, but I can't help without a workbook - sorry. A screenshot MIGHT help.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #33
    Acolyte Polignac's Avatar
    Join Date
    Apr 2021
    Posts
    23
    Articles
    0
    Excel Version
    Office 365
    Hi Ali, if I were to email you the attachment would it be okay to have a look at it? The file is too confidential to show in a public domain like this unfortunately.

  4. #34
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,671
    Articles
    0
    Excel Version
    Office 365 Subscription
    I would, but you don't know me, who I really am or whether I can be trusted. Do I want to share my personal E-mail with someone I know nothing about? No. So I'm afraid it's either provide a desensitised copy here that shows the error, or try to fix it yourself. It's possible that trimming one of the fields in the query might resolve the issue, or changing the formatting of one of the fields.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #35
    Acolyte Polignac's Avatar
    Join Date
    Apr 2021
    Posts
    23
    Articles
    0
    Excel Version
    Office 365
    Hi Ali, I do understand the dilemma here. I seem to be able to replicate the issue here in this camera workbook and provided some dummy info. As you can see, some #N/A shows up itself and dont seem to be able to reference the correct cells. Thanks.
    Attached Files Attached Files

  6. #36
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,671
    Articles
    0
    Excel Version
    Office 365 Subscription
    In the Name Manager, change the Parameters reference from this:

    =Compare!$A$2:$B$59

    to this:

    =Compare!$A$1:$B$59

    then refresh queries.
    Attached Files Attached Files
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #37
    Acolyte Polignac's Avatar
    Join Date
    Apr 2021
    Posts
    23
    Articles
    0
    Excel Version
    Office 365
    Hi Ali, thanks for the error spotting! The #N/A i have in the table results in error arises from the formula: =IF(D$1="Brand & Model","",LOOKUP(2,1/((Composite[Brand & Model]=D$1)*(Composite[Parameters]="Resolution "&$B12)),Composite[Value])) for specific cells. Can you educate me on how is it executed? I have narrowed it down to here being where the problem is for the "real data". As far as cell formatting is concern, I've copied the same formatting style as other cells and just replace the values in there and still yields #N/A error.

  8. #38
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,671
    Articles
    0
    Excel Version
    Office 365 Subscription
    Did you try the fix on your real data? Until you do, the formula will not be able to find anything to return. If that's not fixing it in the real data, then something else is at play.

    You can share the real file with my via a private message using a link to a file-sharing site (r.g. DropBox) if you are prepared to take the risk and trust me, but you can't attach files directly to PMs.
    Ali
    Enthusiastic self-taught user of MS Excel!

  9. #39
    Acolyte Polignac's Avatar
    Join Date
    Apr 2021
    Posts
    23
    Articles
    0
    Excel Version
    Office 365
    Hi Ali, yea I tried to fix it for few days now without success. I really did try to understand the codes but it's a little too much for me at the moment. I will proceed with PM you on a dropbox link in the next 2 hours. Thanks Ali.

  10. #40
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,671
    Articles
    0
    Excel Version
    Office 365 Subscription
    A few days??? I only gave you the fix yesterday! Are you SURE you applied that fix AND refreshed the queries???

    I will have a look at your DropBox link.
    Ali
    Enthusiastic self-taught user of MS Excel!

Page 4 of 6 FirstFirst ... 2 3 4 5 6 LastLast

Posting Permissions

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