Excel Function - Which one?

S

sassykeon

Hi,

I was hoping to receive help on doing the following:

I have a spreadsheet with the first column that has titles of
books.
I want to create a second column that pulls the title of
the book in row 2 of column 1 and print that title into
the first 9 rows of column 2. Then, I want to pull the title of
the row 10 of column 1 and print that title into the second 9
rows (rows 10 to 18) of column 2, and so on.

It would look like this originally:

Column 1 | Column 2
Title 1
Title 2
Title 3
Title 4
Title 5
Title 6
Title 7
Title 8
Title 9
Title 10
Title 11
Title 12
Title 13
Title 14
Title 15
Title 16
Title 17
Title 18
Title 19
Title 20


But i want this to happen:

Column 1 | Column 2
Title 1 | Title 1
Title 2 | Title 1
Title 3 | Title 1
Title 4 | Title 1
Title 5 | Title 1
Title 6 | Title 1
Title 7 | Title 1
Title 8 | Title 1
Title 9 | Title 1
Title 10 | Title 10
Title 11 | Title 10
Title 12 | Title 10
Title 13 | Title 10
Title 14 | Title 10
Title 15 | Title 10
Title 16 | Title 10
Title 17 | Title 10
Title 18 | Title 10
Title 19 | Title 19
Title 20 | Title 19
and so on...


Can you please show me the exact formula to insert into
the first row of column 2 to do this
 
F

Frank Kabel

Interesting question

may i ask for the purpose of this?. Assuming that your title list
starts in row 2 enter the following formula in B2
=INDIRECT("A" & INT((ROW()-2)/9)+2)
copy this formula for the other rows

HTH
Frank
 
B

Bob Phillips

The OPs question seems a little confused as to whether the data starts in
row 2 or row 1. If it is row 2, Frank's formula works. If it is row, it
should be amended to

=INDIRECT("A" &INT((ROW(A1)-1)/9)*9+1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

sassykeon

Frank and Bob,

Thanks for posting your replies. The data starts on
the 2nd row, but Bob, your formula didn't work out perfectly.
After taking the title form row 2 of column 1 and printing
it in the first 9 rows of column 2, it took the title from
row 3 of column 1 and printed it in the next 9 rows of
column 2, when it should have taken the title from
row 10 of column 1, and printed that. I appreciate your
help though.

Frank, your formula works perfectly, but I just had to adjust
the row 2/row 1 confusion.

Btw, I'm doing this to organize a datafeed file that contains a list o
book products that I'm going to use to make a website
 
Top