Combo Pivot Table

**no.18shirt** · 07-03-2013, 03:02 AM

Hi

I've attached a sample workbook where I want to be able to create one Pivot Table out of the data where dates can be along the X axis and the variables 1-16 along the Y axis. But I want to be able to use a check box to be able to select which variables I want to see at any one time eg So I could compare Variables 1 and 2 on the same graph but not see any others.

Is this possible - if so how can it be achieved? I'm using Excel 2013.

Thanks

**oeldere** · 07-03-2013, 03:27 AM

Maybe something like this?

Make sure there are no gaps (empty rows) in column A.

I noticed you used blanc cells and 0 as result.

Is there a specific reason for it?

**no.18shirt** · 07-03-2013, 03:58 AM

Looks good but I need to apply that to my actual data now.

How did you get all the dates to appear under the 'date' heading in the pivot table? If I do it, under the 'dates' column appear all the variables and all the dates appear as the individual dates eg

1/03/13
2/03/13
3/03/13
4/04/13

etc

Please guide me through how you did yours from selecting the data.

Many thanks,

**oeldere** · 07-03-2013, 04:04 AM

Maybe you can anwer my question (in #2) first.

1) You don't have a question on how to put the data on the output sheet?

2) You have a question on the pivot table.

**no.18shirt** · 07-03-2013, 04:12 AM

Yeah blank cells are when there's no data for that day so it can be a zero.

With regards to my question. If you could guide me through how you got the data onto the output sheet then that should answer it. I know I'm doing something wrong but I'm not sure what so if I could see exactly how you did it (step by step) that would be great.

I'm looking to learn how it's done, not just have it done for me.

Thanks a lot.

**oeldere** · 07-03-2013, 04:19 AM

hi no.18shirt,

At first, did you remove the gaps (empty value) in column A?

I put the data (in A1) => in A3.

After that I used the macro CONVERTROWSTOCOL_Oeldere_revisted

Do you know how to run this macro?

If you accomplished that, we can follow for the other questions.

Please Login or Register  to view this content.

**no.18shirt** · 07-03-2013, 05:19 AM

OK I think I managed to get all the way there and got it just how you did, so thanks for the help.

On a different page I have some data in cells A3:JN8 that gets added to daily so extending out to columns JO,JP,JQ etc on a daily basis.

What I want to do is copy and paste this data to another tab so I can 'transpose paste' the data and make it into a pivot table.

I found transposing the data easier than running the macro as that gave an error.

How can I have it so the data in the new tab will be automatically added to whenever I add new data in the original sheet into columns JO,JP and JQ and onwards?

Ideally I just want to press refresh and have the data there in an instant.

How would I do this?

Thanks