Lookup info in support sheets and enter in summary sheet

I have a workbook with a summary sheet and 7 supporting sheets. I need a
macro that will take an account number from the summary sheet, find where it
is in one of 6 of the supporting tabs and return the amount corresponding to
that account number to my summary sheet and put it in the specified column.
Each account will occur only once in the 6 tabs, which means that each
account occurs in 1 of the tabs and not in the other 5. Ideas?? Thanks!

See this:

http://tinyurl.com/q3x4w

Biff

"WendiL99" <[email protected]> wrote in message
news:[email protected]...
>I have a workbook with a summary sheet and 7 supporting sheets. I need a
> macro that will take an account number from the summary sheet, find where
> it
> is in one of 6 of the supporting tabs and return the amount corresponding
> to
> that account number to my summary sheet and put it in the specified
> column.
> Each account will occur only once in the 6 tabs, which means that each
> account occurs in 1 of the tabs and not in the other 5. Ideas?? Thanks!

The array formula only worked for the first worksheet in my defined list.
What am I doing wrong? My formula is below.

=VLOOKUP(A4,INDIRECT("'"&INDEX(Tablist,MATCH(TRUE,COUNTIF(INDIRECT("'"&Tablist&"'!A1:B100"),A4)>0,0))&"'!A1:B1000"),2,0)
"Biff" wrote:

> See this:
>
> http://tinyurl.com/q3x4w
>
> Biff
>
> "WendiL99" <[email protected]> wrote in message
> news:[email protected]...
> >I have a workbook with a summary sheet and 7 supporting sheets. I need a
> > macro that will take an account number from the summary sheet, find where
> > it
> > is in one of 6 of the supporting tabs and return the amount corresponding
> > to
> > that account number to my summary sheet and put it in the specified
> > column.
> > Each account will occur only once in the 6 tabs, which means that each
> > account occurs in 1 of the tabs and not in the other 5. Ideas?? Thanks!
>
>
>

You're formula works just fine on my end.

Is this a typo:

A1:B100")...........A1:B1000")

Both ranges need to be the same or you could get incorrect results.

Biff

"WendiL99" <[email protected]> wrote in message
news:[email protected]...
> The array formula only worked for the first worksheet in my defined list.
> What am I doing wrong? My formula is below.
>
> =VLOOKUP(A4,INDIRECT("'"&INDEX(Tablist,MATCH(TRUE,COUNTIF(INDIRECT("'"&Tablist&"'!A1:B100"),A4)>0,0))&"'!A1:B1000"),2,0)
> "Biff" wrote:
>
>> See this:
>>
>> http://tinyurl.com/q3x4w
>>
>> Biff
>>
>> "WendiL99" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a workbook with a summary sheet and 7 supporting sheets. I need
>> >a
>> > macro that will take an account number from the summary sheet, find
>> > where
>> > it
>> > is in one of 6 of the supporting tabs and return the amount
>> > corresponding
>> > to
>> > that account number to my summary sheet and put it in the specified
>> > column.
>> > Each account will occur only once in the 6 tabs, which means that each
>> > account occurs in 1 of the tabs and not in the other 5. Ideas??
>> > Thanks!
>>
>>
>>

I got the formula working ok, but it returns #N/A if there is no match in the
supporting sheets. How can I make this be a zero instead of #N/A?

"Biff" wrote:

> You're formula works just fine on my end.
>
> Is this a typo:
>
> A1:B100")...........A1:B1000")
>
> Both ranges need to be the same or you could get incorrect results.
>
> Biff
>
> "WendiL99" <[email protected]> wrote in message
> news:[email protected]...
> > The array formula only worked for the first worksheet in my defined list.
> > What am I doing wrong? My formula is below.
> >
> > =VLOOKUP(A4,INDIRECT("'"&INDEX(Tablist,MATCH(TRUE,COUNTIF(INDIRECT("'"&Tablist&"'!A1:B100"),A4)>0,0))&"'!A1:B1000"),2,0)
> > "Biff" wrote:
> >
> >> See this:
> >>
> >> http://tinyurl.com/q3x4w
> >>
> >> Biff
> >>
> >> "WendiL99" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have a workbook with a summary sheet and 7 supporting sheets. I need
> >> >a
> >> > macro that will take an account number from the summary sheet, find
> >> > where
> >> > it
> >> > is in one of 6 of the supporting tabs and return the amount
> >> > corresponding
> >> > to
> >> > that account number to my summary sheet and put it in the specified
> >> > column.
> >> > Each account will occur only once in the 6 tabs, which means that each
> >> > account occurs in 1 of the tabs and not in the other 5. Ideas??
> >> > Thanks!
> >>
> >>
> >>
>
>
>

I got the formula working ok, but it returns #N/A if there is no match in the
supporting sheets. How can I make this be a zero instead of #N/A?

"Biff" wrote:

> You're formula works just fine on my end.
>
> Is this a typo:
>
> A1:B100")...........A1:B1000")
>
> Both ranges need to be the same or you could get incorrect results.
>
> Biff
>
> "WendiL99" <[email protected]> wrote in message
> news:[email protected]...
> > The array formula only worked for the first worksheet in my defined list.
> > What am I doing wrong? My formula is below.
> >
> > =VLOOKUP(A4,INDIRECT("'"&INDEX(Tablist,MATCH(TRUE,COUNTIF(INDIRECT("'"&Tablist&"'!A1:B100"),A4)>0,0))&"'!A1:B1000"),2,0)
> > "Biff" wrote:
> >
> >> See this:
> >>
> >> http://tinyurl.com/q3x4w
> >>
> >> Biff
> >>
> >> "WendiL99" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have a workbook with a summary sheet and 7 supporting sheets. I need
> >> >a
> >> > macro that will take an account number from the summary sheet, find
> >> > where
> >> > it
> >> > is in one of 6 of the supporting tabs and return the amount
> >> > corresponding
> >> > to
> >> > that account number to my summary sheet and put it in the specified
> >> > column.
> >> > Each account will occur only once in the 6 tabs, which means that each
> >> > account occurs in 1 of the tabs and not in the other 5. Ideas??
> >> > Thanks!
> >>
> >>
> >>
>
>
>

I got the formula working ok, but it returns #N/A if there is no match in the
supporting sheets. How can I make this be a zero instead of #N/A?

"Biff" wrote:

> You're formula works just fine on my end.
>
> Is this a typo:
>
> A1:B100")...........A1:B1000")
>
> Both ranges need to be the same or you could get incorrect results.
>
> Biff
>
> "WendiL99" <[email protected]> wrote in message
> news:[email protected]...
> > The array formula only worked for the first worksheet in my defined list.
> > What am I doing wrong? My formula is below.
> >
> > =VLOOKUP(A4,INDIRECT("'"&INDEX(Tablist,MATCH(TRUE,COUNTIF(INDIRECT("'"&Tablist&"'!A1:B100"),A4)>0,0))&"'!A1:B1000"),2,0)
> > "Biff" wrote:
> >
> >> See this:
> >>
> >> http://tinyurl.com/q3x4w
> >>
> >> Biff
> >>
> >> "WendiL99" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have a workbook with a summary sheet and 7 supporting sheets. I need
> >> >a
> >> > macro that will take an account number from the summary sheet, find
> >> > where
> >> > it
> >> > is in one of 6 of the supporting tabs and return the amount
> >> > corresponding
> >> > to
> >> > that account number to my summary sheet and put it in the specified
> >> > column.
> >> > Each account will occur only once in the 6 tabs, which means that each
> >> > account occurs in 1 of the tabs and not in the other 5. Ideas??
> >> > Thanks!
> >>
> >>
> >>
>
>
>