Hello,
I try to build a table with various formulas in Excel 2007 using VBA. The input data are in 2 different spreadsheets and I will build the table in a 3rd sheet of the same file.
In my sample case, I use about 10 rows even though later on that number will increase significantly. I give an example table below.
Method:
Input 1) RowA and RowB in Sheet1: both have 10 rows of numbers and a column title in row 1. I will choose data from 1 of both rows (by selecting RowA or RowB?).
Input 2) RowA in Sheet2 (next sheet): 3 constants
Output 1) RowA through RowE in Sheet3 (some other sheet): 10 rows of calculation using input 1 and input 2, with each column of output 1 using a different calculation method. I will alternatively choose RowA or RowB from Sheet1 for the calculations by choosing its corresponding title heading.
example of table construction (S=sheet):
RowA(S1) RowB(S1) RowA(S2) RowA(S3) RowB(S3) RowC(S3) RowD(S3)
Title A | Title B | Constants | Title C | Title D | Title E | Title F
12.1 | 4.9 | 4
43.8 | 7.6 | 7 | Average(A2:A3)
8.7 | 5.9 | 5 | Average(A3:A4) | Average(A2:A4) | formula3 | formula4
6.5 | 15.8 | x | Average(A4:A5) | Average(A3:A5) | formula3 | formula4
7.4 | 25.7 | x | Average(A5:A6) | Average(A4:A6) | formula3 | formula4
Thank you for your help on VBA code to create sheet3.
I try to build a table with various formulas in Excel 2007 using VBA. The input data are in 2 different spreadsheets and I will build the table in a 3rd sheet of the same file.
In my sample case, I use about 10 rows even though later on that number will increase significantly. I give an example table below.
Method:
Input 1) RowA and RowB in Sheet1: both have 10 rows of numbers and a column title in row 1. I will choose data from 1 of both rows (by selecting RowA or RowB?).
Input 2) RowA in Sheet2 (next sheet): 3 constants
Output 1) RowA through RowE in Sheet3 (some other sheet): 10 rows of calculation using input 1 and input 2, with each column of output 1 using a different calculation method. I will alternatively choose RowA or RowB from Sheet1 for the calculations by choosing its corresponding title heading.
example of table construction (S=sheet):
RowA(S1) RowB(S1) RowA(S2) RowA(S3) RowB(S3) RowC(S3) RowD(S3)
Title A | Title B | Constants | Title C | Title D | Title E | Title F
12.1 | 4.9 | 4
43.8 | 7.6 | 7 | Average(A2:A3)
8.7 | 5.9 | 5 | Average(A3:A4) | Average(A2:A4) | formula3 | formula4
6.5 | 15.8 | x | Average(A4:A5) | Average(A3:A5) | formula3 | formula4
7.4 | 25.7 | x | Average(A5:A6) | Average(A4:A6) | formula3 | formula4
Thank you for your help on VBA code to create sheet3.