Autofill with column change not Row.

[Timbobro]

I have been trying for a couple of days now to Autofill a column from cells in a single row but I cant get the column letter to sequence in any direction only the row numbers.

I am also trying to set up links to those cells in at the same time but I can only do it by individually setting up the link in each cell in the column.
i.e. Link cells K5:K60 to cells in Row 3. So the sequence I have manually put in to sheet 101 in Cell K5 is 101,S3,CLICK HERE
K6 is 101,W3,CLICK HERE
K7 is 101,AA3,CLICK HERE
K8 is 101,AE3,CLICK HERE
And so on.......
I was hoping to find a solution to this as I have 109 sheets all with about 50 cells I want to link in the same way, so sheet 102 will be-
K5 is 102.S3,CLICK HERE
K6 is 102,W3,CLICK HERE
K7 is 102,AA3,CLICK HERE
AND SO ON...

If I have to manually do each cell in every page I will be retired before finishing. Please see screenshot.

Any help will be hugely appreciated.

 

[Micron]

You've been trying how, vba? That is the only solution I can foresee. If you have code that you attempted you should post it. Please post within code tags (vba button on posting toolbar) to maintain indentation and readability.

As I see it, you'd outer loop over the sheets and get the sheet name (e.g. 101). Inner loop is from K5 to K? and make the cell value what you want. What you want it to be isn't entirely clear. Literally like "102.S3,CLICK HERE"? The S3, W3 part is also unclear. I suspect you'd need to use an array of cell addresses for that and loop over the array in the inner loop.

 

[Timbobro]

You've been trying how, vba? That is the only solution I can foresee. If you have code that you attempted you should post it. Please post within code tags (vba button on posting toolbar) to maintain indentation and readability.

As I see it, you'd outer loop over the sheets and get the sheet name (e.g. 101). Inner loop is from K5 to K? and make the cell value what you want. What you want it to be isn't entirely clear. Literally like "102.S3,CLICK HERE"? The S3, W3 part is also unclear. I suspect you'd need to use an array of cell addresses for that and loop over the array in the inner loop.

Hi Micron,

I'm afraid I'm not that knowledgable about excel, I dont know how to write code. I meant K5 to K? are the cells that have the links to cells S3 and W3 and AA3 and so on, so K5 jumps to S3, K6 jumps to W3, K7 jumps to AA3 and so on. The CLICK HERE is the text that shows in K5 to K?, ie reminding users to "click here" to jump to the section of the sheet where "comments" are written and stored.

As I said I can create the links as above but I have 109 bedrooms and a sheet for each, the sheet numbers are not entirely sequential (some missing like rooms 113, 115, 117 to name a few, but my point is if I have to individually input the links on column K5 to K? on every page it will take me a month of Sundays so I was hoping there was a easier solution.

At the moment I have completed the 1st sheet 101 then copied all the K5 to K? cells, I then select all the other sheets and "paste special" on K5 and this then populates all relevant K5 to K? on all sheets - only problem being that all K5 to K? on every sheet then links back to the relevant S3, W3, AA3, AI3 cells on sheet 101, so I now have to manually go through every single K cell on every sheet to change the link sheet ie 101,S3 change to 102,S3 etc.

Any dizzy tricks would be greatly appreciated.