Alternative to Indirect

ValiMandy

New Member
Joined
May 10, 2016
Messages
31
Hello,

I am currently working on a spreadsheet that looks somewhat like this:

Column AColumn BColumn CColumn DColumn EColumn FColumn GColumn HColumn I
Item #Store 1 UnitsStore 1 CasesStore 2 UnitsStore 2 CasesStore 3 UnitsStore 3 CasesTotal UnitsTotal Cases
123502251753=SUM(B2,D2,F2)=SUM(C2,E2,G2)
456251251502=SUM(B3,D3,F3)=SUM(C3,E3,G3)
789753251753=SUM(B4,D4,F4)=SUM(C4,E4,G4)

<tbody>
</tbody>

My question is this: I need the "Store" & "Case" columns to be every other and I need the sum of each. However, I also need to be able to delete an entire column. So, of course when I delete columns I get a reference error. I tried using Indirect =SUM(INDIRECT("B2"),INDIRECT("D2"),INDIRECT("F2")) so I could delete them but then it moves the total columns into the columns that are being added and causes issues. Is there a way to do this?

Hopefully I am explaining this well enough.

Thank you very much!

Mandy
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,722
Office Version
2010
Platform
Windows
Welcome to the board.

A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
1​
Item #
Store 1 Units
Store 1 Cases
Store 2 Units
Store 2 Cases
Store 3 Units
Store 3 Cases
Units
Cases
2​
123​
50​
2​
25​
1​
75​
3​
150​
6​
H2 and across and down: =SUMIF($B$1:$G$1, "*" & H$1, $B2:$G2)
3​
456​
25​
1​
25​
1​
50​
2​
100​
4​
4​
789​
75​
3​
25​
1​
75​
3​
175​
7​

Note the change to H1 and J1.
 

ValiMandy

New Member
Joined
May 10, 2016
Messages
31
Perfect! Thank you very much! I have a feeling this forum is going to be a big part of my life. Hopefully, I will get a chance to give back! :)

Welcome to the board.

A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
1​
Item #
Store 1 Units
Store 1 Cases
Store 2 Units
Store 2 Cases
Store 3 Units
Store 3 Cases
Units
Cases
2​
123​
50​
2​
25​
1​
75​
3​
150​
6​
H2 and across and down: =SUMIF($B$1:$G$1, "*" & H$1, $B2:$G2)
3​
456​
25​
1​
25​
1​
50​
2​
100​
4​
4​
789​
75​
3​
25​
1​
75​
3​
175​
7​

<tbody>
</tbody>


Note the change to H1 and J1.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,722
Office Version
2010
Platform
Windows
You're welcome.
 

Forum statistics

Threads
1,085,307
Messages
5,382,838
Members
401,807
Latest member
xlWatcher

Some videos you may like

This Week's Hot Topics

Top