Home

Populating Range of cells for months based on another cells value

%3CLINGO-SUB%20id%3D%22lingo-sub-674913%22%20slang%3D%22en-US%22%3EPopulating%20Range%20of%20cells%20for%20months%20based%20on%20another%20cells%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-674913%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20the%20attached%20workbook%2C%20what%20i%20am%20wanting%20to%20do%20is%20only%20populate%20the%20cells%20A19%3AA34%20based%20on%20the%20month%20in%20cell%20B8.%20The%20month%20in%20Cell%20B8%20will%20change%20based%20on%20other%20formulas.%20Can%20this%20be%20done%20with%20the%20other%20formulas%20staying%20in%20place%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-674913%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-675023%22%20slang%3D%22en-US%22%3ERe%3A%20Populating%20Range%20of%20cells%20for%20months%20based%20on%20another%20cells%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-675023%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339153%22%20target%3D%22_blank%22%3E%40drt_80%3C%2FA%3E%26nbsp%3Bhello.%20I'm%20not%20sure%20I%20fully%20understand%20what%20you%20want.%20A19%20is%20based%20on%20cell%20B6.%20If%20you%20mean%20you%20want%20the%20date%20of%20cell%20B6%20but%20using%20the%20month%20of%20B8%2C%20that%20formula%20would%20be%20changed%20to%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(ISBLANK(B6)%2C%22%22%2CDATE(YEAR(B6)%2CMONTH(B8)%2CDAY(B6)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20A20%3AA34%20are%20based%20on%20the%20cell%20directly%20above%20it%2C%20assuming%20that's%20what%20you%20want%2C%20you%20wouldn't%20need%20to%20change%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you're%20looking%20for%20something%20else%2C%20please%20be%20as%20specific%20as%20possible%20and%20tell%20us%20what%20you%20need.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-675602%22%20slang%3D%22en-US%22%3ERe%3A%20Populating%20Range%20of%20cells%20for%20months%20based%20on%20another%20cells%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-675602%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F19137%22%20target%3D%22_blank%22%3E%40Zack%20Barresse%3C%2FA%3E%26nbsp%3B%20Hello%2C%20sorry%20about%20that%2C%20it%20should%20be%20in%20the%20cell%20range%20A20%3AA34.%20What%20im%20needing%20is%20the%20gray%20table%20to%20populate%20upto%20the%20month%20that%20is%20in%20cell%20B8.%20If%20the%20value%20of%20B8%20is%205%2F20%2F2020%20that%20is%20the%20last%20month%20i%20need%20showing%20up%20in%20the%20gray%20table.%20Then%20if%20the%20value%20in%20B8%20changes%20to%206%2F15%2F2020%2C%20i%20need%20the%20gray%20chart%20to%20automatically%20populate.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-675767%22%20slang%3D%22en-US%22%3ERe%3A%20Populating%20Range%20of%20cells%20for%20months%20based%20on%20another%20cells%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-675767%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339153%22%20target%3D%22_blank%22%3E%40drt_80%3C%2FA%3E%26nbsp%3Blooks%20like%20you'd%20have%20a%20circular%20reference%20by%20pointing%20to%20B8.%20It%20also%20looks%20like%20you've%20duplicated%20the%20calculations.%20Perhaps%20you%20started%20down%20one%20path%20and%20then%20went%20down%20another%3F%20You%20could%20circumvent%20the%20circular%20reference%20by%20putting%20the%20upstream%20calculations%20in%20the%20formula%20itself%2C%20but%20I%20wouldn't%20suggest%20that%20here%20because%20of%20the%20number%20of%20calculations%20you're%20performing%2C%20the%20formula%20would%20get%20very%20messy%20very%20quickly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20a%20quick%20view%20it%20looks%20like%20you're%20trying%20to%20calculate%20a%20number%20of%20%22available%22%20work%20days%20which%20takes%20into%20account%20weekend%2C%20rain%2C%20cold%2C%20and%20wet%20days%2C%20including%20holidays.%20There%20are%20also%20areas%20for%20calculation%20failure.%20The%20most%20obvious%20is%20calculating%20February%20to%20have%2028%20days%2C%20which%20is%20hard-coded.%20You%20could%20probably%20do%20without%20the%20'DO%20NOT%20TOUCH'%20sheet%20entirely%20I'd%20suspect%2C%20as%20well%20as%20consolidating%20some%20of%20your%20other%20ranges%20of%20data.%20I%20would%20suggest%20you%20look%20at%20your%20logic%20of%20rain%2Fcold%2Fwet%20days%20(it%20may%20be%20what%20you%20want%2C%20given%20the%20margin%20of%20error%2C%20I%20just%20don't%20know%20your%20model%20well%20enough%20to%20know%20its%20accuracy).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20suggestion%20is%20to%20go%20back%20to%20the%20drawing%20board%20and%20define%20what%20it%20is%20you're%20trying%20to%20do%20and%20build%20a%20model%20which%20supports%20your%20inputs%20and%20outputs%20as%20desired.%20My%20guess%20is%20this%20started%20out%20much%20simpler%20and%20was%20built%20upon%20without%20full%20knowledge%20of%20the%20end%20product%2C%20thus%20hampering%20'good'%20design.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-675814%22%20slang%3D%22en-US%22%3ERe%3A%20Populating%20Range%20of%20cells%20for%20months%20based%20on%20another%20cells%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-675814%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F19137%22%20target%3D%22_blank%22%3E%40Zack%20Barresse%3C%2FA%3E%26nbsp%3B%20Thank%20you%20for%20your%20help.%20It%20started%20out%20very%20simple%20and%20users%20wanted%20to%20add%20everything%20it.%20I%20may%20scrap%20it%20and%20start%20over.%20Thanks%20for%20your%20help.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-675823%22%20slang%3D%22en-US%22%3ERe%3A%20Populating%20Range%20of%20cells%20for%20months%20based%20on%20another%20cells%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-675823%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339153%22%20target%3D%22_blank%22%3E%40drt_80%3C%2FA%3E%26nbsp%3BThat%20is%20my%20suggestion%2C%20although%20I'm%20aware%20it's%20costing%20someone%20additional%20work.%20My%20recommendation%20for%20design%20is%20to%20keep%20it%20as%20tabular%20and%20relational%20as%20possible.%20Good%20luck%20and%20post%20back%20if%20you%20need%20anything%20else.%3C%2FP%3E%3C%2FLINGO-BODY%3E
drt_80
Occasional Contributor

In the attached workbook, what i am wanting to do is only populate the cells A19:A34 based on the month in cell B8. The month in Cell B8 will change based on other formulas. Can this be done with the other formulas staying in place?

5 Replies

@drt_80 hello. I'm not sure I fully understand what you want. A19 is based on cell B6. If you mean you want the date of cell B6 but using the month of B8, that formula would be changed to:

 

=IF(ISBLANK(B6),"",DATE(YEAR(B6),MONTH(B8),DAY(B6)))

 

Since A20:A34 are based on the cell directly above it, assuming that's what you want, you wouldn't need to change them.

 

If you're looking for something else, please be as specific as possible and tell us what you need.

@Zack Barresse  Hello, sorry about that, it should be in the cell range A20:A34. What im needing is the gray table to populate upto the month that is in cell B8. If the value of B8 is 5/20/2020 that is the last month i need showing up in the gray table. Then if the value in B8 changes to 6/15/2020, i need the gray chart to automatically populate.

@drt_80 looks like you'd have a circular reference by pointing to B8. It also looks like you've duplicated the calculations. Perhaps you started down one path and then went down another? You could circumvent the circular reference by putting the upstream calculations in the formula itself, but I wouldn't suggest that here because of the number of calculations you're performing, the formula would get very messy very quickly.

 

From a quick view it looks like you're trying to calculate a number of "available" work days which takes into account weekend, rain, cold, and wet days, including holidays. There are also areas for calculation failure. The most obvious is calculating February to have 28 days, which is hard-coded. You could probably do without the 'DO NOT TOUCH' sheet entirely I'd suspect, as well as consolidating some of your other ranges of data. I would suggest you look at your logic of rain/cold/wet days (it may be what you want, given the margin of error, I just don't know your model well enough to know its accuracy). 

 

My suggestion is to go back to the drawing board and define what it is you're trying to do and build a model which supports your inputs and outputs as desired. My guess is this started out much simpler and was built upon without full knowledge of the end product, thus hampering 'good' design.

 

@Zack Barresse  Thank you for your help. It started out very simple and users wanted to add everything it. I may scrap it and start over. Thanks for your help. 

@drt_80 That is my suggestion, although I'm aware it's costing someone additional work. My recommendation for design is to keep it as tabular and relational as possible. Good luck and post back if you need anything else.

Related Conversations