Extracting cells from a table

[John]

I have a table of rows listing budget, actuals and variance for each line
item in my budget. The second column, titled "Item", contains the budget item
name and the last column, titled "Percent", is the percentage over or under
budget for that item. I want to create a second worksheet in the workbook
that lists the item names and percentages for all items that are more than
10% over budget.

Is there a function I can use to pull this list of item names and
percentages? Pls give me an idea how to write this function.

Thank you for your help, John

 

[Ron Coderre]

Would you consider using a Pivot Table?

On the destination sheet....
From the Excel Main Menu: <Data><Pivot Table>
Use: Excel……Click [Next]
Select your data……Click [Next]
Click the [Layout] button

ROW:
Drag the ITEM field here
Drag the VARIANCE field here

COLUMN: (leave this area blank)

DATA: Drag the VARIANCE field here
If it doesn't list as Sum of VARIANCE
....dbl-click it and set it to Sum.... Click [OK]

Select where you want the Pivot Table…Click [Finish].

That will list each ITEM and VARIANCE
and the sum of the VARIANCE (which will only be one number).

To remove the subtotals
....Right-Click on the ITEM heading
....Select: Field Settings....Subtotals: None

Next....Right-Click on the VARIANCE heading and
UNcheck the percentages that are 10% or less.

The remaining visible variances will be more than 10%.

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?
Post back if you have more questions.

 

[John]

Ron, this helped a lot. But I forgot to tell you I had Office 2007, so it
took me a while to translate most of your instructions to 2007. I got the
pivot table constructed pretty well and was able to format it and get rid of
totals and subtotals. I'm having trouble w/ filtering, sorting and adding new
master table cols to the pivot table tho:

1. The only way I could filter the table was to click the drop down arrow
next to the % column and uncheck the 40 items I didn't want. How do I create
a filter on that col to keep only % < -10%, so I don't have to uncheck a
bazillion items?

2. How do I sort the table lowest to highest %? Clicking the drop down on %
and selecting Sort Z-A does nothing. The table stays sorted on the Item col
(the first col).

3. I thought of a couple more cols to add to my master table. Is there a way
to add them into the pivot table without having to recreate the entire thing?

I sure appreciate your help, John

Would you consider using a Pivot Table?

On the destination sheet....
From the Excel Main Menu: <Data><Pivot Table>
Use: Excel……Click [Next]
Select your data……Click [Next]
Click the [Layout] button

ROW:
Drag the ITEM field here
Drag the VARIANCE field here

COLUMN: (leave this area blank)

DATA: Drag the VARIANCE field here
If it doesn't list as Sum of VARIANCE
...dbl-click it and set it to Sum.... Click [OK]

Select where you want the Pivot Table…Click [Finish].

That will list each ITEM and VARIANCE
and the sum of the VARIANCE (which will only be one number).

To remove the subtotals
...Right-Click on the ITEM heading
...Select: Field Settings....Subtotals: None

Next....Right-Click on the VARIANCE heading and
UNcheck the percentages that are 10% or less.

The remaining visible variances will be more than 10%.

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?
Post back if you have more questions.

-----------------
Regards,

Ron
Microsoft MVP - Excel

I have a table of rows listing budget, actuals and variance for each line
item in my budget. The second column, titled "Item", contains the budget
item
name and the last column, titled "Percent", is the percentage over or
under
budget for that item. I want to create a second worksheet in the workbook
that lists the item names and percentages for all items that are more than
10% over budget.

Is there a function I can use to pull this list of item names and
percentages? Pls give me an idea how to write this function.

Thank you for your help, John

 

[Ron Coderre]

Hi, John

I don't use XL2007, so....instead of guessing, I'll let someone who does use
it give you proper instructions.

Regards,

Ron
Microsoft MVP - Excel

Ron, this helped a lot. But I forgot to tell you I had Office 2007, so it
took me a while to translate most of your instructions to 2007. I got the
pivot table constructed pretty well and was able to format it and get rid
of
totals and subtotals. I'm having trouble w/ filtering, sorting and adding
new
master table cols to the pivot table tho:

1. The only way I could filter the table was to click the drop down arrow
next to the % column and uncheck the 40 items I didn't want. How do I
create
a filter on that col to keep only % < -10%, so I don't have to uncheck a
bazillion items?

2. How do I sort the table lowest to highest %? Clicking the drop down on
%
and selecting Sort Z-A does nothing. The table stays sorted on the Item
col
(the first col).

3. I thought of a couple more cols to add to my master table. Is there a
way
to add them into the pivot table without having to recreate the entire
thing?

I sure appreciate your help, John

Would you consider using a Pivot Table?

On the destination sheet....
From the Excel Main Menu: <Data><Pivot Table>
Use: Excel……Click [Next]
Select your data……Click [Next]
Click the [Layout] button

ROW:
Drag the ITEM field here
Drag the VARIANCE field here

COLUMN: (leave this area blank)

DATA: Drag the VARIANCE field here
If it doesn't list as Sum of VARIANCE
...dbl-click it and set it to Sum.... Click [OK]

Select where you want the Pivot Table…Click [Finish].

That will list each ITEM and VARIANCE
and the sum of the VARIANCE (which will only be one number).

To remove the subtotals
...Right-Click on the ITEM heading
...Select: Field Settings....Subtotals: None

Next....Right-Click on the VARIANCE heading and
UNcheck the percentages that are 10% or less.

The remaining visible variances will be more than 10%.

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?
Post back if you have more questions.

-----------------
Regards,

Ron
Microsoft MVP - Excel

I have a table of rows listing budget, actuals and variance for each
line
item in my budget. The second column, titled "Item", contains the
budget
item
name and the last column, titled "Percent", is the percentage over or
under
budget for that item. I want to create a second worksheet in the
workbook
that lists the item names and percentages for all items that are more
than
10% over budget.

Is there a function I can use to pull this list of item names and
percentages? Pls give me an idea how to write this function.

Thank you for your help, John

 

[Max]

.. list the item names and percentages for all items

that are more than 10% over budget.

Assume source data is in sheet named: x,
data from row 2 down
where col B = Item, col K = Percent

In another sheet,
In A2: =IF(x!K2="","",IF(x!K2>10%,ROW(),""))
Leave A1 blank

In B2:
=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(x!B:B,SMALL(A:A,ROWS($1:1))))

In C2:
=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(x!K:K,SMALL(A:A,ROWS($1:1))))
Format C2 as percentage. Select A2:C2, copy down to cover the max expected
extent of data in x, say down to C200. Minimize col A. Cols B & C will return
the exact results that you seek, all neatly bunched at the top

 

[Max]

Here's a variation to the earlier play
to auto-extract the list in sorted descending order by x's col K
(just in case this is desired as well)

Assume source data is in sheet named: x,
data from row 2 down
where col B = Item, col K = Percent

In another sheet,
=IF(x!K2="","",IF(x!K2>10%,x!K2-ROW()/10^10,""))
Leave A1 blank

In B2:
=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(x!B:B,MATCH(LARGE(A:A,ROWS($1:1)),A:A,0)))

In C2:
=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(x!K:K,MATCH(LARGE(A:A,ROWS($1:1)),A:A,0)))

Format C2 as percentage. Select A2:C2, copy down to cover the max expected
extent of data in x, say down to C200. Minimize col A. Cols B & C will return
the descending-sort results, all neatly bunched at the top

 

[Max]

Typo here:

=IF(x!K2="","",IF(x!K2>10%,x!K2-ROW()/10^10,""))

should read as:
In A2: =IF(x!K2="","",IF(x!K2>10%,x!K2-ROW()/10^10,""))