Page 2 of 6 FirstFirst 1 2 3 4 ... LastLast
Results 11 to 20 of 51

Thread: Specification Comparison Table from Different Worksheets

  1. #11
    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!

    No worries. I appreciate your help on this.

  2. #12
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,651
    Articles
    0
    Excel Version
    Office 365 Subscription
    Did you see the post I just made? Sorry, but I've had to withdraw the solutions offered.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #13
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,651
    Articles
    0
    Excel Version
    Office 365 Subscription
    I believe I have now resolved the issues. I will post a How To ... within a couple of hours.
    Ali
    Enthusiastic self-taught user of MS Excel!

  4. #14
    Acolyte Polignac's Avatar
    Join Date
    Apr 2021
    Posts
    23
    Articles
    0
    Excel Version
    Office 365
    Thanks Ali! Looking forward to it!

  5. #15
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,651
    Articles
    0
    Excel Version
    Office 365 Subscription
    And here it is!

    I suggest you print out and read through the instructions BEFORE attempting anything. PQ is easy once you get the hang of it, but there's a lot here for a novice, and any slight wrong step will mess everything up.

    Also, be sure to use THIS version of the workbook (version 3), as this is the one that has been tweaked. If you want to work through the instructions I have provided to practise setting this up for yourself, then start with the workbook that YOU originally attached to the thread.

    Let me know how you get on!
    Attached Files Attached Files
    Ali
    Enthusiastic self-taught user of MS Excel!

  6. #16
    Acolyte Polignac's Avatar
    Join Date
    Apr 2021
    Posts
    23
    Articles
    0
    Excel Version
    Office 365
    Hi Ali, thank you so much for the attachments. It's the first time I heard of Power Query but I'll look at tutorials to see how to work with it. Had a quick glance of the word doc, and will spend next few days digesting the data. Will need your guidance some time next week on this.

    Just a quick question for now: I've also tried to tweak some numbers in the individual worksheet (e.g. Canon R5 model) in the v3 file to see if the comparison table will update itself, doesn't seem to do so unless I tweak it in the Composite worksheet. Is there a way to tweak the numbers in the worksheets (canon for eg) to reflect changes in the comparison worksheet, i.e. by-passing hunting down the corresponding cell in the composite worksheet?
    Last edited by Polignac; 2021-04-10 at 10:33 PM.

  7. #17
    Acolyte Polignac's Avatar
    Join Date
    Apr 2021
    Posts
    23
    Articles
    0
    Excel Version
    Office 365
    Edit Update: I've look closer at the instruction, i think the answer to my query above is on line 11(d), i'd need to click Refresh All on data ribbon to update the queries. Also just want to note that under instruction 2, the syntax "i" has been capitalised by Word doc, so future users will need to change it to small caps.

    Thank you Ali for your guidance on the steps. They are really clear and helpful. I'll need to figure out how to integrate it to sets of real data in the next few days! PQ is so powerful! Please don't mind me bugging you on the codes when i encounter some issues...
    Last edited by Polignac; 2021-04-11 at 04:41 AM.

  8. #18
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,651
    Articles
    0
    Excel Version
    Office 365 Subscription
    No problem. Thanks for the typo notice, but this won’t be used again as it’s completely bespoke to you.

    As you have now discovered, you should not be changing anything on the composite worksheet. That updates when the queries are refreshed.

    Each query can be set to refresh automatically, up to once every minute, if desired. You can add a bit of VBA to do the refreshing whenever there’s a worksheet change event, if you wish. PQ is indeed very powerful - I often refer to it as Pandora’s Box.
    Ali
    Enthusiastic self-taught user of MS Excel!

  9. #19
    Acolyte Polignac's Avatar
    Join Date
    Apr 2021
    Posts
    23
    Articles
    0
    Excel Version
    Office 365
    Hi Ali, I'm trying to retrofit the codes to my existing data, which contains more rows of data than before, i.e. from 25 rows of data to 59 rows, in addition, the split columns under Image (Boosted) which contains 4 rows of data, now I have 11 rows of data located at rows 6 to 16, as compared to original files at Rows 12-15, if that make sense to you?

    Which parts of the codes should I change in order to make it work? I'm looking through the codes and none seem to have reference to the new rows until the very end. Thanks

  10. #20
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,651
    Articles
    0
    Excel Version
    Office 365 Subscription
    I can’t visualise what you mean. Can you provide a sample of the layout? Are you talking about the formulae in the last part or something to do with the query?
    Ali
    Enthusiastic self-taught user of MS Excel!

Page 2 of 6 FirstFirst 1 2 3 4 ... 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
  •