jaredstine
New Member
- Joined
- May 23, 2011
- Messages
- 1
Hi all,
Reaching out to see if there's a way to lock a column from a table in a formula. Here's the backgound: I'm using SUMPRODUCT formula, my arrays contain columns within a table, and the formula works perfect. The problem is that when I go to paste/drag the formula to other cells I can't seem to figure out how to lock the colums within the table so they don't change (I've tried F4 and it doesn't seem to work).
Example: =SUMPRODUCT(--(Table1[[#All],[Month]]=Dashboard!$B$1) This is the correct formula that I want to drag to the next colum in my dashboard but I need the month column within Table 1 to stay, instead I end up with this: =SUMPRODUCT(--(Table1[[#All],[Auto / Home / Other]]=Dashboard!$B$1),.
I realize I could remove the table and just lock the columns but it takes a while to calculate or the other case might be to maybe just take the array to the 100,000th row so that I'm not always having to update when I add data. Any help is greatly appreciated!
Thanks -
Jared
Reaching out to see if there's a way to lock a column from a table in a formula. Here's the backgound: I'm using SUMPRODUCT formula, my arrays contain columns within a table, and the formula works perfect. The problem is that when I go to paste/drag the formula to other cells I can't seem to figure out how to lock the colums within the table so they don't change (I've tried F4 and it doesn't seem to work).
Example: =SUMPRODUCT(--(Table1[[#All],[Month]]=Dashboard!$B$1) This is the correct formula that I want to drag to the next colum in my dashboard but I need the month column within Table 1 to stay, instead I end up with this: =SUMPRODUCT(--(Table1[[#All],[Auto / Home / Other]]=Dashboard!$B$1),.
I realize I could remove the table and just lock the columns but it takes a while to calculate or the other case might be to maybe just take the array to the 100,000th row so that I'm not always having to update when I add data. Any help is greatly appreciated!
Thanks -
Jared
