Linking Cell based on Date

[Andy Roberts]

What I want to do is more complicated than my example below but once I find
a solution I can roll it out across my workbook. I have 2 worksheets (both
in the same workbook). In one I have rows and columns set up for sales
targets each month in various category:-

Month 1 Month 2 Month 3 etc
Cat 1 1 2 3
Cat 2 1 2 3
Cat 3 1 2 3

On the second sheet is a summary sheet which will be for a particular month.
I want to be able to enter the month in one cell and it populate each of the
categories based on the first sheet e.g.

Month 1
Cat 1 1
Cat 2 1
Cat 3 1

If I then changed the cell with "Month 1 in it to "Month 2" I would see the
following:-

Month 2
Cat 1 2
Cat 2 2
Cat 3 2

The aim is to fill in the first sheet for the whole year each month but
change the second sheet to just display a particular months figures as
needed.

Regards

Andy

 

[Claus Busch]

Hi Andy,

Am Tue, 15 Jan 2013 14:52:11 -0000 schrieb Andy Roberts:

Month 1 Month 2 Month 3 etc
Cat 1 1 2 3
Cat 2 1 2 3
Cat 3 1 2 3

On the second sheet is a summary sheet which will be for a particular month.
I want to be able to enter the month in one cell and it populate each of the
categories based on the first sheet e.g.

Month 1
Cat 1 1
Cat 2 1
Cat 3 1

in sheet2 B2:
=INDEX(Sheet1!$A$1:$D$4,MATCH(A2,Sheet1!$A$1:$A$4,0),MATCH($B$1,Sheet1!$A$1:$D$1,0))

Regards
Claus Busch

 

[Andy Roberts]

Spot on Claus, many thanks

Hi Andy,

Am Tue, 15 Jan 2013 14:52:11 -0000 schrieb Andy Roberts:


in sheet2 B2:
=INDEX(Sheet1!$A$1:$D$4,MATCH(A2,Sheet1!$A$1:$A$4,0),MATCH($B$1,Sheet1!$A$1:$D$1,0))

Regards
Claus Busch

 

[Andy Roberts]

Ok that works for me, I now want to move it on a stage.

The set up is the same but i have 2 columns per month in each category (a
tyarget and a actual) so it now looks like this.

Month 1 Month 2 Month 3
Tar Act Tar Act Tar Act
Cat 1 1 2 3 3 2 3
Cat 2 2 2 3 3 2 3
Cat 3 3 3 3 3 2 3

Therefore the formaula cant match the correct elements as the month title is
across 2 merged columns (for presentation).

Any thoughts?

 

[Claus Busch]

Hi Andy,

Am Tue, 15 Jan 2013 15:33:49 -0000 schrieb Andy Roberts:

Month 1 Month 2 Month 3
Tar Act Tar Act Tar Act
Cat 1 1 2 3 3 2 3
Cat 2 2 2 3 3 2 3
Cat 3 3 3 3 3 2 3

Therefore the formaula cant match the correct elements as the month title is
across 2 merged columns (for presentation).

in B2 of sheet 2 try:
=INDEX(Sheet1!$A$1:$D$5,MATCH($A3,Sheet1!$A$1:$A$5,0),RIGHT($B$1,1)+COLUMN(A1))
and copy down and to the right


Regards
Claus Busch

 

[Claus Busch]

Hi Andy,

Am Tue, 15 Jan 2013 16:47:11 +0100 schrieb Claus Busch:

in B2 of sheet 2 try:
=INDEX(Sheet1!$A$1:$D$5,MATCH($A3,Sheet1!$A$1:$A$5,0),RIGHT($B$1,1)+COLUMN(A1))

sorry, the formula above is wrong.
In B3 try:
=INDEX(Sheet1!$A$1:$G$5,MATCH($A3,Sheet1!$A$1:$A$5,0),RIGHT($B$1,1)*2)
and in C3:
=INDEX(Sheet1!$A$1:$G$5,MATCH($A3,Sheet1!$A$1:$A$5,0),RIGHT($B$1,1)*2+1)


Regards
Claus Busch

 

[Andy Roberts]

Thanks Claus

I'm not sure if that works either as I'm getting the wrong value in the
columns and changing the date at the top isn't making any difference. My
test file is uploaded to http://www.formbysurveys.com/Test.xls (24kb)

Andy

Win XP Pro
Office 2010

 

[Claus Busch]

Hi Andy,

Am Tue, 15 Jan 2013 16:54:31 -0000 schrieb Andy Roberts:

I'm not sure if that works either as I'm getting the wrong value in the
columns and changing the date at the top isn't making any difference. My
test file is uploaded to http://www.formbysurveys.com/Test.xls (24kb)

your data now looks very different to the example.
In B3 try:
=INDEX(Sheet1!$A$1:$G$5,MATCH($A3,Sheet1!$A$1:$A$5,0),MATCH($B$1,Sheet1!$A$1:$G$1,0))
and in C3:
=INDEX(Sheet1!$A$1:$G$5,MATCH($A3,Sheet1!$A$1:$A$5,0),MATCH($B$1,Sheet1!$A$1:$G$1,0)+1)


Regards
Claus Busch

 

[Andy Roberts]

Apologies Claus I thought my description was what I was trying to do.
Thanks for your help as your latest suggestion works a treat. Many thanks
again

Andy