Linking Cell based on Date


A

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
 
Ad

Advertisements

C

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
 
A

Andy Roberts

Spot on Claus, many thanks


Claus Busch said:
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
 
A

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?
 
C

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
 
C

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
 
Ad

Advertisements

C

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
 
Ad

Advertisements

A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top