Amortization and financial statements

Welcome to the Forum zumbito!

Originally Posted by zumbito

1 The years summary come every twelve cells, so the table for year summary is huge, is there any possible to do another table, referencing only every 12 cells?

Yes, but I can't give you an actual formula without seeing where your data is. The idea is this: Suppose your data starts in A2 (row 1 contains a header). Then to show a summary for row 2, 14, 26, etc., starting in row 2 of another column you would use

=INDEX(A:A,(ROW()-2)*12+2,1)

and copy down.

2 Amortization is in horizontal as I have 240+ entries, but would like my financial statements in horizontal for ease of read.

I am lost. If it's horizontal and you want it horizontal, what do you need to change?[/quote]

The only way I can think about it (besides manually) is transposing it, but that way I will lose the references, any clue about how to go about this?

What references would you lose?

I think it would really help if you attach your file, if it does not contain any private data. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

Originally Posted by 6StringJazzer

Welcome to the Forum zumbito!

Yes, but I can't give you an actual formula without seeing where your data is. The idea is this: Suppose your data starts in A2 (row 1 contains a header). Then to show a summary for row 2, 14, 26, etc., starting in row 2 of another column you would use

=INDEX(A:A,(ROW()-2)*12+2,1)

and copy down.

I am lost. If it's horizontal and you want it horizontal, what do you need to change?

What references would you lose?

I think it would really help if you attach your file, if it does not contain any private data. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

I meant vertical to horizontal, sorry.
Done.
I want to put the monthly amortization in a anually table (red square in the first tab), and then transpose it in the next tab (red square in second tab), but keep the references so that it is updated automatically.

I don't understand your target format. You have two or three rows for each value. Your titles in column B are merged, which we discourage. I tried to give you a solution that looks the same. Also the data on CS starts in 2017 and the data on FS starts in 2019, there is no source data for 2031, so be aware of that.

This solution uses an INDEX formula. The current column is used to determine what row to get the data from, and the current row is used to determine what column to get the data from.

Post repeated sorry.