Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup macro...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup macro...
On Jul 1, 12:33*pm, alenhart wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP in a macro?? | Excel Programming | |||
VLOOKUP in a macro?? | Excel Discussion (Misc queries) | |||
Vlookup in a macro | Excel Programming | |||
VBA Macro for VLOOKUP | Excel Programming | |||
Please help.. VLookup Macro | Excel Programming |