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
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