MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by steve morgan on May 05, 2001 12:01 PM


Each month I have to import a file from our antiquated UNIX system and convert it to an excel spreadsheet. I open the file using excel and then have to space out the columns and change the column type (date and text and general). Then I have to bring the data into excel and do some more manipulation.
Is there a way to set up a repetitive action so that I don't have to re-invent the wheel each month?=

Thanks to all

Posted by Dave Hawley on May 06, 2001 2:18 AM

Hi Steve

The manipulation of your data, once in Excel could certainly be fully automated via the use of VBA. Whether this is big task or not depends on exactly what it is you are doing to manipulate it.

OzGrid Business Applications

Posted by Mike on May 06, 2001 8:40 AM


One possible approach is this: Bring your data into a separate worksheet, say, Sheet1. Then, by using simple formulas, link the data to your "presentation" worksheet, say Sheet2. Then a cell on Sheet2 could have the formula, for example, (A1) = Sheet1!A1.

This scheme allows you to set the column width, number format, point size, font, etc., etc., on your presentation worksheet. You can set most of the format variables on a cell by cell basis.

You could take an additional step and "lock" the cells on the presentation worksheet so they cannot be inadvertently changed.

I am using this approach to display subsets of my data on different worksheets. By using simple links as described above, I do not have to put the entire matrix on the additional worksheets. I can put my data wherever I want them, formatted as desired, and surrounded by appropriate lables and units.

Hope this is useful.

(I too am going from a Unix system to Excel