Filling down formulas

[marketshare]

I need to 'fill down' a series of formulas in several columns - but the number of rows I need to fill down will vary dependent on my raw data.

See attached file.

Worksheet 1 - contains the raw data which is copied and pasted in from an external source. In this example, there are 20 records.

Worksheet 2 - brings in the raw data from worksheet 1 and does some calculations. The formulas are in the first 30 rows - in case the number of records grow. But what happens if the number of records is, say, 40?

I have a much bigger workbook in reality - so having too many formulas will increase the file size.

Can anyone please suggest a better way? Thanks.

[Fotis1991]

I don't believe that using these simple formulas you'll have speed problem. I believe that you are in the right road.

Only suggestion could be to don't use whole columns references but be more specific. Something like this(for column D-same for the others)

Please Login or Register  to view this content.

[benishiryo]

hi there. how big is much bigger? there's no doubt the file size will increase, but it shouldn't be a big problem. and it seems like you just want everything from Raw Data + an additional column. why not just copy everything over to a new sheet if you must. then column D is a simple:
=B2*C2

you can use Pivot Table, but it's going to increase your file size too (maybe even more). but it definitely processes faster than formulas if you have a huge workbook. after doing a Pivot, use calculated fields for the Pay. and to ensure your new data gets captured the next time you refresh, convert your raw data to a Table first by doing a CTRL + T on cell A1.

go to Insert -> Pivot Table. put all data in Row Labels. go to Design -> Report Layout -> Tabular. right-click on the subtotals you don't want & uncheck the Subtotal. go to Options -> Fields, Items & Sets -> Calculated Field. Name it Pay. Formula:
='Hours worked'* 'Hourly rate'

Simple example.xlsx

[marketshare]

Thanks for your suggestions Fotis1991 and benishiryo.

My workbook actually takes data from two separate worksheets and uses several formulas - mainly lookup formulas like INDEX and MATCH. I am trying to match customers in two different databases and there is no key field! There are around 3,000 records in each database and this will grow to 5,000.

I often find myself having to fill down formulas and using if(x="","",etc) to suppress results for unpopulated records. I just thought there might be a better way