MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Extraction and printing of values from every 12th cell of row

Posted by John Davidson on January 17, 2001 2:59 PM

Here is my problem.
I am creating a financial analysis spreadsheet that will extract specific data from an amortization schedule into a table that I have created. This amortization data is contained in an amortization schedule that is on the same spreadsheet page as the table. The problem starts with a logical test to determine if it is correct to extract the first number from a column in the amortization schedule. If this logical test determines a "yes" result, then this first number will be extracted and printed in that "test" cell. Here now lies my problem. After printing the first number, I now need to extract a series of numbers from the same column in the amortization schedule and print them in a row of cells in the spreadsheet table. The numbers that need to be extracted from the amortization schedule column are located every 12th cell in that column.

Any ideas. I am stuck!!

Posted by Aladin Akyurek on January 18, 2001 12:48 AM

I'm not sure I interpret what you want correctly.

I'll assume that

(a) the numbers you are interested in is in column A with the first number in A1.
(b) your test cell is B1.
(c) you want to extract/copy/read off the contents every 12th cell from A into column C, starting in C1, iff there is a number in B1.

Enter the following formula in C1 and copy down as far as needed.


If the number in the test cell is always greater than zero when the test positive, you may replace ISNUMBER($B$1) by just $B$1. In order to understand the second arg of OFFSET, you have to know the syntax of the OFFSET-function (you can find the necessary info via Contents and Index under Help of Excel).

You say "print them in a row of cells in the spreadsheet table". I interpreted this as "in the cells of a column". If you want these numbers in the cells of a given row instead, you need to adjust the formula above. Supposing that these numbers must occupy the cells
D1,E1,F1, and so on, then enter in D1


and copy it to the cells next to D1.

Hope all this is what you wanted.