Paste Formula & Skip Columns

[dhewett]

I've tried INDEX and OFFSET. I'm doing something wrong or using the wrong formulas.

Here's a "dumbed down" version of the sheets for reference.

I have a "goals" sheet with goals in columns B-M. I need to include those in my "report" sheet but skip two columns every time I paste or compute the formula.

In other words the goals from columns B-M will be pasted into the report columns B, E, H, K, etc.

Plant A Goals.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Jan.	Feb.	March	April	May	June	July	Aug.	Sept.	Oct.	Nov.	Dec.
2	Daily	3,400	3,400	3,400	4,200	4,200	4,200	4,400	4,400	4,400	4,400	4,400	4,400
goals

Plant A Goals.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1		Jan 24			Feb 24			Mar 24			Apr 24			May 24			Jun 24			Jul 24			Aug 24			Sep 24			Oct 24			Nov 24			Dec 24
2	Plant	Daily	Goal	%	Daily	Goal	%	Daily	Goal	%	Daily	Goal	%	Daily	Goal	%	Daily	Goal	%	Daily	Goal	%	Daily	Goal	%	Daily	Goal	%	Daily	Goal	%	Daily	Goal	%	Daily	Goal	%
3	Plant A
report

Cell Formulas
Range	Formula
E1,H1,K1,N1,Q1,T1,W1,Z1,AC1,AF1,AI1	E1	=EDATE(B1,1)

 

[Jeffrey Mahoney]

You should use Sumif or Sumifs in Cell B3, E3, etc. You would need to change your headers on Goals sheet to be dates and then number format them to show only the month name.

The formula would look something like this in Cell B3:
=Sumifs(Goals!B2:M2,Goals!B1:M1,B1)
This assumes you have dates in row 1 on the Report sheet and the date for January starts in cell B1

 

[dhewett]

You should use Sumif or Sumifs in Cell B3, E3, etc. You would need to change your headers on Goals sheet to be dates and then number format them to show only the month name.

The formula would look something like this in Cell B3:
=Sumifs(Goals!B2:M2,Goals!B1:M1,B1)
This assumes you have dates in row 1 on the Report sheet and the date for January starts in cell B1

I don't know why I didn't think of that Jeffrey. Simple solution. Thank you for helping overcome my mental fog today!