Incrementing Columns

[NickinLondon]

Hi

I would be grateful for some help, I have a data sheet with sales by month. Column A shows the monthly dates and columns B onwards show the monthly sales per store, I have a separate tab for each store and want to copy formulae down rows which increase/increment the column reference by two columns. I think it is an OFFSET function but can't work it out, how can I increment columns in formula going down rows??

Thanks
nick

 

[T. Valko]

Hi

I would be grateful for some help, I have a data sheet with sales by month. Column A shows the monthly dates and columns B onwards show the monthly sales per store, I have a separate tab for each store and want to copy formulae down rows which increase/increment the column reference by two columns. I think it is an OFFSET function but can't work it out, how can I increment columns in formula going down rows??

Thanks
nick

See if this helps...

Book1
	A	B	C	D	E	F
1	_	9	37	68	51	68
2	9	_	_	_	_	_
3	37	_	_	_	_	_
4	68	_	_	_	_	_
5	51	_	_	_	_	_
6	68	_	_	_	_	_
Sheet1

This formula entered in A2 and copied down:

=INDEX(B$1:F$1,ROWS(A$2:A2))

 

[NickinLondon]

Hi

Thanks for the reply, I dont know how to attach a sample to the thread, so have copied my existing formula below. I have tried to use the ROWS formulae in my spreadsheet but still cant figure it out..

=IFERROR((PRODUCT(INDEX(Sheet1!$B:$B,MATCH($B$1,Sheet1!$A:$A)):INDEX(Sheet1!$B:$B,MATCH($B$1,Sheet1!$A:$A))/100+1)-1)*100,"n/a")

B1 is a date from dropdown list, column A is a range of monthly dates and column B, D, F are monthly performance figures for each store.. I would attach a sample but dont know how to..

I need to copy this formula down rows which will increase the column reference on sheet 1 from column B to D..

I think it offset and rows but cant get my head round it, i would be grateful for your advice..!

Thanks

 

[T. Valko]

Hi

Thanks for the reply, I dont know how to attach a sample to the thread, so have copied my existing formula below. I have tried to use the ROWS formulae in my spreadsheet but still cant figure it out..

=IFERROR((PRODUCT(INDEX(Sheet1!$B:$B,MATCH($B$1,Sheet1!$A:$A)):INDEX(Sheet1!$B:$B,MATCH($B$1,Sheet1!$A:$A))/100+1)-1)*100,"n/a")

B1 is a date from dropdown list, column A is a range of monthly dates and column B, D, F are monthly performance figures for each store.. I would attach a sample but dont know how to..

I need to copy this formula down rows which will increase the column reference on sheet 1 from column B to D..

I think it offset and rows but cant get my head round it, i would be grateful for your advice..!

Thanks

Your formula doesn't make sense to me.

Your INDEX:INDEX expression is returning a 1 cell range.

I think you can get rid of the PRODUCT function, also.

Since I'm not sure what you're doing with this I'll just show you how to increment the INDEX function.

You need to index the entire range of interest. We'll assume it's B:F.

Assuming you enter the formula in cell H1 and copy down, the INDEX expression would be:

INDEX(Sheet1!$B:$F,MATCH($B$1,Sheet1!$A:$A),ROWS(H$1:H1)*2-1)

 

[NickinLondon]

Thanks Valko, I understand what you mean, the reason for the index:index is that I am getting the product of 3 and 6 months too. therefore i change it look back three rows above the last value, noted below:

=IFERROR((PRODUCT(INDEX(Sheet1!$B:$B,MATCH($B$1,Sheet1!$A:$A)):INDEX(Sheet1!$B:$B,MATCH($B$1,Sheet1!$A:$A)-3)/100+1)-1)*100,"n/a")

Not sure if/how i can post a sample spreadsheet..

 

[T. Valko]

Thanks Valko, I understand what you mean, the reason for the index:index is that I am getting the product of 3 and 6 months too. therefore i change it look back three rows above the last value, noted below:

=IFERROR((PRODUCT(INDEX(Sheet1!$B:$B,MATCH($B$1,Sheet1!$A:$A)):INDEX(Sheet1!$B:$B,MATCH($B$1,Sheet1!$A:$A)-3)/100+1)-1)*100,"n/a")

Not sure if/how i can post a sample spreadsheet..

OK, I see now. It wasn't in your original formula.