Dragging Offset Function

[ajt8888]

I am led to believe that the offset function will help you use cells that are not next to each other together. Whilst I can do this for adjacent cells it doesnt work beyond that and you cant drag the formula either up down left or right to make it work so I cant see why anyone would use it, I have just given up and done it manually as its far quicker. If anyone has any ideas on this please see link, I have highlighted the cells I want the data from and for the top set I have paste linked which I think is easier. File

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about in AB5

=INDEX($B$4:$V$9,MATCH("Gross sales",$A$4:$A$9,0),MATCH(AA5,$B$2:$U$2,0))

and in AC5

=INDEX($B$4:$V$9,MATCH("Gross sales",$A$4:$A$9,0),MATCH(AA5,$B$2:$U$2,0)+1)

 

[ajt8888]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about in AB5

=INDEX($B$4:$V$9,MATCH("Gross sales",$A$4:$A$9,0),MATCH(AA5,$B$2:$U$2,0))

and in AC5

=INDEX($B$4:$V$9,MATCH("Gross sales",$A$4:$A$9,0),MATCH(AA5,$B$2:$U$2,0)+1)

Thanks for your comments I will look into amending my account details. I did try your solution but as i anticipated there is not a workable solution to this so I will do it manually all I got from your system was a column of zero's but thanks for trying anyway.

 

[Fluff]

Thanks for updating your profile.
Not sure why you get a column of zero's, as it works for me with your file.

Takings.xlsx
	AA	AB	AC
1
2
3
4	Date	Qty	(£) Value
5	Sunday 31/10/2021	3	6.1
6	Monday 01/11/2021	76	323.1
7	Tuesday 02/11/2021	26	91.05
8	Wednesday 03/11/2021	0	0
9	Thursday 04/11/2021	7	20.3
10	Friday 05/11/2021	31	134.65
11	Saturday 06/11/2021	4	16.75
43c7c60dd6aa392f178bf53ef08b5f7

Cell Formulas
Range	Formula
AB4:AC4	AB4	=B3
AA5	AA5	=B2
AB5:AB11	AB5	=INDEX($B$4:$V$9,MATCH("Gross sales",$A$4:$A$9,0),MATCH(AA5,$B$2:$U$2,0))
AC5:AC11	AC5	=INDEX($B$4:$V$9,MATCH("Gross sales",$A$4:$A$9,0),MATCH(AA5,$B$2:$U$2,0)+1)
AA6	AA6	=E2
AA7	AA7	=H2
AA8	AA8	=K2
AA9	AA9	=N2
AA10	AA10	=Q2
AA11	AA11	=T2

 

[ajt8888]

Thanks
I didnt realise I still had to manually enter the formula for the dates and whilst it all works fine for the top set of figures, I have tried modifying the formula for the ones below because cut and paste won't work but that doesn't work either. I have given up on this now and written to the till company to ask if they can build a different format for their report as it useless as it currently is. I shall mark this post as complete because its daft trying to resolve something that should be fixed at source. Thank you for trying and the time you expended on this.

 

[Fluff]

Glad to help & thanks for the feedback.