About MS Excel Formula !! Help !!?

Question

I thank you all for giving me answer for last question on MS Excel formula. It worked well and helped me lot.
Now, I want to know the formula in MS excel for putting fill range (Sr.No. in sequence) in Cell A1, A2, A3, A4, and so on, where, if the Cell B1, B2, B3, B4 and so on has the data in it, if there is no data in a particular cell B, then the corrosponding cell A should be blank and the next cell in A should have continuing last fill range prior to the blank range.
Please help.

Answer 1

In cell A1 enter:

=IF( B1="", "" , C1 )

Copy that formula down as far as you need. This can be done by grabbing the fill handle (the tiny black box in the lower right corner of the cell when it is selected) and dragging it down.

In cell C1 enter your first sequential serial number.

In cell C2 enter:

=C1 + IF( B1="",0 ,1 )

Copy that formula down as far as you need.

Now, whenever you enter something in column B you will get the next serial number in order in column A.

If you need to you can hide column C by right clicking on the C at the top of that column and choosing Hide.

Hope that helps...

Answer 2

A pivot table would be the simplest, but you want a formula so OK. First the pivot table. Highlight all of the cells that contain data, including the header information. Go to Data>Pivot Table. When the dialog box opens, Click Finish. The Pivot Table Field list will open. Drag the data to the far right column and to the data field. This will be automatically set to count. It will give you a count and a summary of each down the left column. Right click on the top left gray button and choose Field Settings. A dialog will open allowing you to adjust for your needs. Anytime you add new data, you will need to refresh the pivot table by right clicking anywhere on the table and select Refresh. AutoFilter: Select the cell at the top of your list and click Data>Filter>Auto Filter. A drop down box will appear. Click on the arrow and select the filter criteria "New Dehli", no quotes. Look at the bottom left corner of your screen. It will say "x of xx records found". This will give you a quick count. Formula: Select and empty cell and type =countif(a1:a1000,"New Dehli") This will count all of the records that have the exact text "New Dehli", no quotes, in them. You will need to select several cells to place this formula in. Replace "New Dehli" with "Agra", etc. as needed. This can be done on your summary tab. It does not have to be on the same work sheet.

Answer 3

type A1, A2, in two cells and drag the cell upto reach u r requirement