Home

Autofill data across columns based on dates

%3CLINGO-SUB%20id%3D%22lingo-sub-711268%22%20slang%3D%22en-US%22%3EAutofill%20data%20across%20columns%20based%20on%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711268%22%20slang%3D%22en-US%22%3E%3CP%3EHaving%20some%20trouble%20autofilling%20a%20spreadsheet%20to%20include%20phased%20revenue%20across%20months.%3C%2FP%3E%3CP%3EColumn%20Headers%20Horizontally%20are%3A%3CBR%20%2F%3EProject%20Name%20Project%20Start%20Date%20Project%20End%20Date%20Project%20Duration%20(Months)%20Monthly%20Value%3C%2FP%3E%3CP%3EThen%20more%20that%20are%20titled%20months%3A%3CBR%20%2F%3EJune%2019%20July%2019%20August%2019%20etc%3C%2FP%3E%3CP%3EI%20want%20to%20be%20able%20to%20put%20a%20formula%20in%20all%20the%20month-headed%20cells%20so%20that%20when%20the%20first%20half%20of%20the%20info%20is%20populated%2C%20they%20autofill%20with%20info%2C%20as%20per%20the%20below%3A%3C%2FP%3E%3CP%3Eeg%3A%3CBR%20%2F%3EName%20%2FStart%20Date%20%2FEnd%20Date%20%2F(Months)%20%2FMonthly%20Value%20%2FJune%2F%20July%20%2FAug%20%2FSept%20%2F%20Oct%3CBR%20%2F%3ELondon%2FJune%2F%20Aug%2F2%20%2F10%20%2F10%20%2F10%20%2F10%3CBR%20%2F%3EBristol%2FJuly%2F%20Oct%2F%204%20%2F45%20%2F%20%2F45%20%2F45%20%2F45%20%2F45%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3CBR%20%2F%3Ethanks%3CBR%20%2F%3EEllie%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-711268%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-711843%22%20slang%3D%22en-US%22%3ERe%3A%20Autofill%20data%20across%20columns%20based%20on%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711843%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F363920%22%20target%3D%22_blank%22%3E%40eeJFTS%3C%2FA%3E%26nbsp%3Bhello%20there.%20I'm%20assuming%20you%20want%20the%20same%20'Monthly%20Value'%20to%20appear%20in%20the%20month%20columns%20dependent%20on%20their%20start%20and%20end%20dates.%20If%20so%2C%20you%20could%20use%20something%20like%20the%20below%20formula.%20It%20was%20used%20in%20cell%20G2%20of%20your%20sample%20spreadsheet.%20You%20can%20copy%20it%20right%20and%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(G%241%26gt%3B%3DEOMONTH(%24C2%2C-1)%2B1%2CG%241%26lt%3B%3DEOMONTH(%24D2%2C0))%2C%24F2%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHTH%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
eeJFTS
Occasional Visitor

Having some trouble autofilling a spreadsheet to include phased revenue across months.

Column Headers Horizontally are:
Project Name Project Start Date Project End Date Project Duration (Months) Monthly Value

Then more that are titled months:
June 19 July 19 August 19 etc

I want to be able to put a formula in all the month-headed cells so that when the first half of the info is populated, they autofill with info, as per the below:

eg:
Name /Start Date /End Date /(Months) /Monthly Value /June/ July /Aug /Sept / Oct
London/June/ Aug/2 /10 /10 /10 /10
Bristol/July/ Oct/ 4 /45 / /45 /45 /45 /45

 

Any ideas?
thanks
Ellie

1 Reply

@eeJFTS hello there. I'm assuming you want the same 'Monthly Value' to appear in the month columns dependent on their start and end dates. If so, you could use something like the below formula. It was used in cell G2 of your sample spreadsheet. You can copy it right and down.

 

=IF(AND(G$1>=EOMONTH($C2,-1)+1,G$1<=EOMONTH($D2,0)),$F2,0)

 

HTH

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies