Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Varying a Formula in an Excel Spreadsheet
I have been trying to insert formula into the cells of an Excel spreadsheet
that sums the contents of cells from a number of other Excel spreadsheets, within the same workbook. I would like to extend this to apply to other speadsheets were the number of spreadsheets within the formula varies. i.e. Spreadsheet 'sheet1' might source its information from 2 spreadsheets, whilst spreadsheet 'sheet2' might want to source its infromation from 3 spreadsheets. I also set up a numebr of for_next loops to control the row and column of the cells which require to be populated and information sourced from. I tried to define a string variable 'eqn' which I hoped could be placed in the formula in one line of code such that this could be applied to either of the spreadsheets. The string variable could then be altered to reflect the number of source spreadsheets required. I tried the following: For Each sheet In Array ("sheet1","sheet2") For Each column In Array ("A","B","C") For Each row In Array ("1","2","3") If sheet = "sheet1" then eqn = "'sheet10'!" & column & row & ",'sheet11'!" & column & row &" If sheet = "sheet2" then eqn = "'sheet10'!" & column & row & ",'sheet11'!" & column & row & "'sheet12'!" & column & row" sheet.Cells("row","column").Formula = "=SUM(" & eqn &")" Next Next Next The intention was to insert a formula in the relevant cells of 'sheet1', in the example, which looked something like this: "=SUM('sheet10'!" & column & row & ",'sheet11'!" & column & row & ")" Excel dosen't seem to recognise the use of string variables used in this way. Could anyone suggest an alternatiive or suggest where I might be going wrong. -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Varying a Formula in an Excel Spreadsheet
You just had some typos and syntax errors. this worked fine for me:
Sub ABC() For Each sheet In Array("sheet1", "sheet2") For Each Column In Array("A", "B", "C") For Each Row In Array("1", "2", "3") If sheet = "sheet1" Then eqn = _ "'sheet10'!" & Column & Row & ",'sheet11'!" & _ Column & Row & "" If sheet = "sheet2" Then eqn = _ "'sheet10'!" & Column & Row & ",'sheet11'!" & _ Column & Row & ",'sheet12'!" & Column & Row & "" Worksheets(sheet).Cells(Row, Column) _ .Formula = "=SUM(" & eqn & ")" Next Next Next End Sub -- Regards, Tom Ogilvy "Ardvaark the Third via OfficeKB.com" wrote in message ... I have been trying to insert formula into the cells of an Excel spreadsheet that sums the contents of cells from a number of other Excel spreadsheets, within the same workbook. I would like to extend this to apply to other speadsheets were the number of spreadsheets within the formula varies. i.e. Spreadsheet 'sheet1' might source its information from 2 spreadsheets, whilst spreadsheet 'sheet2' might want to source its infromation from 3 spreadsheets. I also set up a numebr of for_next loops to control the row and column of the cells which require to be populated and information sourced from. I tried to define a string variable 'eqn' which I hoped could be placed in the formula in one line of code such that this could be applied to either of the spreadsheets. The string variable could then be altered to reflect the number of source spreadsheets required. I tried the following: For Each sheet In Array ("sheet1","sheet2") For Each column In Array ("A","B","C") For Each row In Array ("1","2","3") If sheet = "sheet1" then eqn = "'sheet10'!" & column & row & ",'sheet11'!" & column & row &" If sheet = "sheet2" then eqn = "'sheet10'!" & column & row & ",'sheet11'!" & column & row & "'sheet12'!" & column & row" sheet.Cells("row","column").Formula = "=SUM(" & eqn &")" Next Next Next The intention was to insert a formula in the relevant cells of 'sheet1', in the example, which looked something like this: "=SUM('sheet10'!" & column & row & ",'sheet11'!" & column & row & ")" Excel dosen't seem to recognise the use of string variables used in this way. Could anyone suggest an alternatiive or suggest where I might be going wrong. -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for selecting a varying cell reference | Excel Discussion (Misc queries) | |||
can th same spreadsheet have varying cell sizes top & bottom | Excel Discussion (Misc queries) | |||
Pivotting - Formula w/ Varying Column | Excel Worksheet Functions | |||
copy data from varying slected rows into new spreadsheet | Excel Discussion (Misc queries) | |||
Excel formula for varying payment interest bear installment loan. | Excel Discussion (Misc queries) |