• 0

Can Excel live query multiple sheets and print it?


Question

I'm not sure if I'm using the correct terminology here. What I have is a spreadsheet with multiple workbooks for a card collection. The cards are divided by color, and then there's a fifth workbook for the new set, and that workbook includes all four colors. Each workbook includes all the cards that I know to be available in the set, even if I don't have the card here. One purpose of the spreadsheet is to tell me where the cards go in a binder, so that if I acquire a new card, I don't have to shift them all X spaces. I've done that before and it is such a PITA. So I have one binder with four sections that was enough for the entire collection before the latest set came out, but not the latest set, so it gets its own binder.

 

What I want is a query (I think that's the right term) that pulls data from each workbook, and lists only the entries where I own zero cards, so I always have a live sheet of what I'm missing. And when I acquire something and add to its quantity, that card's entry would vanish from that workbook. Is that a thing Excel can do automatically?

 

I'd also like to do the opposite. Any time I list more than four cards, I'd like them to be listed on a separate workbook. I have something like this made manually (the one called Extra), but it would be cool if it was automatic, and I could re-add those cards to the regular workbooks. It would just be easier to see what I have at a glance.

 

As a slightly more advanced feature... the game in question allows us to have no more than 4 of any one particular card in a deck. So I would want a second query that lists cards where I own fewer than 4, and how many I am missing from having 4. And then have it sorted by cards where I own zero, cards where I own one, cards where I own two, and lastly, cards where I already have three.

 

I consider myself to be pretty good with Excel (and I enjoy using it!), but there's still so much I don't know. Honestly, compared with people who use it professionally, I am but a novice. Compared to people I know in real life, however... I'm a wizard.

 

Now if you're wondering, the game is Weiß Schwarz, and I play the Sword Art Online set. If you'd like to take a look at the spreadsheet (there's really no personal information in it, as far as I know), here's a link to it on OneDrive (view only). The links in the yellow workbook go to Google Photos scans of the cards — please be aware a couple of them are slightly risque and potentially NSFW. Eventually I'm going to scan all my cards and link to all of them I have scans for. I play the game because I like the show, from the characters to the art style, but it also helps to be able to see the card and what it does without having to open the binder. And of course I might have the binder at home and want to pull up my cards at work, on my laptop, or on my phone. I mainly use my spreadsheet to aid in deck building (and archiving old decks, you'll see I have such a deck in the red workbook, I dismantled the deck so my best friend could build a similar one, so those cards do not count toward total ownership, but his do). But I'm asking about the queries because it can help me acquire the cards I'm missing. I can share the spreadsheet with local collectors and sellers, who can then make me offers on cards I'm missing that they're selling, or vice versa.

1 answer to this question

Recommended Posts

  • 0
  On 17/01/2018 at 11:22, dragontology said:

nd that workbook includes all four colors. Each workbook includes all the cards that I know to be available in the set, even if I don't have the card here. One purpose of the spreadsheet is to tell me where the cards go in a binder, so that if I acquire a new card, I don't have to shift them all X spaces. I've done that before and it is such a PITA. So I have one binder with four sections that was enough for the entire collection before the latest set came out, but not the latest set, so it gets its own binder.

 

What I want is a query (I think that's the right term) that pulls data from each workbook, and lists only the entries where I own zero cards, so I always have a live sheet of what I'm missing. And when I acquire something and add to its quantity, that card's entry would vanish from that workbook. Is that a thing Excel can do automatically?

Expand  

Have you tried using a Pivot table to summarize/display your data? Convert your current data into a Table first (ctrl+T) and then use the table to use as a source for your Pivot table. Try going to Pivot table preferences > View as table to display the output in different cells (resulting in a more like a conventional table layout) 

 

To combine different data sets (tables) into one Pivot table you can add the tables in a data model using Power Pivot. Google is your friend here as it requires many steps and error checking. 

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Amazon Deal: This Vifa Stockholm 2.0 is one of the best sounding bluetooth speakers by Sayan Sen A few days back we covered some great JBL bluetooth speaker deals across several of its popular models. The discounts are still live and you can check them out in this dedicated piece. Meanwhile for those who prefer more powerful home cinema sound systems, Nakamichi and Samsung are offering the Dragon and Shockwafe models, and the Q-series models, respectively, at their best ever prices. However, if you are someone who is looking for a bit of both, the portability of a bluetooth speaker and the fidelity of a good sounding hi-fi system then the Vifa Stockholm 2.0 Bluetooth Soundbar is something you should look at as it's currently a nice deal (purchase link under the specs table below). While you are not getting a subwoofer with the Vifa Stockholm as it is a 2.0 system, Vifa promises lows down to 42 Hz at +3dB and that should be pretty good for a device lacking a dedicated bass unit; it does pack passive radiators to help with the bass. The Stockholm 2.0 is praised for its sound quality (SQ) and one of the reasons behind it is becasue it has three-way drivers. The technical specifications of the Vifa Stockholm 2.0 Bluetooth Soundbar are given below: Specification Frequency Response 42 Hz – 20 kHz @ ±3 dB Materials Frame: One-piece die-cast aluminium; Enclosure: ABS reinforced; Grills: Kvadrat textile Connectivity Bluetooth® Qualcomm aptX™ HD audio; Wi-Fi Direct & networked (2.4 GHz); Wired optical or analog (3.5 mm mini-jack); USB-disk; Vifa®HOME, Vifa®LINK, Vifa®PLAY Driver Units Tweeter: 2 × 28 mm soft-dome drivers; Midrange: 2 × 80 mm aluminium-cone drivers; Woofer: 4 × 100 mm flat sandwich-cone drivers (force-balanced, backed by 4 passive radiators) Other Features Apple AirPlay & DLNA streaming; DSP signal processing; 6-channel high-performance power amplifier Get it at the link below: Vifa Stockholm 2.0 Bluetooth Soundbar, Nordic Design Soundbar, Smart APP Multi-Room System (Slate Black): $1156.99 (Sold and Shipped by Amazon US) This Amazon deal is US-specific and not available in other regions unless specified. If you don't like it or want to look at more options, check out the Amazon US deals page here. Get Prime (SNAP), Prime Video, Audible Plus or Kindle / Music Unlimited. Free for 30 days. As an Amazon Associate, we earn from qualifying purchases.
    • Explzh 9.81 by Razvan Serea Explzh is a free Windows archive manager for creating, extracting and managing archives. The program supports many different types of archives, including zip, 7z, rar, tar, ace, lzh, arj, cab, iso, img, msi, sfx and more. Apart from archive creation and extraction, you will also be able to verify compressed data for errors, initiate repair routines, split data into multiple items, and more. It additionally allows you to password protect your data and attach digital signatures to files. Key features of Explzh: Explorer-like GUI and operability. LHA, ZIP (ZIPX), JAR, CAB, RAR, TAR, TAR.XXX, 7z, ARJ, WIM, CHM, PE, HFS, NSIS Format Installer, ISO, InstallShield, MSI, and several other formats... Support for more archive formats by introducing the integrated archiver DLL. Self-extracting archive creation function that can create high-performance automatic installers. Digital signature addition function to created self-extracting archive. Office 2007 or later document compression / image optimization re-archiving function. Supports compression and decompression of Unicode file names. Supports compression and expansion exceeding 4GB. AES encryption function. You can create a robust secure ZIP encryption archive. Thumbnail function of image file. In-library file search function. . Equipped with archive file conversion function. File split function. The split file has a self-consolidation function, and can concatenate files larger than 4GB. (No need for batch file or connection software) UU (XX) Encode, Base64 decode function. FTP upload function Supports Windows 11 shell integration extended context menu. Explzh 9.81 changelog: Improved to send update notifications to the shell when making changes such as additional compression to existing zip and 7-zip files. This also updates the Explorer view of the open file in real time. (If the drop target feature is enabled, you can easily create an encrypted ZIP by dragging and dropping onto the ZIP icon while holding down the Ctrl key.) When the zip drop target setting is enabled, the "Compressed (zipped) Folder" item will be added to the "New" shell context menu if it does not already exist. Password manager bug fix: Fixed a bug that caused the app to crash when reading password.dat (password data) when changing authentication method. Updated to Visual Studio 2022 v.17.14.9. Download: Explzh 64-bit | Explzh 32-bit | ~6.0 MB (Freeware) Download: Explzh ARM64 | 5.9 MB View: Explzh Home Page | Screenshot | Themes Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Have to get that indoctrination in early I guess
    • The reason why I'm using as much portable software as possible.
  • Recent Achievements

    • One Month Later
      Johnny Mrkvička earned a badge
      One Month Later
    • Week One Done
      Sender88 earned a badge
      Week One Done
    • Dedicated
      Daniel Pinto earned a badge
      Dedicated
    • Explorer
      DougQuaid went up a rank
      Explorer
    • One Month Later
      MIghty Haul earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      604
    2. 2
      Michael Scrip
      200
    3. 3
      ATLien_0
      191
    4. 4
      +FloatingFatMan
      138
    5. 5
      Xenon
      126
  • Tell a friend

    Love Neowin? Tell a friend!