For input files of variable length, I want to avoid using "Fill" down to set up the correct number of formulae

[Robert Lush]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:DoNotShowComments/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>BN</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:DontVertAlignCellWithSp/> <w:DontBreakConstrainedForcedTables/> <w:DontVertAlignInTxbx/> <w:Word11KerningPairs/> <w:CachedColBalance/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--> I want to input sets of data and process them into a new ranges. Each set that I input will be of a variable length in rows.

I want to be able to process this data using Excel formulae, but without having to manually “Fill” down the formulae according to the number of rows in the input RANGE. I can see how I might do this in VBA but I was wondering whether there is a way to do this without using VBA. One alternative might be always to have the formulae in every single row of the Worksheet, or the part of the original Worksheet, where the output is sent. But this seems so inelegant.

For example, here is some sample input data


[TABLE="width: 210"]
<tbody>[TR]
[TD="colspan: 3"]Contributions in input section
[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Employee[/TD]
[TD]Employer[/TD]
[/TR]
[TR]
[TD="align: right"]15/04/2011[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]15/07/2011[/TD]
[TD="align: right"]2000000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15/01/2011[/TD]
[TD="align: right"]100000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15/09/2012[/TD]
[TD="align: right"]200000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2011[/TD]
[TD][/TD]
[TD="align: right"]500000[/TD]
[/TR]
[TR]
[TD="align: right"]15/07/2012[/TD]
[TD="align: right"]600000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15/01/2012[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]3000


[/TD]
[/TR]
</tbody>[/TABLE]
I might sort it using RANK and produce this output in a separate RANGE, leaving the original data unchanged

[TABLE="width: 225"]
<tbody>[TR]
[TD="colspan: 3"]Contributions in output section
[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Employee[/TD]
[TD]Employer[/TD]
[/TR]
[TR]
[TD="align: right"]15/01/2011[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]15/04/2011[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]500000[/TD]
[/TR]
[TR]
[TD="align: right"]15/07/2011[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]15/01/2012[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]3000[/TD]
[/TR]
[TR]
[TD="align: right"]15/07/2012[/TD]
[TD="align: right"]600000[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]15/09/2012[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

I'd at least like to know if this is possible. I can then probably work my way through it.

Thanks

 

[Norie]

Perhaps you could use a table.

When you append data to columns in table any formulas in other columns will automatically be replicated.

 

[Robert Lush]

Thanks Norie,

I've not used Tables up to now, but it looks to be worthwhile learning to use them. I'll let you know how I got on in a day or so.

Robert

 

[Robert Lush]

Thanks Norie,

I've not used Tables up to now, but it looks to be worthwhile learning to use them. I'll let you know how I got on in a day or so.

Robert

Norie. This did what I wanted, although I had to rewrite some of my formlae. If I have fixed cell references such as $A$5 then the new rows I might add will not amend these. SO I'll do it a different way. It's useful to know that the Tables functionality exists, especially as it is closely related to PivotTable applications. So I have another tool for my toolbox. Thank you

Robert