Alternative to Indirect

[ValiMandy]

Hello,

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

[TABLE="class: grid, width: 1000, align: center"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[TD]Column H[/TD]
[TD]Column I[/TD]
[/TR]
[TR]
[TD]Item #[/TD]
[TD]Store 1 Units[/TD]
[TD]Store 1 Cases[/TD]
[TD]Store 2 Units[/TD]
[TD]Store 2 Cases[/TD]
[TD]Store 3 Units[/TD]
[TD]Store 3 Cases[/TD]
[TD]Total Units[/TD]
[TD]Total Cases[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]50[/TD]
[TD]2[/TD]
[TD]25[/TD]
[TD]1[/TD]
[TD]75[/TD]
[TD]3[/TD]
[TD]=SUM(B2,D2,F2)[/TD]
[TD]=SUM(C2,E2,G2)[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]25[/TD]
[TD]1[/TD]
[TD]25[/TD]
[TD]1[/TD]
[TD]50[/TD]
[TD]2[/TD]
[TD]=SUM(B3,D3,F3)[/TD]
[TD]=SUM(C3,E3,G3)[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]75[/TD]
[TD]3[/TD]
[TD]25[/TD]
[TD]1[/TD]
[TD]75[/TD]
[TD]3[/TD]
[TD]=SUM(B4,D4,F4)[/TD]
[TD]=SUM(C4,E4,G4)[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[sheetspread]

Try sumif with a search

 

[shg]

Welcome to the board.

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][td="bgcolor:#C0C0C0"]

I​

[/td][td="bgcolor:#C0C0C0"]

J​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Item #​

[/td][td="bgcolor:#F3F3F3"]

Store 1 Units​

[/td][td="bgcolor:#F3F3F3"]

Store 1 Cases​

[/td][td="bgcolor:#F3F3F3"]

Store 2 Units​

[/td][td="bgcolor:#F3F3F3"]

Store 2 Cases​

[/td][td="bgcolor:#F3F3F3"]

Store 3 Units​

[/td][td="bgcolor:#F3F3F3"]

Store 3 Cases​

[/td][td="bgcolor:#F3F3F3"]

Units​

[/td][td="bgcolor:#F3F3F3"]

Cases​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

123​

[/td][td]

50​

[/td][td]

2​

[/td][td]

25​

[/td][td]

1​

[/td][td]

75​

[/td][td]

3​

[/td][td]

150​

[/td][td]

6​

[/td][td]H2 and across and down: =SUMIF($B$1:$G$1, "*" & H$1, $B2:$G2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

456​

[/td][td]

25​

[/td][td]

1​

[/td][td]

25​

[/td][td]

1​

[/td][td]

50​

[/td][td]

2​

[/td][td]

100​

[/td][td]

4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

789​

[/td][td]

75​

[/td][td]

3​

[/td][td]

25​

[/td][td]

1​

[/td][td]

75​

[/td][td]

3​

[/td][td]

175​

[/td][td]

7​

[/td][td][/td][/tr]
[/table]

Note the change to H1 and J1.

 

[ValiMandy]

Thanks for your input! I'll try it!

 

[ValiMandy]

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.

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[TD="bgcolor: #C0C0C0"]

C​

[/TD]
[TD="bgcolor: #C0C0C0"]

D​

[/TD]
[TD="bgcolor: #C0C0C0"]

E​

[/TD]
[TD="bgcolor: #C0C0C0"]

F​

[/TD]
[TD="bgcolor: #C0C0C0"]

G​

[/TD]
[TD="bgcolor: #C0C0C0"]

H​

[/TD]
[TD="bgcolor: #C0C0C0"]

I​

[/TD]
[TD="bgcolor: #C0C0C0"]

J​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

1​

[/TD]
[TD="bgcolor: #F3F3F3"]

Item #​

[/TD]
[TD="bgcolor: #F3F3F3"]

Store 1 Units​

[/TD]
[TD="bgcolor: #F3F3F3"]

Store 1 Cases​

[/TD]
[TD="bgcolor: #F3F3F3"]

Store 2 Units​

[/TD]
[TD="bgcolor: #F3F3F3"]

Store 2 Cases​

[/TD]
[TD="bgcolor: #F3F3F3"]

Store 3 Units​

[/TD]
[TD="bgcolor: #F3F3F3"]

Store 3 Cases​

[/TD]
[TD="bgcolor: #F3F3F3"]

Units​

[/TD]
[TD="bgcolor: #F3F3F3"]

Cases​

[/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]

123​

[/TD]
[TD]

50​

[/TD]
[TD]

2​

[/TD]
[TD]

25​

[/TD]
[TD]

1​

[/TD]
[TD]

75​

[/TD]
[TD]

3​

[/TD]
[TD]

150​

[/TD]
[TD]

6​

[/TD]
[TD]H2 and across and down: =SUMIF($B$1:$G$1, "*" & H$1, $B2:$G2)[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD]

456​

[/TD]
[TD]

25​

[/TD]
[TD]

1​

[/TD]
[TD]

25​

[/TD]
[TD]

1​

[/TD]
[TD]

50​

[/TD]
[TD]

2​

[/TD]
[TD]

100​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

4​

[/TD]
[TD]

789​

[/TD]
[TD]

75​

[/TD]
[TD]

3​

[/TD]
[TD]

25​

[/TD]
[TD]

1​

[/TD]
[TD]

75​

[/TD]
[TD]

3​

[/TD]
[TD]

175​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Note the change to H1 and J1.

 

[shg]

You're welcome.