vlookup macro...

[alenhart]

I am a little rusty with the creation of macros and hope someone
might
be able to give me a hand.

I have a sheet with 45 of so tabs of data and i need to roll the data
into one summary sheet at the beginning of the workbook.
For example, I have the account code "5010" that exists in column B
in each of my sheets. then column L on the same row shows the number
that i need for the summary. so in the summary sheet i know to type
in
=VLOOKUP(5010,'110'!B8:L37,9,FALSE)
I also know how to manually compile these using the same formula over
and over again, to complete the full workbook of data
example:
=VLOOKUP(5010,'110'!B8:L37,9,FALSE)+VLOOKUP(5010,'210'!
B8:L28,9,FALSE)
+VLOOKUP(5010,'220'!B8:L37,9,FALSE)

what i would like to be able to do, however, is the someway create a
marco that would run through each sheet and get the 5010 value from
all 45 sheets without having to manually add them together with a
formula.


Thoughts?
Thank you
Anne

 

[Matthew Herbert]

I am a little rusty with the creation of macros and hope someone
might
be able to give me a hand.

I have a sheet with 45 of so tabs of data and i need to roll the data
into one summary sheet at the beginning of the workbook.
For example,  I have the account code "5010" that exists in column B
in each of my sheets.  then column L on the same row shows the number
that i need for the summary. so in the summary sheet i know to type
in
=VLOOKUP(5010,'110'!B8:L37,9,FALSE)
I also know how to manually compile these using the same formula over
and over again, to complete the full workbook of data
example:
=VLOOKUP(5010,'110'!B8:L37,9,FALSE)+VLOOKUP(5010,'210'!
B8:L28,9,FALSE)
+VLOOKUP(5010,'220'!B8:L37,9,FALSE)

what i would like to be able to do, however, is the someway create a
marco that would run through each sheet and get the 5010 value from
all 45 sheets without having to manually add them together with a
formula.

Thoughts?
Thank you
Anne

Anne,

If your worksheets are evenly spaced (i.e. it seems that each
worksheet is the previous worksheet + 110), if the ranges are the same
size, and there is only one 5010 account number on each worksheet,
then you can set up an INDIRECT function rather than a macro. This
will be much more simple.

For example, do something like the following on a blank worksheet:

A1: 5010
B1: 110; B2: =B1+110; Fill Down
C1: B8:L37; C2: =C1; Fill Down
D1: ="'"&B1&"'!"&C1; D2: =D1; Fill Down
E1: =VLOOKUP($A$1,INDIRECT(D1),9,FALSE); E2: =E1; Fill Down

Best,

Matthew Herbert