Discussion Locked

Sharing info frm 1 Excel to 3 new excel

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I prepare 3 monthly Excel reports that is cummulative from 1998 with data from one Excel file for the specific month. I have been cutting a page but the process takes at least 8 hours to complete -- lots of data. How do I simplyfy this and programmy monthly reports to automaticaly update the information from the 1 file. Each new report takes only a certain potion of the data from the 1 file. Help - I am in cut and paste hell. Thanks for any help you can give.

Elaine

7 replies

by dklippert

You can create an accumulation WorkSheet/Book with what are called "3D" Formulas. Hopefully the entries are laid out pretty much the same on each sheet.
Create a Total for each piece of pertinent data. (?TotalSales98?, ?TotalCostsMay99?, etc.)
Name that cell or range. (An easy way is to select the cell and then enter the name in the Address box ? where you see B34 ? and hit enter.
If you have all of the sheets in one book, you can then set up an accumulator page with formulas like = (TotalSales98+ TotalSales99)-(TotalCosts98+TotalCosts99).
If they are in separate workbooks, it?s a little more difficult, but not that bad. Start the formula in the accumulator (type =) then open each book and select the cell you want. Type a arithmetic operator (+ - /) and the click the next cell.
If the cells are laid out the same way on each sheet, you can drag the formulas and they will automatically adjust them selves. Let me know if you have any questions, it?s a little difficult to tell you exactly what to do without more information.

by don christner

The vblookup function can help a lot. Basically, it looks for a match in the first column and then returns the value from a different column on the same row. You can use this function across pages by using named ranges. Very handy function.

Don

by don christner

Yesterday (12/20), there was an article on this site about using vblookup to redesign a customers system of handling unemployment claims.

This discussion is closed to new replies.