Referencing cell with formula in another sheet doesn't work?

[Berko]

I hope this isn't considered double posting. My other post began to stray from the original topic, so I thought I should post a new topic.

I have a formula in 'Sheet 2'!AC4 (it's actually a variable column, but that I can work out later) that I need to reference in a totals sheet. If I type the cell reference in Excel, the value is reported splendidly. But, I need to enter the cell reference in VBA. Here is the code that I am using in VBA that should accomplish this, but is resulting in simply a blank cell - no data entered at all.

This is the formula entered in the cell I want to reference - 'Sheet 2'!AC4

=SUM(INDIRECT("B4:"&ADDRESS(ROW(),COLUMN()-1)))

And here is the code that I have to reference that cell in a newly created column on Totals. I change ActiveCell to C3 and then I have this code.

ActiveCell.Value = "='" & NewSem & "'!AC4"

This code works for a string that is in Sheet 2 (a.k.a. NewSem)!A2.

ActiveCell.Offset(0, LastSheet - 1).Value = "='" & NewSem & "'!A2"

The only difference between these two references is that A2 has a string (Fall 2005, for instance) while AC4 has a formula with INDIRECT. This is really strange too, because the two similar code snippets above work completely differently, but if I type

='Sheet 2'!AC4

in the cell where I want the reference to appear, it works just fine. I just don't know why VBA can't handle this. :-/

 

[venkat1926]

why not try
worksheets("sheet2").range("ac4").select

or is it too simplistic a solution because I have not understood.

 

[Berko]

That would work except that I have to ascertain the column based on the number of weeks in the semester. The macro figures out the number of weeks in the semester and inserts that many columns between the row headings and the total column. I know based on working with the same semester for a while that AC4 is where I want the formula, but I'm still having the problems described above.

 

[venkat1926]

difficult to say in the absence of data base
however plese note column number of any cell can be obtained by the statementm in vba
for e.g.
range("a1").column
(column singular) will give you 1
iF A1 is activecell
activecell.column
will give you 1


range("a1:a5").EntireRow.Insert
will insert 5 rows in the beginning

use this staement to create a sub for you.

 

[Tazguy37]

I guess I'm missing something, but couldn't you just store the value from that formula in a variable and refer to it later?

If the formula is the problem, could you not use an R1C1 formula instead?

Maybe I need to see a sample of the sheet, what is happening, and what you want to happen.

 

[Berko]

If I store the value in a variable, will it update as data is added in the semester sheet? Data is added every week when hours are reported and credited. So the formula will update through the end of the semester. The cell on the "Totals" sheet needs to update too.

Taz, I would be interested in how an R1C1 formula would make a difference for my purpose. I hadn't thought of that, but if it works, I would be more than willing to use it.

As far as the column number goes, I have the column number because I know how many columns there are. There is a headings column, a totals column, and the same number of columns between those two as the number of weeks in the semester.

P.S. I have tried to use the HTML maker add-in, but it doesn't work on my Mac for some reason. I could export it as html and put it up on my site if that would help.

 

[Berko]

OK, in looking at my workbook some more, I noticed that Excel seems to think I have some circular references, which I assume have to do with the INDIRECT formula. If anyone wants to see the spreadsheet, I could send the file over for you to take a look at. The code is really ugly. I could also include a more detailed explanation of what is happening and what needs to be happening. Sorry I can't use the HTML maker.

 

[Berko]

Huzzah! I am finally able to use the HTML Maker. Here is the Totals sheet from my workbook. Cell C3 needs to reference row 4 of the last column on sheet 2. I will post some more sheet data so you guys can get a better picture of what I'm trying to accomplish.

INPROGRESS.counselortemplat.xls
	A	B	C	D	E	F	G	H	I
1	JamiePhelps	Summer2005	Fall2005	Totals	NeededforAAPC MemberAssociate
2	PastoralCounseling:
3	Individual			0
4	Couple			0
5	Family			0
6	Group			0
7	Total			0	375
8	ObservationofCounseling:
9	PeerConsultationorRolePlay			0
10	ObservationofTapes/TrainingVideos			0
11	Other			0
12	Total			0
13	TotalCreditedHours			0
14	TotalHoursNeeded
15	TotalHoursRemaining			0
16	Supervision
17	Individual			0
18	GroupCaseConference			0
19	InterdisciplinaryCaseConference			0
20	TOTAL			0	125
21	PeerGroup/Didactic			0
22	AdministrativeTasks			0
23	TOTAL			0
Totals

 

[Berko]

This is the template for the new semester that is added via the macro that interestingly enough did not show up in the above post. It is a WordArt at the very end of the top row that says "Add Semester". As you can see, this template only has three columns. The macro figures out the number of weeks in the semester (obviously this could vary) and adjusts the sheet so that there are that many columns between the first and last column.

INPROGRESS.counselortemplat.xls
	A	B	C	D
1			TOTAL
2
3	PastoralCounseling:
4	Individual		0
5	Couple		0
6	Family		0
7	Group		0
8	Total		0
9	ObservationofCounseling:
10	PeerConsultationorRolePlay		0
11	ObservationofTapes/TrainingVideos		0
12	Other		0
13	Total		0
14	Supervision
15	Individual		0
16	GroupCaseConference		0
17	InterdisciplinaryCaseConference		0
18	TOTAL		0
19	PeerGroup/Didactic		0
20	AdministrativeTasks		0
21	TOTAL		0
Semester Template

 

[Berko]

And here is an example of how the sheet would look with columns inserted for the weeks. HTML Maker would only let me do one month, but I think this is sufficient for showing how the last column's address could vary.

INPROGRESS.counselortemplat.xls
	A	B	C	D	E	F	G
1		August					TOTAL
2	Fall2005	1	8	15	22	29
3	PastoralCounseling:
4	Individual	8	7	10	11	5	41
5	Couple						0
6	Family						0
7	Group						0
8	Total						41
9	ObservationofCounseling:
10	PeerConsultationorRolePlay						0
11	ObservationofTapes/TrainingVideos						0
12	Other						0
13	Total						0
14	Supervision
15	Individual						0
16	GroupCaseConference						0
17	InterdisciplinaryCaseConference						0
18	TOTAL						0
19	PeerGroup/Didactic						0
20	AdministrativeTasks						0
21	TOTAL						0
Semester 2