Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

DankTamagachi: Jan 20, 2005; Tamagachi to your throat!

I have two worksheets I use daily for work. One is a shared sheet that tracks progress on projects and the second is a sheet that runs macros to generate lists of words. When the shared sheet is closed, the generator runs very quickly. However, when the shared sheet is open, the generator runs extremely slowly, generating terms line-by-line. Why does the completely separate generator sheet slow down when the other is opened? Has anyone encountered this before?

# ¿ Apr 4, 2011 17:56

Adbot: ADBOT LOVES YOU

# ¿ May 6, 2024 19:42

DankTamagachi: Jan 20, 2005; Tamagachi to your throat!

I'm trying to create a pivot table where there are multiple children of one item in the hierarchy. It's hard to describe, but here we go:

I want to be able to show a table with a list of names and total cost. Each total cost is the sum of three values that are also in the table.
My table is set up like this:

code:

Name  |  ID  |  Item 1a  |  Item 1b  |  Item 1c  |  Total 1  |  Item 2a  |  Item 2b  | Item 2c  |  Total 2

I'd like to be able to (in a pivot table, ideally) expand a "total cost" cell to show the parts that made that total. So it looks like this:

Anyone have any ideas about how to do this?

# ¿ Jul 1, 2011 05:40

DankTamagachi: Jan 20, 2005; Tamagachi to your throat!

Aredna posted:

How hard is it to rearrange your source data? If you can set it up as 4 columns (name,id,item,cost) then it will be easy to get the pivot table very close to what you want. Note, you don't need a row for total in this setup. The pivot table will sum up the individual parts into a total for you.

Once the data is in the above format, all you need to do is set the name as your column field and cost as your data field. Then double click on a name and select item and it will be added as an expanded 2nd column with the item level details. To show and hide the details for each name you just double click them. You only need to select the item field the first time and I'm sure there is another way to add it with all of the details collapse by default, but I don't know it without Excel in front of me.

I can probably write some VBA to rearrange the data to include separate rows for each item. If I manage that, how would I ensure the data looks like the mock-up above?

# ¿ Jul 1, 2011 18:19

DankTamagachi: Jan 20, 2005; Tamagachi to your throat!

esquilax posted:

Or, you could just make a few extra columns with formulas and base a pivot off that.

For example, make E2 be "=if(A2="", E1, A2)" and copy it down to the bottom of the data. Then run a pivot off of columns C:E. You can hide the blank rows inside the pivot.

I ended up doing something very similar to this and it worked great, thanks!

Next question:

I have a pivot table with columns of scores, each row represents a person. How can I use conditional formatting to highlight cells where the value is greater than the value of the previous column? I want to make them one color if the value is trending up and another if the value is trending down.

Thanks!

# ¿ Jul 6, 2011 00:46

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread