Complex autofill patterns

I have posted this previously, under a thread however I believe it was marked as solved and am having no response.

Please see attached sheet for a better understanding, however I am stumped on how to get excel to autofill the cells required,
At present the only way I can see is by manually doing it and it taking a month of Sundays.

Firstly:

On each work sheet is a tab containing numerical information listed monthly row by row
On a summary worksheet I need it to collect the information and display it monthly.
So if the information for January is on row 21
The info for Feb is on row 22
However when I click and drag across the cell row stays at 21.


2ndly:
I then need the information to be shown in a small table, however the way the cells are laid out in no distinct pattern so autofill does not work.
So some how I wish to tell the cells where to look and it automatically sort it self. If possible

Lastly:
On the contents I wish to hyperlink a shortcut to each tab, is there a way to do this without manually inserting a hyperlink.

For more clarification best have a look through the sheet, I have completed some parts manually so hopefully it will give an idea of what it is trying to achieve.

Many thanks in advance!

For your first problem I'd suggest trying a vlookup formula, for example:
=INDIRECT("'"&VLOOKUP(K15,$A23:$I47,9)&"'!T10") in K21 and copy and paste to Q21.

I don't quite understand the second one.

For the last point, you can use the hyperlink function =HYPERLINK() to create one to a file but don't think it works for a cell. You would have to use VBA for this I think.

Thanks for that, but I am a little confused.

Does that take into consideration the INDIRECT formula already in the cell to find the information on the seperate work sheets.
When I tried it didnt seem to work.

If you see on SUMMARY page starting at cell A22 it shows all the month info collected from Row 18 on summary page (K,L,M,N / Q,R,S,T etc)
I wish the information to auto fill for each month and to find the corresponding information on row 18.

I dont know anything about VBA, so that is a shame as I would have liked to have used the links to go to each worksheet.
That will be a manual job then I think!

Thanks again.

Ok, so in your excel workbook, press Alt+F11, this will take you to the visual basic editor.

Then, from the menu bar select insert->module

In the window that appears (should be titled module1) copy and paste this code:

Sub macro_1()
Dim count
For count = 21 To Sheets("SUMMARY").Range("I" & Rows.count).End(xlUp).Row
    Sheets("SUMMARY").Hyperlinks.Add Anchor:=Sheets("SUMMARY").Range("I" & count), _
        Address:="", SubAddress:="'" & Sheets("Summary").Range("I" & count) & "'!A1"
Next
End Sub

click the cursor on a line between sub macro_1 and end sub, then press F5.

this should generate the hyperlinks for you.

Excellent, it worked and i was able to copy the information and paste it into various other places it needed.

Much appreicated, I cant believe how quickly you got it sorted!!