Locking columns within a table in a formula

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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Watch MrExcel Video

Forum statistics

Threads
1,095,400
Messages
5,444,253
Members
405,278
Latest member
Rashford

This Week's Hot Topics

Top