Excel - Importing data as text then manipulating

[tannersarms]

I am regularly sent a price list in text format, which I open in Excel, comma delimited, and then make it look pretty and readable by changing the column width, and also do stuff like add new columns as the price list only shows retail price with the percentage I have to remit, so I put in columns applying the percentage to the retail so I can figure out my cost.

Now, as they do, prices change, and currently I just start from scrap, open excel, file, open, select the txt file and use the wizard to convert it, but I want to know if I can set up an excel spreadsheet with my changes preprogrammed in and then use the data import tool to bring in the text and fix it up automatically?


Cheers for any assistance

Mark

[Chris Locke]

Regular things like adding a column, changing formatting, etc, can be recorded to a macro. You could then add to the macro, to go through each row and add the formula as required.

[BrynJ]

I think Import External Data (available in the Data dropdown) may be what you need. Open up a blank workbook, use the Import External Data tool, do all your formatting, then look at the Data Range Properties to decide how you want to refresh your data (file prompt, auto-refresh etc). There's an option called 'Fill down formulas in columns adjacent to data', I think this is what you require to replicate your formulas on the imported data.

Experimentation probably required :)