Skipping Columns in Forumla and Replicating across rows

[burtm05]

Hi...

I'm hoping a guru here can help answer this one...

I have a table I am trying to fill based on data from another sheet. All the data is stored in a single row UPC!D5:KM5. The data needed to fill the table is in every other column starting with E5, so my data points are E5, G5, I5, K5, M5. I've gotten this to work using an Index formula relatively well for a single row in the table.

The issue I am having is I need to replicate this formula down to the next row in my table; however, my data repeats itself horizontally in the source sheet with the next start point being O5, there is a separator column (N5) for visual formatting.

I need to repeat this exercise across a total of 24 rows down in the table with 5 columns of data in the repeating pattern.

Any ideas?

 

[S Oberlander]

Can you provide sample data?

 

[burtm05]

Can you provide sample data?

Attached is a rough sample of what I am trying to accomplish. I populated the first sheet (UPC) with sample data spanning row 5. On the second sheet, PriceList, I created the table I want to populate. C5:G5 I referenced the cells directly so you can see the data pattern, C6:G7 are partially filled and left open so you can see the pattern I am attempting to achieve.

ColumnSkipRepeatRowSample.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	AL	AM	AN	AO	AP	AQ	AR
1
2		A4 Mono Printer											A4 Mono Printer											A4 Mono Printer											A4 Color Printer
3		Device1 Short		Device1 Long		Device1 Long		Device1 Long		Device1 Print Only			Device2 Short		Device2 Long		Device2 Long		Device2 Long		Device2 Print Only			Device3 Short		Device3 Long		Device3 Long		Device3 Long		Device3 Print Only			Device4 Short		Device4 Long		Device4 Long		Device4 Long		Device4 Print Only
4		without escalation	with escalation	without escalation	with escalation	without escalation	with escalation	without escalation	with escalation	without escalation	with escalation		without escalation	with escalation	without escalation	with escalation	without escalation	with escalation	without escalation	with escalation	without escalation	with escalation		without escalation	with escalation	without escalation	with escalation	without escalation	with escalation	without escalation	with escalation	without escalation	with escalation		without escalation	with escalation	without escalation	with escalation	without escalation	with escalation	without escalation	with escalation	without escalation	with escalation
5		$10.05	$12.57	$14.33	$16.55	$19.25	$22.58	$26.58	$30.05	$15.00	$18.01		$45.01	$50.12	$25.47	$26.99	$12.35	$18.55	$22.45	$26.58	$7.58	$8.58		$56.58	$60.58	$36.59	$45.10	$26.35	$25.99	$37.88	$48.52	$35.21	$42.51		$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
UPC

Cell Formulas
Range	Formula
AI5,AK5,AM5,AO5,AQ5	AI5	=+AI101
AJ5,AL5,AN5,AP5,AR5	AJ5	=+AI108

ColumnSkipRepeatRowSample.xlsx
	A	B	C	D	E	F	G
1
2		Price List	6 months	36 months	48 months	60 months	12 months
3			Monthly Payments	Monthly Payments	Monthly Payments	Monthly Payments	Monthly Payments
4		Models	Short Term	Long Term	Long Term	Long Term	Print Only
5		Device1	$12.57	$16.55	$22.58	$30.05	$18.01
6		Device2	$50.12	$26.99
7		Device3				$48.52	$42.51
PriceList

Cell Formulas
Range	Formula
C5	C5	=UPC!C5
D5	D5	=UPC!E5
E5	E5	=UPC!G5
F5	F5	=UPC!I5
G5	G5	=UPC!K5
C6	C6	=UPC!N5
D6	D6	=UPC!P5
F7	F7	=UPC!AE5
G7	G7	=UPC!AG5

Thanks!

 

[S Oberlander]

You are trying to drag from c5 to g5 that the formula should refer to 11 columns later then the previous cell. I don't think that's possible.
Maybe you can try to name your data with more detail so you can perhaps try a lookup formula instead.

 

[Marcelo Branco]

Try

C5 copied across and down
=INDEX(UPC!$B$5:$AR$5,MATCH($B5&"*",UPC!$B$3:$AR$3,0)+2*COLUMNS($C5:C5)-1)

M.

 

[burtm05]

Try

C5 copied across and down
=INDEX(UPC!$B$5:$AR$5,MATCH($B5&"*",UPC!$B$3:$AR$3,0)+2*COLUMNS($C5:C5)-1)

M.

That formula is ?. It seems to be exactly what I needed. Thank you very much!

 

[Marcelo Branco]

You're welcome. Glad to help.

M.