formatting

[getyourbiglobster]

hello, i'm new here.

i am trying to ease the pain in taking an existing excel workbook and modifying it so that it can be in the proper format for importing into our database.

an image of what i'm trying to achieve is here--> http://www.onertia.com/xl.gif

the top workbook in the image is the original file, and i need to strip it down to the lower yellow shaded file format. doing it by hand is a major pain.

i assume that this can either be done by linking, or by some macro or function, but i have no clue where to begin.

any ideas?
thanks for your help.

 

[Brian]

Easy, since things appear consistent.

1. Open your data workbook.
2. Open a new workbook.
3. Place the titles in your new workbook
4. Select the first data cell in the new workbook. Type = and then go to data workbook, select the cell that this should equal, and then press return. You just entered a link as an equation.
5. Repeat for the first cell in each column.
6. You don't need to touch the the equations for the columns that have data changing in each row.
7. For the columns that have constant data, select the cell and change the cell reference from, say, "A1" to $A$1" (You can do this by pressing F4 while editing the equation.
8. Fill down the formulas: Select the entire row of equations by clicking on the row number at the far left. Hover the cursor over the black box at the lower left of the selected region until the cursor becomes a black cross. Click and hold as you drag down the formulas as far as you need.
9. You should now see the data in the method that you want!

10. When you want to get data for a new source file, then if it has the same name, simply open the new file and press CTRL-ALT-F9 to ensure that all of the equations calculate, thereby importing the data.

11. To accomodate cahnging filenames, it would be easiest to first cut and paste the entire source sheet into the new workbook. Then link to that sheet. To "import" the new data simply paste it over top of the old data, and save the file under a new name.

Good Luck

 

[getyourbiglobster]

thanks for your reply. just one more question though.

the original 'source' document is updated frequenty by simpling adding another row at the bottom (see row 21 & 22). will excel know to keep a link to the added rows?

 

[Brian]

No. Because you are manually dragging down the equations.

If you drag dwn the equns to far you will probably get "0" for the blank cell. You can hide the zeros with tools>options>view>show zeros.

or you can edit your equations to be
= If(link<>"", link,"") to only show the data when there is some.

If you get error messages when there is no source data, use

=If(iserror(link),"",link)

I often use "-" instead of "" so that I can tell how far down the equations are copied.

Then include a "checksum" to copare the original and source data.

If you have numerical data, then do something like:

a1 = sum(sourcedata!A:A)
b2 = sum(linkeddata!A:A)
c3 = if (A1 = A2, "OK", "ERROR!")
or c3 = if (A1 = A2, "", "ERROR! - NEED TO ADD ROWS TO SHEETS ...")
In cell c3, insert a comment that explains what the problem is and how to solve it.

If sums don't work for you, then you can use counta or countif. Use excel help to find something appropriate.

However, ensure that the "ERROR" cell is placed somewhere that you will see it before printing or publishing your worksheet.