Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Divide Use column headings - Indirect?
*** 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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Divide Use column headings - Indirect?
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 On 9 Jun., 12:27, Darin Kramer wrote: *** 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 Developersdexhttp://www.developersdex.com*** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Divide Use column headings - Indirect?
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 "Darin Kramer" wrote: *** 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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
divide column(x) by column(y) to give column(x/y) in excel? | New Users to Excel | |||
Worksheet has numeric column headings. Change to alpha headings? | Excel Discussion (Misc queries) | |||
Column headings to numbers and row headings to alphabets? | Excel Discussion (Misc queries) | |||
How do I divide a column into two? | Excel Discussion (Misc queries) | |||
Can I invert a table so row headings are now column headings etc | Excel Worksheet Functions |