Divide Use column headings - Indirect?

[Darin Kramer]

*** similar to my previous query, but different!****

Howdie!

so situation is

Several Tabs (similar contents but different names) and a consolidated
Tab.
Within the Consolidated tab Cols A to Z each have a unique header (these
are the names of other tabs in the same book)
say Col A = Cat, Col B = Dog etc (ie we thus have a sheets entitled Cat,
dog, etc...)

In the consolidated tab I want to put a formulae In column A that
performs the following calC: From (Tab entitled Cat) , add cell a1 and
cell c3 and then divide by b3.
Now I could just say
=(Cat!a1+:Cat!a1c3)/Cat!b3, but then I would manually have to change cat
to dog
for each of my columns.
I Any ideas???


Regards
D

*** Sent via Developersdex http://www.developersdex.com ***

 

[stefan onken]

hi Darin,
Excel has a function called INDIRECT().
in A1 of your consolidated sheet is cat, then in A2
=(INDIRECT(A1&"!A1")+INDIRECT(A1+"!C3"))/INDIRECT(A1&"!B3")

copying this formula to B2 changes A1 (cat) to B1 (dog) automatically

stefan

 

[Mike H]

Hi,

This requires a couple of steps. First you need to get you worksheet names
in a row and to do that use a function, put this in a general module

Function wsnames(x As Integer) As String
If x > Worksheets.Count Then Exit Function
wsnames = Sheets(x).Name
End Function

Then in column A in a row out of the way enter
=wsnames(COLUMN(B40))
I used row 40 and note that it references B40 in Column A which is avoid
getting the name of the sheet you are working on. Drag right and you should
get the names of your worksheets.

Use this formula to do the calculation
=(INDIRECT(A40&"!A1")+INDIRECT(A40&"!C3"))/INDIRECT(A40&"!B3")
drag right to reference the different sheets.

Mike