0

I have an excel file that relies on an external CSV file which I reload from time to time.

The csv is loaded into a sheet and a different sheet is used to perform calculations.

My question is - How do I extend the formula to the number of rows on the external data sheet?

Thanks in advance!

Clarification

I see that my meaning was not understood.

I have x rows in the source table. I need to have x rows in the computation table.
How do I automaticaly fill the target computation table with 10 rows (they will all be formulas) For example:

Before the reload the src table had 3 rows, so the target table will have 3 rows of formula enter image description here

after the refresh 3 rows were added to the source. I want the target to automatically add 3 rows of formula: enter image description here

  • Are you dumping the source data in, or have you set up a live connection to it? – CLockeWork Jun 23 '14 at 10:00
  • Live connection to a Csv file where it asks me the file name whenever I refresh and then it automatically fills the content – Ben Jun 23 '14 at 10:01
  • If the data is entering the sheet as a table then you can just add your formulas at the end of the table and the formula rows will extend as far as the table does. – CLockeWork Jun 23 '14 at 10:19
0

I would get the CSV file data using the Data ribbon / From Text File button. At the end of the import wizard, when asked Select how you want to view this data in your workbook, I would check the option to Add this data to the Data Model.

The result will be an Excel Table. You can add your formula adjacent to the last column and it will be automatically copied down to all rows. When you use the Refresh command, the number of rows will grow or reduce based on the Text file data. Any formulas will be copied to the new rows, or removed from deleted rows.

|improve this answer|||||
  • I was never asked "how I want to view this data".. I'm using excel 2010. (using Data Tab in ribbon -> under "Get External Data" section -> From Text, Is that right?) – Ben Jun 24 '14 at 4:50
  • My bad - the Data Model feature arrived with Excel 2013. – Mike Honey Jun 24 '14 at 5:14
  • You do know you can buy just Excel 2013 for about $100? – Mike Honey Jun 24 '14 at 5:22
  • I'm afraid it's not up to me... – Ben Jun 24 '14 at 5:23
0

This is just an example. Say we have formulas in Sheet1 that process csv-loaded data in Sheet2

Rather than use a formula like:

=SUM(Sheet2!A1:A12)

use instead:

=SUM(Sheet2!A:A)

This will work regardless of the number of filled rows.

|improve this answer|||||
  • Watch out using full column selection (like A:A), this might have some performance impact an you should test with larger datasets. But all to often i do the same. Another way is to use tables and use the table reference that can grow automatically as needed. – Ragnar Von Lodbrok Jul 23 '18 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.