------ Register to get rid of these "In Post" ads! ------


Thanks for the feedback
I had a safe 1st as I'm a Canadian
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"BruceG" <BruceG@discussions.microsoft.com> wrote in message
news:892E07FE-A911-4E8D-A9BD-536982C38D0B@microsoft.com...
> That dud it.
>
> Thank you, Bernard. And yes, I used to use 123.
>
> Everyone, have a safe 4th.
>
> Bruce
>
> "Bernard Liengme" wrote:
>
>> You can use the INDIRECT function
>>
>> In A1:B 20 have
>> A 10
>> B 12
>> C 14
>> D 16
>> E 18
>> etc.....
>>
>> In E1:G1 I have
>> A1:B10 C 14
>> The formula in G1 is =VLOOKUP(F1,INDIRECT(E1),2)
>> This is equivalent to =VLOOKUP(F1,A1:B10,2) and returns the value 14
>>
>> So it looks like you need to use =vlookup(j51,INDIRECT(p51),2)
>>
>> Please note that none of the plus signs (+) in any of your formulas are
'Original Source: The Code Cage Forums http://www.thecodecage.com/forumz/worksheet-functions/112640-formula-range-area-within-vlookup.html#post403993
>> needed.
>> Did you begin life a Lotus-123 users where + was needed?
>>
>> best wishes
>> --
>> Bernard Liengme
>> Microsoft Excel MVP
>> http://people.stfx.ca/bliengme
>>
>> "BruceG" <BruceG@discussions.microsoft.com> wrote in message
>> news:E05BAA7C-ECFF-4BB3-BF7A-69EF8813E373@microsoft.com...
>> > Is it possible to use a formula referenced by a cell as the range area
>> > within
>> > Vlookup?
>> >
>> > I am trying to do the following vlookup -
>> >
>> > vlookup(j51,+p51,2)
>> > j51 is a valid receipt number I am trying to look up for an invenotry
>> > item.
>> >
>> > +p51 is a formula of other vlookup concatenated together to form a
>> > range
>> > value -
>> > it looks like AE$4:AH$30 done by the formula
>> > =+"AE$"&+VLOOKUP(C51,B$8:G$22,5)&+":"&+("AH$"&+VLOOKUP(C51,B$8:G$22,6))
>> > This
>> > define the specific area of a particular inventory item and where all
>> > the
>> > recepits. Each item has a different area and no set row amount.
>> >
>> > For exxample Item ABC will be in rows 4-30 whereas CDE would be in rows
>> > 31-91. The receipts nuumber will alwats be in column AE of the receipt
>> > table
>> > but the receipt # repeat for different items. Meaning Receipts #123 is
>> > in
>> > both ABC & CDE.Which is why I was defining the item area first, then
>> > focusing
>> > on receipt number.
>> >
>> > 2 is the column number in the rea of AE - AH.
>> >
>> > The purpose of this lookup is to bring in a receipt # date so I can
>> > comppare
>> > it against a sold date to find out how long an item stood arond in
>> > stock.
>> >
>> > Is this possible or is there a better way to do this?
>> >
>> > Thank you in advance for any help.
>> >
>> > Bruce
>> >
>> >
>> >
>>
>>