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

Contributions in input section
Date	Employee	Employer
15/04/2011	1000000	1000
15/07/2011	2000000
15/01/2011	100000
15/09/2012	200000
01/07/2011		500000
15/07/2012	600000
15/01/2012	20000	3000

<tbody>
</tbody>

I might sort it using RANK and produce this output in a separate RANGE, leaving the original data unchanged

Contributions in output section
Date	Employee	Employer
15/01/2011	100000	0
15/04/2011	1000000	1000
01/07/2011	0	500000
15/07/2011	2000000	0
15/01/2012	20000	3000
15/07/2012	600000	0
15/09/2012	200000	0

<tbody>
</tbody>

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