to give numbers to rows as per your wish
 

i have some 2000 plus records in a worksheet. i want to give the adjoining records numbers as 1A, 1B(for rows 1,2) 2A,3B(for rows 3,4) 3A, 3B (for rows 5,6) ----in this order. is it possible?
the first of each is a song title and second is the lyricist/musicdirector/singer
it should look like this

1A i want to sing
1B titanic/frac/jessy
2A heartful
2B george/perse/ki9n

to give numbers to rows as per your wish
 

Hi Sumesh,

Am Wed, 4 Apr 2012 02:46:48 +0000 schrieb sumesh56:

1A i want to sing
1B titanic/frac/jessy
2A heartful
2B george/perse/ki9n

in Row1:
=IF(ISODD(ROW()),ROUND(ROW()/2,0)&"A",ROW()/2&"B")
and copy down


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

to give numbers to rows as per your wish
 

Hi Sumesh,

Am Wed, 4 Apr 2012 09:49:22 +0200 schrieb Claus Busch:

in Row1:
=IF(ISODD(ROW()),ROUND(ROW()/2,0)&"A",ROW()/2&"B")
and copy down

or in row1:
=IF(ISODD(ROW()),ROW()/2+0.5&"A",ROW()/2&"B")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

thanks for the suggestion.your formula is related to exact row numbers. but this has a problem. the row one contains the header.the data starts from row2. so if i type the formula on row2 i get the wrong numbering.i get 1B instead of 1A. if the data starts from row5 (for example) and i want to give numbers from row 6 as 1A and to row 7 as 1B and so on.in brief the formula should be free from exact row numbers. is it possible?

how to copy a formula to other cells-any shortcut
 

Hi Sumesh,

Am Wed, 4 Apr 2012 16:46:13 +0000 schrieb sumesh56:

i have a formula on B4 cell. i want to copy this formula to the 2024th
row. holding the shift key and drag is a tiresome job.how can we do this
with a few stroke? thanks

you can double-click on the drag point. Then the formula will be copied
as far as column A is filled.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

how to copy a formula to other cells-any shortcut
 

Hi Sumesh,

Am Wed, 4 Apr 2012 16:46:13 +0000 schrieb sumesh56:

i have a formula on B4 cell. i want to copy this formula to the 2024th
row. holding the shift key and drag is a tiresome job.how can we do this
with a few stroke? thanks

you also can select the range B4:B2024 and then press CTRL+D


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

thanks for the suggestion. i have checked like this
entered the formula in a1 cell. clicked on it. the went to a20 pressing the shiftkey. then i pressed control d and i got the result. but if i have to select a 3030 what is method to select the range?
how select the range?


ok u have made it. you enter a formula in a1. then press f5(goto) and enter a3030. pressing the shift key press enter. now the range is selected. now press control d as suggested by you the formula in a1 is copied to a3030. done. thanks. is this the method to be followed? am i right?

to give numbers to rows as per your wish
 

Easiest method for me is to enter the formula in A1 then simply type
A1:A3030 in the NameBox and hit ENTER key to select the range.

Then hit Ctrl + d


Gord

On Fri, 6 Apr 2012 00:16:56 +0000, sumesh56
wrote:

ok u have made it. you enter a formula in a1. then press f5(goto) and
enter a3030. pressing the shift key press enter. now the range is
selected. now press control d as suggested by you the formula in a1 is
copied to a3030. done. thanks. is this the method to be followed? am i
right?

the reply to my querry is still awaited. thanks