Importing Data under multiple categories, and in Alphabetical Order

%3CLINGO-SUB%20id%3D%22lingo-sub-2281812%22%20slang%3D%22en-US%22%3EImporting%20Data%20under%20multiple%20categories%2C%20and%20in%20Alphabetical%20Order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281812%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%E2%80%99m%20not%20sure%20if%20this%20has%20been%20addressed%20in%20earlier%20discussions%2C%20however%20I%20need%20a%20little%20help%20in%20determining%20the%20next%20step%20in%20creating%20two%20different%20sheets%20I%20am%20currently%20working%20on.%20The%20first%20sheet%20is%20an%20easy%20reference%20sheet%20with%20multiple%20categories%20that%20others%20can%20look%20at%20throughout%20the%20day.%20The%20second%20sheet%20is%20a%20Master%20sheet%20with%20multiple%20forms%20of%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20would%20like%20to%20do%20is%20simply%20press%20a%20checkbox%20located%20next%20to%20a%20specific%20piece%20of%20data%20on%20the%202nd%20sheet%2C%20and%20have%20that%20data%20imported%20to%20the%201st%20sheet%20under%20the%20proper%20category%20and%20alphabetical%20order.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20not%20sure%20what%20formulas%20or%20options%20I%20need%20to%20look%20at%20from%20this%20point%2C%20if%20you%20could%20send%20me%20point%20me%20in%20the%20right%20direction%2C%20I%20would%20appreciate%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2281812%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282595%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20Data%20under%20multiple%20categories%2C%20and%20in%20Alphabetical%20Order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282595%22%20slang%3D%22en-US%22%3ECan%20you%20post%20an%20anonymized%20example%20perhaps%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2291613%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20Data%20under%20multiple%20categories%2C%20and%20in%20Alphabetical%20Order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2291613%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1035737%22%20target%3D%22_blank%22%3E%40MattyMatt880%3C%2FA%3E%26nbsp%3BAn%20alternative%20might%20be%20to%20use%20pivot%20tables.%20In%20the%20attached%2C%20I%3A%3C%2FP%3E%0A%3CP%3E-%20Removed%20the%20checkboxes%20(they%20are%20a%20disaster%20to%20maintain%20and%20you%20can't%20easily%20get%20data%20from%20them%20unless%20your%20set%20their%20properties%20one%20by%20one)%20and%20replaced%20with%20a%20simple%201%20in%20a%20%22checked%22%20row%3C%2FP%3E%0A%3CP%3E-%20Created%20a%20Pivot%20table%2C%20filtered%20by%20the%20first%20column%20%3D%201%20and%20the%20column%20which%20shows%20Cantrips%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20add%20more%20types%2C%20simply%20copy%20the%20pivot%20table%20and%20paste%20below%20the%20existing%20one%2C%20add%20some%20categories%20to%20your%20data%2C%20click%20the%20Refresh%20All%20button%20(on%20the%20Data%20tab)%20and%20adjust%20the%20filter%20for%20the%20second%2C%20third%20...%20pivot%20table.%20After%20adjusting%20your%20data%2C%20just%20click%20refresh%20all%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, I’m not sure if this has been addressed in earlier discussions, however I need a little help in determining the next step in creating two different sheets I am currently working on. The first sheet is an easy reference sheet with multiple categories that others can look at throughout the day. The second sheet is a Master sheet with multiple forms of data.

 

What I would like to do is simply press a checkbox located next to a specific piece of data on the 2nd sheet, and have that data imported to the 1st sheet under the proper category and alphabetical order.

 

I am not sure what formulas or options I need to look at from this point, if you could send me point me in the right direction, I would appreciate it.

7 Replies
Can you post an anonymized example perhaps?

@Jan Karel Pieterse Sure. The sheets I am referring to are the Spell Sheet (sheet 1) and The Complete Spell List (sheet 2). What I want to be able to do is click the checkbox located on the far left of each spell on Sheet 2, and have that information appear in alphabetical order on Sheet 1 underneath the appropriate Level, and in alphabetical Order. So Cantrips will appear under cantrips, level one spells under level one, so on and so forth. 

@MattyMatt880 An alternative might be to use pivot tables. In the attached, I:

- Removed the checkboxes (they are a disaster to maintain and you can't easily get data from them unless your set their properties one by one) and replaced with a simple 1 in a "checked" row

- Created a Pivot table, filtered by the first column = 1 and the column which shows Cantrips

 

To add more types, simply copy the pivot table and paste below the existing one, add some categories to your data, click the Refresh All button (on the Data tab) and adjust the filter for the second, third ... pivot table. After adjusting your data, just click refresh all again.

@Jan Karel Pieterse No. That will not work. That format is too confusing even for me, and I know my players will have a hard time with it. I'm trying to use the sheet you sent me, and I cannot get different spells added to the alternative spell list sheet you created. I keep putting =1, or 1 in the column you suggested, and the spells are not appearing where I would like them to. Is there another alternative option that would be more user friendly?

You'll see that the 1 only helps in adding spells to the sheet I created. Which exact version of Excel do you have?

@Jan Karel Pieterse Sorry for my belated response. I have Microsoft 365, so it would be whatever version came with that. 

@MattyMatt655 Here is another take on this. I only did one category.