using vba to enter an excel formula by sheet position not sheet name

Hi,

I hope there's a very simple solution to this and its just my lack of vba knowledge thats giving me a problem finding it.

I need to enter some formula into a sheet and these formula refer to data on the sheet two along. Unfortunately i don't know the sheets name as this is variable and defined during the course of the macro... only its position... 2 sheets along from the currently active sheet on which the formula is being entered.

I would also be able to work out its absolute position in regards to the first sheet of the workbook if thats useful.

I've put below an example of a piece of formula i want to enter using the macro...

Please Login or Register  to view this content.

So its basically just getting the max value from a column in sheet 'results_0.26pass', however i won't know that this is its name, only that it is 2 sheets along from the sheet in which i am asking vba to write the formula

(alternatively I will know that it will be the name of the sheet in which the formula is being written ... "results_0.26pass" .... with the words "results_" removed... i.e. "0.26pass" in this case).

I'd be greatful for any help!

Ben

This will return the name of the sheet 2 along:

Please Login or Register  to view this content.

and this the name of the sheet without 'results_':

Please Login or Register  to view this content.

Dom

Please Login or Register  to view this content.

??
my problem is in getting the sheet name into the "MAX=" formula i'm asking vba to type into a cell

Like this:

Please Login or Register  to view this content.

Dom

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.