How to efficiently add financial statement data

[ammartino44]

Spreadsheet attached.


I have a financial statement with 100's of rows that I need to update each month from different workbooks (sometimes I need to update multiple months if I slacked haha). My strategy is to open each of the files (file names below in bold) and use the following formula to get the numbers into my main spreadsheet (title "main" tab below.

To the right, in the first column I have coded values and sum formula values. In column B, I have the vlookup formula that copies the data from the other workbooks.

Question 1: I have TONS of rows in my file with lots of empty rows. Ideally, I want to just copy my vlookup formula down to the last row with data (on a seperate note is there a way that I can do that without having to drag the formula all the way down? Control end doesn't work because there are empty rows). The only problem with doing this is that 1) Whenever there are empty rows it will copy a zero (I don't want it to copy ""). This is ok if there is a line item in that row, but when there is no line item I don't want to have to erase all the zeros. For example, in row 7 to the left, and 2) If I copy all of the formulas down it will copy over the sum formulas which I DO not want to do.

Question 2: If I am working on multiple months, how can I copy the formula in column C and have it update to automatically pull from the other open files (all have same formatting)? For example, if I copy formula in column C to D, how can I have it automatically pull from Management Book Mar. Maybe I have to insert something that references a month in row2?

[TMS]

For the first question ...

Please Login or Register  to view this content.

Regards, TMS

[TMS]

For question 2, if you use consistent headers and tab names, you could probably use INDIRECT to reference the monthly sheets. But I suspect that if you use INDIRECT over hundreds of rows, it might prove to be very, very slow.

I think you are confusing (raw) data with presentation and presentation with calculation and vice versa.

Ideally, you would have raw data in a tabular format with each record date stamped. Then you could use a Pivot Table to analyse the data.

[ammartino44]

TMS. Thanks for the first explanation. Makes perfect sense. However, I didn't understand your second comment regarding presentation. I have a master sheet that I use. Each month the monthly results come out and I import them into my master sheet that has different formatting. Sometimes I do this for more than 1 month at a time (for example, Jan/Feb/Mar) and I would like to be able to have those files open and just copy the formula to the right without having to change the name for each month. Does that make sense?

Regarding pivot tables, do you know of any resources for using pivot table with financial statement info. I've used pivottables for other data, but never for financial analysis. Even just some spreadsheets of how other people do it would be insightful. Thanks.