How to make a master sheet of expiration dates

%3CLINGO-SUB%20id%3D%22lingo-sub-2758070%22%20slang%3D%22en-US%22%3EHow%20to%20make%20a%20master%20sheet%20of%20expiration%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2758070%22%20slang%3D%22en-US%22%3EI'm%20trying%20to%20make%20one%20master%20sheet%20that%20keeps%20track%20of%20expiration%20dates%20across%20multiple%20sheets.%20I%20tried%20using%20Query%2C%20but%20couldn't%20figure%20out%20how%20to%20make%20it%20not%20show%20dates%20that%20weren't%20expiring%20in%20the%20current%20month.%20I%20tried%20a%20couple%20different%20conditional%20formats%20but%20just%20can't%20make%20it%20work.%20Any%20advice%20would%20be%20appreciated.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2758070%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%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-2758657%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20a%20master%20sheet%20of%20expiration%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2758657%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1158331%22%20target%3D%22_blank%22%3E%40MyUsername30%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWithout%20any%20additional%20information%20(or%20a%20copy%20of%20the%20workbook)%20it's%20hard%20to%20give%20a%20specific%20and%20appropriate%20answer.%20From%20what%20little%20you've%20said%2C%20I'd%20suggest%20looking%20at%20the%20feasibility%20of%20the%20INDIRECT%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20from%20a%20design%20perspective%2C%20I%20almost%20always%20want%20to%20know%20why%20there%20are%20multiple%20spreadsheets%3B%20many%20people%20create%20multiple%20sheets%20within%20a%20workbook%20because%20that's%20how%20they%20tracked%20X%20--whatever%20it%20is%20you're%20tracking--when%20doing%20it%20on%20paper.%20But%20Excel%20can%20track%20very%20well%2C%20and%20report%20on%20distinctive%20components%2C%20when%20all%20the%20records%20are%20kept%20in%20a%20single%20database.%20So%20that%20would%20be%20another%20question%20I'd%20want%20to%20explore.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20link%20to%20help%20you%20assess%20the%20applicability%20of%20INDIRECT%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-indirect-function%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-indirect-function%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor
I'm trying to make one master sheet that keeps track of expiration dates across multiple sheets. I tried using Query, but couldn't figure out how to make it not show dates that weren't expiring in the current month. I tried a couple different conditional formats but just can't make it work. Any advice would be appreciated.
2 Replies

@MyUsername30 

 

Without any additional information (or a copy of the workbook) it's hard to give a specific and appropriate answer. From what little you've said, I'd suggest looking at the feasibility of the INDIRECT function.

 

But from a design perspective, I almost always want to know why there are multiple spreadsheets; many people create multiple sheets within a workbook because that's how they tracked X --whatever it is you're tracking--when doing it on paper. But Excel can track very well, and report on distinctive components, when all the records are kept in a single database. So that would be another question I'd want to explore.

 

Here's a link to help you assess the applicability of INDIRECT: https://exceljet.net/excel-functions/excel-indirect-function

 

@MyUsername30 When you say you tried to use "Query", do you mean Power Query? Would be helpful to see how your actual workbook looks like, with an example of what you expect to see in the Master sheet. Replace any sensitive data in the workbook before uploading, though.