Dynamically refer to a worksheet name in a formula

[Scooby62]

Hi there.

I'm currently writing a macro that inserts a column and give it a title 7 days from the title of the last column. I then want to copy the forumlas from the original column and just add 7 days to the sheet reference in the formula. So one formula might be: ='June 9'!I7/'June 9'!H7 and now I want it to read: ='June 16'!I7/'June 16'!H7. Does this make sense?

This is what I'd like to do.
Thanks in advance to anyone who can crack this.

 

[Fenrirshowl]

The following uses a macro to enter the worksheet functions that can be used to accomplish the goal (apart from the insert column).

First, insert an new Worksheet names. list 1 to 12 in A1 to A12 and Jan, Feb,...Dec in B1 to B12
name this range (A1 to B12) "Cal", say
Note: the months need to be in the same format as your sheet names

I assume cell I2 is the previous date referred & the new column is to be inserted at column J/column 10. Therefore J2 will be 7 days later than I2.

Sub Macro1()

Cells(1, 10).Select
Selection.EntireColumn.Insert

Cells(2, 10) = "=RC[-1]+ 7" ' calculates date based on column before (on row 2)

Cells(3, 10) = "=day(R[-1]C)" ' calculates the day part of the cell above

Cells(4, 10) = "=vlookup(month(R[-2]C),Cal,2)" & " " ' calculates the month of the cell 2 above and returns the value from the CAL range

Cells(5, 10) = "=R[-1]C & "" "" & R[-2]C" ' generates a sheet name string from the parts above

Cells(6, 10) = "=INDIRECT(ADDRESS(7,9,,,R[-1]C))/INDIRECT(ADDRESS(7,8,,,R[-1]C))" ' address function creates a text reference to a cell, indirect can then return the value of cell referred to

End Sub

The job may have been easier if you set up tables for the information held in I7 and H7 on two separate sheets, but for all dates, rather than a new sheet per date. You could then have used Vlookups or Index and Match functions referring to those two sheets rather than needing to bother with the address and indirect functions for a variable sheet name.

 

[GlennUK]

Hi Scooby62,

what is your title exactly? You might be able to use it to generate the formula.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Scooby62]

Glenn you make good point. My title is actually a date, such as 16-June which corresponds to the sheet it refers to 'June 16'. Does this help?

Thanks also to Fenrirshowl for all your work here. I will definitely try this.

The main thing I'm doing here is adding a new column to a table with a date, 7 days greater than the adjacent column. Then I'm copying all the formulas over and just want to update each formula to refer to the sheet below that is 7 days greater than the previous sheet and column.

 

[GlennUK]

Oh yes, that helps a lot.

Use a formula like this:

=INDIRECT("'"&TEXT(A$1,"mmmm dd")&"'!I7")/INDIRECT("'"&TEXT(A$1,"mmmm dd")&"'!H7")

or even

=INDIRECT("'"&TEXT(A$1,"mmmm dd")&"'!I"&ROW())/INDIRECT("'"&TEXT(A$1,"mmmm dd")&"'!H"&ROW())

which creates a formula that adjusts as you copy downwards ( points to the same row in June 16 as the formula is entered into ).

Well, I hope that gives you some ideas.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Scooby62]

Sorry guys I'm a bit of a neophyte here so bear with me. My nex column heading would go in X54. My previous column heading is in W54. Also, I'll have to put my "CAL" range in another worksheet for organization and neatness purposes. I'm calling the sheet "formulas".

So having told you all of this can you re-adjust the macro above to reflect this? I know it's a lot to ask but hoping you can help.

Thanks again.