SOLVED

Categories & Sub-Categories

%3CLINGO-SUB%20id%3D%22lingo-sub-2911391%22%20slang%3D%22en-US%22%3ECategories%20%26amp%3B%20Sub-Categories%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2911391%22%20slang%3D%22en-US%22%3E%3CP%3EI%20downloaded%20a%20daily%20expense%20template%20online%20and%20i'm%20trying%20to%20modify%20it%20for%20my%20use.%20I%20added%20a%20new%20column%20for%20sub-categories%20(office%20supplies)%20but%20cannot%20get%20it%20show%20on%20the%20expense%20capture%20sheet.%20How%20can%20I%20do%20this%2C%20cos%20I%20need%20to%20be%20able%20to%20periodically%20update%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2911391%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2912214%22%20slang%3D%22en-US%22%3ERe%3A%20Categories%20%26amp%3B%20Sub-Categories%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2912214%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1202881%22%20target%3D%22_blank%22%3E%40Jaspyroman%3C%2FA%3E%26nbsp%3BSee%20attached.%20Fixed%20it%20for%20you.%20This%20particular%20template%20uses%20Data%20validation%20with%20INDIRECT%20references%20to%20structured%20tables.%26nbsp%3BMore%20about%20INDIRECT%20in%20the%20link%20below.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Findirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Findirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20trick%20is%20that%20you%20need%20to%20create%20a%20a%20structured%20table%20for%20the%20new%20sub-category.%20%26nbsp%3BThe%20name%20of%20the%20new%20table%20should%20be%20exactly%20the%20sames%20as%20the%20name%20of%20the%20main%20category%2C%20but%20it%20may%20NOT%20contain%20spaces.%3C%2FP%3E%3CP%3EMore%20about%20structured%20Excel%20tables%20in%20the%20link%20below.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fcreate-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fcreate-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I%20renamed%20the%20Main%20category%20to%20%22OfficeSupplies%22%20(without%20the%20space)%20and%20the%20list%20with%20sub-categories%20on%20sheet%20%22C%22%20in%20column%20L%20is%20now%20a%20structured%20table%20called%20%22OfficeSupplies%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I downloaded a daily expense template online and i'm trying to modify it for my use. I added a new column for sub-categories (office supplies) but cannot get it show on the expense capture sheet. How can I do this, cos I need to be able to periodically update it.

2 Replies
best response confirmed by Jaspyroman (New Contributor)
Solution

@Jaspyroman See attached. Fixed it for you. This particular template uses Data validation with INDIRECT references to structured tables. More about INDIRECT in the link below.

https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261 

 

The trick is that you need to create a a structured table for the new sub-category.  The name of the new table should be exactly the sames as the name of the main category, but it may NOT contain spaces.

More about structured Excel tables in the link below.

https://support.microsoft.com/en-us/office/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0a... 

 

So, I renamed the Main category to "OfficeSupplies" (without the space) and the list with sub-categories on sheet "C" in column L is now a structured table called "OfficeSupplies".

 

 

That's great @Riny_van_Eekelen. Thanks so much. I will keep in touch please, if you don't mind.