Hello everyone. Ive got a quick question for you regarding locking ranges in a formula for multiple cells.
I have 5 reference matrices of data located on their own worksheets in A1:Z26 (26x26), lets call these ref1 to ref5. I also have a table of values located in A1:E100 (5x100), lets call this table1. What I am trying to create is a set of 100 matrices (26x26) that basically do a sum product with Row 1 of the 5x100 and each cell in the 5 reference matrices. For example, the formula in Row 1 Col 1 of the first of 100 matrices is something like this:
I can lock the reference to the table cells so that they do not change columns or rows and drag this formula to all 26x26 cells in my first matrix. But to create the next 99 matrices I have to go back and edit the formulas to reference the correct reference matrices and table reference.
To solve this I have taken the values from row 1 in the table and just duplicated them 26 times next to the desired matrix and not locked the row reference. That way when I copy the values from the first matrix to the second it has the correct table reference and I only need to go back and change the ref1 to ref 5 values.
Is there an easy way to lock the ref1 to ref 5 references in the 1st matrix so that when i copy and paste for the remaining 99 matrices I don't have to go back and change them?
I have 5 reference matrices of data located on their own worksheets in A1:Z26 (26x26), lets call these ref1 to ref5. I also have a table of values located in A1:E100 (5x100), lets call this table1. What I am trying to create is a set of 100 matrices (26x26) that basically do a sum product with Row 1 of the 5x100 and each cell in the 5 reference matrices. For example, the formula in Row 1 Col 1 of the first of 100 matrices is something like this:
Code:
=[col1,row1,table] x {col1,row1,ref1] + [col2,row1,table]x[col1,row1,ref2] + ...I can lock the reference to the table cells so that they do not change columns or rows and drag this formula to all 26x26 cells in my first matrix. But to create the next 99 matrices I have to go back and edit the formulas to reference the correct reference matrices and table reference.
To solve this I have taken the values from row 1 in the table and just duplicated them 26 times next to the desired matrix and not locked the row reference. That way when I copy the values from the first matrix to the second it has the correct table reference and I only need to go back and change the ref1 to ref 5 values.
Is there an easy way to lock the ref1 to ref 5 references in the 1st matrix so that when i copy and paste for the remaining 99 matrices I don't have to go back and change them?
