I have 100 worksheets from a doctor's office. Each row is data from a patient visit. Each cell in a row contains string data about the patient (name, blood pressure, etc.) as well as the date of the exam, diagnositcs, etc.
I want to create 100 new worksheets, but with some fairly basic manipulations performed on each cell. For instance, I want to separate the patient's name so that there is a cell with just the first name, and a cell with just the last name. I want to delete the word "DATE" which precedes the actual date in each cell. There will still be one row per patient visit, but there will me more columns (since I'm separating patient name into firstname/lastname).
I want each final worksheet to have string data, just like the source worksheet, and not have any formulas or references to the original worksheets.
All of this is very simple to do with formulas, and if I only had one worksheet, I'd insert various columns and create the formulas needed to do the work. I'd then "copy/paste special" to copy the results to the columns in the new worksheet, putting the results into columns in a different column order than in the original worksheet.
But, I have 100 worksheets.
I am using Excel 2003. I am very knowledgeable about how to use Excel, and reasonably proficient with VBA.
Question: Can anyone recommend a simple approach to solve this problem?
The solution I am going to try, if I don't get a better idea here, is to create a worksheet with a macro which prompts the user for the worksheet name of the first worksheet. I was then going to have the first row be the column names of the source worksheet, but in the order I want them to appear in the final worksheet. Below that, I was going to put the formulas for parsing each of these columns. And, below that, I was going to put the results of these formulas for the first row in the source worksheet.
The idea here is that I can modify the column order by simply putting different column letters in this template worksheet, and I can modify my parsing logic by changing the formulas.
However, this leads to my second and final question.
If what I outline is a sensible approach, is there a way in which VBA can use formulas contained in a cell in a spreadsheet, and apply them to a range?
This is a crude example of what I'm trying to do. The formulas are mostly bogus, just to indicate the idea, and I'm not using the file name or column letters to extract data. The key thing is that I want to apply the formulas in B5:H5 to the columns from the original spreadsheet, as shown by the column letters in row 3. I want to do this for every row in the source worksheet, and output the results to a new worksheet.
http://dl.dropbox.com/u/1561578/test.xls
If this is valid, my main stumbling block is knowing a way for VBA to use the formulas shown in B5:H5. I know I can hard-code those formulas in VBA, but I want the flexibility and interactivity of being able to modify them within the template and immediately see the results.
Thanks!!
I want to create 100 new worksheets, but with some fairly basic manipulations performed on each cell. For instance, I want to separate the patient's name so that there is a cell with just the first name, and a cell with just the last name. I want to delete the word "DATE" which precedes the actual date in each cell. There will still be one row per patient visit, but there will me more columns (since I'm separating patient name into firstname/lastname).
I want each final worksheet to have string data, just like the source worksheet, and not have any formulas or references to the original worksheets.
All of this is very simple to do with formulas, and if I only had one worksheet, I'd insert various columns and create the formulas needed to do the work. I'd then "copy/paste special" to copy the results to the columns in the new worksheet, putting the results into columns in a different column order than in the original worksheet.
But, I have 100 worksheets.
I am using Excel 2003. I am very knowledgeable about how to use Excel, and reasonably proficient with VBA.
Question: Can anyone recommend a simple approach to solve this problem?
The solution I am going to try, if I don't get a better idea here, is to create a worksheet with a macro which prompts the user for the worksheet name of the first worksheet. I was then going to have the first row be the column names of the source worksheet, but in the order I want them to appear in the final worksheet. Below that, I was going to put the formulas for parsing each of these columns. And, below that, I was going to put the results of these formulas for the first row in the source worksheet.
The idea here is that I can modify the column order by simply putting different column letters in this template worksheet, and I can modify my parsing logic by changing the formulas.
However, this leads to my second and final question.
If what I outline is a sensible approach, is there a way in which VBA can use formulas contained in a cell in a spreadsheet, and apply them to a range?
This is a crude example of what I'm trying to do. The formulas are mostly bogus, just to indicate the idea, and I'm not using the file name or column letters to extract data. The key thing is that I want to apply the formulas in B5:H5 to the columns from the original spreadsheet, as shown by the column letters in row 3. I want to do this for every row in the source worksheet, and output the results to a new worksheet.
http://dl.dropbox.com/u/1561578/test.xls
If this is valid, my main stumbling block is knowing a way for VBA to use the formulas shown in B5:H5. I know I can hard-code those formulas in VBA, but I want the flexibility and interactivity of being able to modify them within the template and immediately see the results.
Thanks!!
