Excel Formula - Incrementally Add a +1 to a Header Name each time it is copied/pasted

[Johnny Thunder]

Hello Group,

I am working on a project and need one last piece to wrap up this file.

Explanation:
I have a block of Data B2:N15, There are various formulas within the block that link to other sheets and some manual entry fields. I have set up these blocks so that a user can copy a block and paste it two rows below the last block. The issue I need help on is that There is a descriptor in Column A next to the first row of the block "Block 1" and as the second block is copied pasted I need a formula that can look up at all the cells above and incrementally add a +1 to the block name so it changes to "Block 2".

Any ideas on how to do this. There is no other data in column A other than the block names.

Thanks in advance.

 

[DSCfromCFA]

G'day JT,

Assuming that the block header is always 17 rows below the previous i.e. row 2, row 19 row 36 etc. then the following will work. If you copy into the correct location it will be fine. If you drag it down you will get "Block" written into all the intermediate cells in column A with the correct number appearing at the 17 row interval - it's down and dirty but its also 5:00 pm of Friday afternoon.

="Block "&IF(MOD(ROW(),17)=2,(ROW()-2)/17+1,"")

Cheers

shane

 

[Johnny Thunder]

Thanks Shane! I will give it a try, the size of the blocks will differ but this is a start.... I can try and adapt a way to look at the last activecell in the block prior to the copy of the block and see if there is a way to grab the range in between to search for the last name used