Linking worksheet information

[rena]

I have a worksheet (in one workbook) for each day of the week, with the same type of info on each sheet, i.e. in the same location. I need to create different worksheet (either in the same workbook or preferrably a new one)using the same information in a different way. What I have tried to do is link one day and auto-fill the remaining days but, of course, the day remains the same.

For example, I want to copy the value of C5 in each worksheet (daily) to a row in a new worksheet. The copied "formula" would read "=Sep01!C5, =Sep2!C5 etc (=worksheet name, cell name).

Also, I would like to make this into a template so that I can use these workbooks every month.

Is there a way?

[mrice]

You could try something like this...


Create a user defined function on a VBA module sheet

Function Test(SheetIndex As Integer, Address As String)
Test = Sheets(SheetIndex).Range(Address).Value
End Function

Enter Sheet index numbers into your new worksheet

1 in A1
2 in A2
3 in A3

etc

Then add the following formula to B1 and copy down

=test(A1,"$C$5")

[raypayette]

Prepare a template with all your formulas and formatting as click File, Save as...,, Save as type and select Template.

[rena]

Thank you Martin and Ray!

Hmmm, Martin, I've tried to do what you suggested. I have to admit, it was the first time I ever entered anything into the VB thingamagig. This is what I entered into the Test (code) :

Private Sub Worksheet_SelectionChange(ByVal Target As Range) ...was there when I started....then your suggestion:

Function Test(ShetIndex As Integer, Address As String)
Test = Sheets(SheetIndex).Range(Address).Value
End Function

I also entered the info in A1 and B1 respectively. The column with the formula resulted in "#NAME?". I guess it would really be a good idea to become familiar with VB (I haven't got a clue). Then I decided to click on "Activate". No change resulted in the worksheet.

Perhaps I will have to do all of it manually. I have to give it some more thought.

Thank you so much for your input. I truly appreciate it.

[Bryan Hessey]

Rena,

Perhaps the attached will give you some ideas

The Aug and Sep must be open to use the Sum workbook

The VBA (independant of the other three) workbook requires that you 'button' the figure collection

Hope this helps
---

Thank you Martin and Ray!

Hmmm, Martin, I've tried to do what you suggested. I have to admit, it was the first time I ever entered anything into the VB thingamagig. This is what I entered into the Test (code) :

Private Sub Worksheet_SelectionChange(ByVal Target As Range) ...was there when I started....then your suggestion:

Function Test(ShetIndex As Integer, Address As String)
Test = Sheets(SheetIndex).Range(Address).Value
End Function

I also entered the info in A1 and B1 respectively. The column with the formula resulted in "#NAME?". I guess it would really be a good idea to become familiar with VB (I haven't got a clue). Then I decided to click on "Activate". No change resulted in the worksheet.

Perhaps I will have to do all of it manually. I have to give it some more thought.

Thank you so much for your input. I truly appreciate it.

[rena]

Hi Bryan,

I am very intrigued with what you came up with. I looked at your zip file contents and I can see that I really should learn VB. Seems you can do so much with programming. Since I don't (yet) know a thing about it, I will study your suggestion and see how I could apply it in my case.

In the meantime, thank you so much!

Rena