Transpose Formula when Certain Criteria are met

[Gadgetgav]

Hi All,

Apologies if this has been asked before but I have searched and only found similar requests, nothing solving my exact issue.

I have some weekly KPI data which gets collated each month into charts. The data is entered each week and totaled using sum formulas onto a sheet. I have a chart on another sheet that I would like to transpose this data onto (the data currently has the months as column headers but the chart needs them to be as rows). My issue is that I only want to show last month and earlier and hide the data entered so far for this month.

Example:

Original data:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]150[/TD]
[TD]125[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

How I want to see the chart data:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Jan[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I would like to do is have a transpose formula to enter the data onto the chart but only if the month is lower than today's month.

I hope this makes sense.

Thanks in advance,
Gav

 

[James006]

Hi,

say your data in located in range A1:D2

in cell A5, you could have : =OFFSET($A$1,0,ROW()-5)

and in cell B5, you could have : =IF(MONTH("1"&A5)>=MONTH(TODAY()),"",OFFSET($A$1,1,MATCH(A5,$1:$1,0)-1))

Hope this will help

 

[Gadgetgav]

Hi,

say your data in located in range A1:D2

in cell A5, you could have : =OFFSET($A$1,0,ROW()-5)

and in cell B5, you could have : =IF(MONTH("1"&A5)>=MONTH(TODAY()),"",OFFSET($A$1,1,MATCH(A5,$1:$1,0)-1))

Hope this will help

Thanks for the help but unfortunately that won't work as the data is on a different sheet to the chart and is among other data so it's not as simple as just offsetting.

 

[theBardd]

Use Power Query (Data>Get & Transform) , unpivot the data, and the chart the result table.

 

[James006]

Thanks for the help but unfortunately that won't work as the data is on a different sheet to the chart and is among other data so it's not as simple as just offsetting.

Do not know the complexity of your workbook ...

But Offset can easily operate across sheets ...

 

[Gadgetgav]

This has actually helped a lot as it helped me realise I can just do a HLookup using the month headers. Thank you

 

[James006]

Glad you could fix your problem