Extracting cells from a table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John - 18 May 2008 16:25 GMT

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

Reply to this Message

Ron Coderre - 18 May 2008 17:14 GMT

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
[quoted text clipped - 9 lines]
>
> Thank you for your help, John

Reply to this Message

John - 18 May 2008 20:26 GMT

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?
>
[quoted text clipped - 52 lines]
> >
> > Thank you for your help, John

Reply to this Message

Ron Coderre - 18 May 2008 22:36 GMT

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
[quoted text clipped - 83 lines]
>> >
>> > Thank you for your help, John

Reply to this Message

Max - 19 May 2008 01:14 GMT

> .. 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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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
[quoted text clipped - 7 lines]
>
> Thank you for your help, John

Reply to this Message

Max - 19 May 2008 01:25 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Max - 19 May 2008 01:37 GMT

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,""))

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message