Calculate cell row and column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a case where I have a pivot table one ROW designator with about 20
values. I also have about 10 values. What I want to do is have the data
listed as

ROW 1 Data value 1 Data value 2 Data value 3 instead of

Row 1 Data value1
Data value2
Data value3
Row 2 Data value1

etc.

Is there a way to do this that isn't tedious?

Thanks in advance,
Barb Reinhardt
 
Barb,

Drag the Data Value button to the top of the columns.

HTH,
Bernie
MS Excel MVP
 
Tried that. It didn't work. I have data for 10-12 subprograms summarized
as data for several metrics. I want to summarize each metric by subprogram.
If I put the metric at the top of the column, it summarizes it by value for
the metric.

Did I miss something?

I opted to use the "Transpose" function, but it's not as elegant as I'd like.

Barb Reinhardt
 
Barb,

If you could post a small table of your data, the result you get, and the
result you want, then it would help clarify what you mean.

HTH,
Bernie
MS Excel MVP
 
OK, here is an attempt at an example without real data
Metric 1 Metric 2 Metric 3 Metric 4
Group 1
Group 2
Group 3
Group 1
Group 2
Group 3
Group 1
Group 2
Group 3
Group 1
Group 2
Group 3

I want to sum metric 1, 2, 3, etc. or each Group. My pivot table looks
like this:

Group 1 Metric1
Metric2
Metric3
Group 2 Metric1
Metric2
Metric3
Group 3 Metric1
Metric2
Metric3

And I want
Metric 1 Metric 2 Metric 3
Sum Sum Sum
Group 1
Group 2
Group 3
 
Barb,

You don't actually have a database: you have a cross-tab table. To get it
to work the way you want, you need to "un-cross-tab" your data, by using a
pivot table in a special way, described at:

http://j-walk.com/ss/excel/usertips/tip068.htm

Once you've done that, you can use a pivot table in a normal way to get the
result you want.

HTH,
Bernie
MS Excel MVP
 
I never said I had a database. I'll take a look and see what I can glean
from the link.

Thanks,
Barb Reinhardt
 
Barb,

Yes, you never said that you had a database, but I wanted to be clear in my
description, since pivot tables expect database style data tables. That's
why I asked for an example of the type of data that you had.

HTH,
Bernie
MS Excel MVP
 

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

Similar Threads

can this be done with conditional formatting 3
multiple value in row 1
Pivot Table? ID, Date, Value sorting 4
formulae in excel worksheets 4
how to transpose a table into two columns? 1
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Checking Column Status on closing excel - Macro Needed 2
Copy rows to different sheets 1

Back
Top