Pulling data from a big report

[nbeaird_51]

I am looking for a way to pull data (ie a specific state's financials) from a
huge report that I have. Need a drop tab or something so when I click on
State abbrevation it pulls only that states financials from the bigger
report? How do I do this???

[JulieD]

Hi

does data / filter / autofilter give you what you need?
if not, how is your data structured?

Cheers
JulieD

"nbeaird_51" <[email protected]> wrote in message
news:[email protected]...
>I am looking for a way to pull data (ie a specific state's financials) from
>a
> huge report that I have. Need a drop tab or something so when I click on
> State abbrevation it pulls only that states financials from the bigger
> report? How do I do this???

[nbeaird_51]

I have tried this the data is posted in a way that the state is a header and
then it goes into different products we offer going down a column. The rows
that grow accross are the financials from years past up till the current year
Example:
State 1-03 2-03 3-03 4-03.....
Product $$$$$$$$$$$$$$$$.....
Product $$$$$$$$$$$$$$$....
Product $$$$$$$$$$$$$$$.....

It does this fore every state and I want to be able to viewan individual
state from this bigger list to ease data entry and printing

ANY HELP APPRECIATED!!! Thanks and Happy Early St Patty's
"JulieD" wrote:

> Hi
>
> does data / filter / autofilter give you what you need?
> if not, how is your data structured?
>
> Cheers
> JulieD
>
> "nbeaird_51" <[email protected]> wrote in message
> news:[email protected]...
> >I am looking for a way to pull data (ie a specific state's financials) from
> >a
> > huge report that I have. Need a drop tab or something so when I click on
> > State abbrevation it pulls only that states financials from the bigger
> > report? How do I do this???
>
>
>

[vikesh]

You may use Pivot tables

[JulieD]

Hi

check out
http://www.j-walk.com/ss/excel/usertips/tip068.htm
to see if this will give you a structure you can then use with autofilter /
pivot tables etc

Cheers
JulieD

"nbeaird_51" <[email protected]> wrote in message
news:[email protected]...
>I have tried this the data is posted in a way that the state is a header
>and
> then it goes into different products we offer going down a column. The
> rows
> that grow accross are the financials from years past up till the current
> year
> Example:
> State 1-03 2-03 3-03 4-03.....
> Product $$$$$$$$$$$$$$$$.....
> Product $$$$$$$$$$$$$$$....
> Product $$$$$$$$$$$$$$$.....
>
> It does this fore every state and I want to be able to viewan individual
> state from this bigger list to ease data entry and printing
>
> ANY HELP APPRECIATED!!! Thanks and Happy Early St Patty's
> "JulieD" wrote:
>
>> Hi
>>
>> does data / filter / autofilter give you what you need?
>> if not, how is your data structured?
>>
>> Cheers
>> JulieD
>>
>> "nbeaird_51" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am looking for a way to pull data (ie a specific state's financials)
>> >from
>> >a
>> > huge report that I have. Need a drop tab or something so when I click
>> > on
>> > State abbrevation it pulls only that states financials from the bigger
>> > report? How do I do this???
>>
>>
>>

[nbeaird_51]

Hey
That did not work I have the states as a header in yellow background and
when i did the pivot table it did not show the states it pretty much just put
the years in alphabetical order. This is really bugging me, any other
assistance will be helpful.

Nate

"JulieD" wrote:

> Hi
>
> check out
> http://www.j-walk.com/ss/excel/usertips/tip068.htm
> to see if this will give you a structure you can then use with autofilter /
> pivot tables etc
>
> Cheers
> JulieD
>
> "nbeaird_51" <[email protected]> wrote in message
> news:[email protected]...
> >I have tried this the data is posted in a way that the state is a header
> >and
> > then it goes into different products we offer going down a column. The
> > rows
> > that grow accross are the financials from years past up till the current
> > year
> > Example:
> > State 1-03 2-03 3-03 4-03.....
> > Product $$$$$$$$$$$$$$$$.....
> > Product $$$$$$$$$$$$$$$....
> > Product $$$$$$$$$$$$$$$.....
> >
> > It does this fore every state and I want to be able to viewan individual
> > state from this bigger list to ease data entry and printing
> >
> > ANY HELP APPRECIATED!!! Thanks and Happy Early St Patty's
> > "JulieD" wrote:
> >
> >> Hi
> >>
> >> does data / filter / autofilter give you what you need?
> >> if not, how is your data structured?
> >>
> >> Cheers
> >> JulieD
> >>
> >> "nbeaird_51" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I am looking for a way to pull data (ie a specific state's financials)
> >> >from
> >> >a
> >> > huge report that I have. Need a drop tab or something so when I click
> >> > on
> >> > State abbrevation it pulls only that states financials from the bigger
> >> > report? How do I do this???
> >>
> >>
> >>
>
>
>

[JulieD]

Hi Nate

the only other idea i have is resort to code to extract the data you want
.... but i'm guessing the number of products is different for each state - so
the first state's products would be say rows 2 - 50, the second rows 3 - 100
.... or do each of your states sell the same products ??

another idea - at the end of each state's products do you have a total line?
if so, data / group & outline / auto outline might give you what you're
looking for (or you could outline each state manually and then hide &
display the states as required).

Cheers
JulieD

"nbeaird_51" <[email protected]> wrote in message
news:[email protected]...
> Hey
> That did not work I have the states as a header in yellow background and
> when i did the pivot table it did not show the states it pretty much just
> put
> the years in alphabetical order. This is really bugging me, any other
> assistance will be helpful.
>
> Nate
>
> "JulieD" wrote:
>
>> Hi
>>
>> check out
>> http://www.j-walk.com/ss/excel/usertips/tip068.htm
>> to see if this will give you a structure you can then use with autofilter
>> /
>> pivot tables etc
>>
>> Cheers
>> JulieD
>>
>> "nbeaird_51" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have tried this the data is posted in a way that the state is a header
>> >and
>> > then it goes into different products we offer going down a column. The
>> > rows
>> > that grow accross are the financials from years past up till the
>> > current
>> > year
>> > Example:
>> > State 1-03 2-03 3-03 4-03.....
>> > Product $$$$$$$$$$$$$$$$.....
>> > Product $$$$$$$$$$$$$$$....
>> > Product $$$$$$$$$$$$$$$.....
>> >
>> > It does this fore every state and I want to be able to viewan
>> > individual
>> > state from this bigger list to ease data entry and printing
>> >
>> > ANY HELP APPRECIATED!!! Thanks and Happy Early St Patty's
>> > "JulieD" wrote:
>> >
>> >> Hi
>> >>
>> >> does data / filter / autofilter give you what you need?
>> >> if not, how is your data structured?
>> >>
>> >> Cheers
>> >> JulieD
>> >>
>> >> "nbeaird_51" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >I am looking for a way to pull data (ie a specific state's
>> >> >financials)
>> >> >from
>> >> >a
>> >> > huge report that I have. Need a drop tab or something so when I
>> >> > click
>> >> > on
>> >> > State abbrevation it pulls only that states financials from the
>> >> > bigger
>> >> > report? How do I do this???
>> >>
>> >>
>> >>
>>
>>
>>

[RGC]

I think this may work for you:

This assumes that the report has some structure to the heading info, for instance, a line that begins with "Company: ZYZ"

1)Insert a column to the left of the report (I'll assume column A) and a row above (Row 1)
2)Run this forumula down column A (beginning in Row2):
=if(left(B2,7)="Company",Mid(b2,9,40),A1)

That way, Column A will list the same company for all rows in that company until the next company header appears. Then the new company will display.

Regards,

Ron C