In VLOOKUP is it possible to use cell values to make up a named range for the Table-array?

[PWD]

Good afternoon, I've inherited a workbook that has complex (to me) formulae that look up values based on Brands, Quarters & Countries. At the moment we have a block of formulae per Brand but this seems to really slow things down when the workbook recalculates. Here is an example of the formula in one of the cells:-

=IFERROR(VLOOKUP($A12, INDIRECT("'" & F$11 &  " - City'!" & VLOOKUP($C$6,RangeDataBRAND1City,2,FALSE)),MATCH($C$4,INDIRECT("'" & F$11 &  " - City'!" & "A1:Z1"),0),FALSE),"0")

"'" & F$11 & " - City'!" This is the sheet, for example, Quarter3 - City.

VLOOKUP($C$6,RangeDataBRAND1City,2,FALSE) This is what I'd like to be more flexible; it identifies the row references of the Table-array.

The 'MATCH' finds the Column number for the appropriate country.

What I'd like to be able to do is just have one block of formulae and use the value of a cell to substitute for the hard-coded "BRAND1", BRAND2" etc.

So that

RangeDataBRAND1City

Becomes something like,

RangeData & "C5" & City
or
RangeData & CityQBrand & City
If CityQBrand were the name assigned to "C5".

Is this possible?

Many thanks,
D€$

 

[Hoaokapohaku]

RangeData" & C5 & "City

 

[Hoaokapohaku]

Oooops!...The above will give you a text string with the right name, but Excel won't interpret the text string as a range reference so it won't work in VLOOKUP.

 

[SkipVought]

Range("RangedataBrand1City")

BUT!!!!

I question why you use such Range Names???

You do not seem to understand relationsl table concepts. If you have a table that contains a Brand field in which are recorded multiple brands, that range is all you need for brand.

Maybe a sample of you table would help illustrate what you need to do.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PWD]

Hi Skip, here's the sort of table we have:-

UK USA Canada
Base 3026 3000 1803
Brand1
Unprompted Brand Awareness (First Mention)
City/Region 1 0 4% 1%
City/Region 2 37% 36% 37%
City/Region 3 25% 12% 29%
City/Region 4 8% 6% 3%
City/Region 5 1% 4% 4%
City/Region 6 0 4% 1%
City/Region 7 6% 1% 2%
City/Region 8 9% 9% 8%
City/Region 9 2% 0 1%
City/Region 10 0 0 0
Brand1
Unprompted Brand Awareness (All Mentions)
City/Region 1 3% 12% 11%
City/Region 2 64% 57% 66%
City/Region 3 53% 31% 59%
City/Region 4 32% 18% 14%
City/Region 5 11% 16% 15%
City/Region 6 5% 17% 10%
City/Region 7 30% 7% 10%
City/Region 8 42% 30% 35%
City/Region 9 14% 4% 6%
City/Region 10 2% 2% 1%
Brand1
Prompted Brand Awareness
City/Region 1 92% 98% 91%
City/Region 2 99% 98% 98%
City/Region 3 99% 99% 99%
City/Region 4 87% 82% 74%
City/Region 5 95% 97% 94%
City/Region 6 98% 98% 96%
City/Region 7 99% 97% 95%
City/Region 8 99% 98% 96%
City/Region 9 99% 92% 90%
City/Region 10 88% 87% 80%

Brand2
Unprompted Brand Awareness (First Mention)
City/Region 1 0 4%
City/Region 2 37% 36%
City/Region 3 25% 12%
City/Region 4 8% 6%
City/Region 5 1% 4%
City/Region 6 0 4%
City/Region 7 6% 1%
City/Region 8 9% 9%
City/Region 9 2% 0
City/Region 10 0 0
Brand2
Unprompted Brand Awareness (All Mentions)
City/Region 1 3% 12%
City/Region 2 64% 57%
City/Region 3 53% 31%
City/Region 4 32% 18%
City/Region 5 11% 16%
City/Region 6 5% 17%
City/Region 7 30% 7%
City/Region 8 42% 30%
City/Region 9 14% 4%
City/Region 10 2% 2%
Brand2
Prompted Brand Awareness
City/Region 1 92% 98%
City/Region 2 99% 98%
City/Region 3 99% 99%
City/Region 4 87% 82%
City/Region 5 95% 97%
City/Region 6 98% 98%
City/Region 7 99% 97%
City/Region 8 99% 98%
City/Region 9 99% 92%
City/Region 10 88% 87%

And what the

Range("RangedataBrand1City")

does is to determine the row numbers of the above data where to carry out the VLOOKUP. "City/Region 1" etc. is exactly how the data comes through as there are a variable number of regions per country.

So RangedataBrand1City looks like this:-

Top of Mind A5:AB14
Spontaneous Awareness A17:AB26
Prompted Awareness A29:AB38

Not sure if this clarifies or muddies the water more

So in order to create the charts the workbook has 'chart areas' based on each brand. What I'd like to have would be a generic 'chart area' where the formula could include the value of C5.

Many thanks,
D€$

 

[SkipVought]

I cannot perform a VLOOKUP on a UNION of ranges!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PWD]

OK, thanks; so it can't be done.

Many thanks,
D€$

 

[SkipVought]

This is a VERY non-standard design.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PWD]

It sure is.

Being as I have the T-shirt "Too Stupid to Quit" I found that this will do what I want and saves me having to have 5 different data areas for the chart for the 5 brands; I hope this will reduce the time it takes the workbook to calculate - probably not significantly though!

=IFERROR(VLOOKUP($A12, INDIRECT("'" & N$11 &  " - City'!" & VLOOKUP($C$6,INDIRECT(IF($C$5="Brand1","RangeDataBrand1City",IF($C$5="Brand2","RangeDataBrand2City",IF($C$5="BRAND3","RangeDataBRAND3City",IF($C$5="Brand4","RangeDataBrand4City",IF($C$5="Brand5","RangeDataBrand5City",)))))),2,FALSE)),MATCH($C$4,INDIRECT("'" & N$11 &  " - City'!" & "A1:Z1"),0),FALSE),"0")

Many thanks,
D€$

 

[SkipVought]

You ought to have ONE Brands colunmn for all your brands and ONE City/Region column for all your ciry/regions.

Take the time now to NORMALIZE your data, and your data analysis and reporting will be so much simpler in the future.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PWD]

Thanks Skip. I have to accept whatever data 'they' send me, unfortunately. It does seem overly complex as this is market research data. So there's a new sample every month, resulting in data in a new sheet. Furthermore each brand has data for each City/Region provided for each question. So each brand has 10 rows for the maximum number of regions for each of the 5 types of answer, resulting in 50 rows of data x 30 columns of countries, per brand. What I had then was 5 ranges of data for potential use in a chart. What I have now managed to do is only have one 'chart-data' area, based on the INDIRECT(IF.

Thanks for your assistance.

Many thanks,
D€$

 

[zelgar]

Don't know if this will help, but this macro will somewhat "normalize" your code based upon your example data above

Sub adjust()
Start = 3
last = Application.WorksheetFunction.CountA(Range("sheet1!C:C")) + 1
    Columns("A:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1:B2").Select
    Selection.Delete Shift:=xlToLeft
For x = Start To last
    If Left(Range("C" & Trim(Str(x))), 5) = "Brand" Then
        b$ = Range("C" & Trim(Str(x)))
        c$ = Range("C" & Trim(Str(x + 1)))
        For y = x + 2 To last
            If Left(Range("C" & Trim(Str(y))), 5) = "Brand" Then
                Range("a" & Trim(Str(x)) & ":a" & Trim(Str(y - 1))) = b$
                Range("b" & Trim(Str(x)) & ":b" & Trim(Str(y - 1))) = c$
                Rows(Trim(Str(x)) & ":" & Trim(Str(x + 1))).Delete
            End If
            If y = last Then
                Range("a" & Trim(Str(x)) & ":a" & Trim(Str(y))) = b$
                Range("b" & Trim(Str(x)) & ":b" & Trim(Str(y))) = c$
                Rows(Trim(Str(x)) & ":" & Trim(Str(x + 1))).Delete
            End If
        Next y
    End If
Next x
w = 0
For Z = Start To last
    If Range("C" & Trim(Str(Z))) = "" And w < 20 Then
        Rows(Trim(Str(Z)) & ":" & Trim(Str(Z))).Delete
        Z = Z - 1
        w = w + 1
    End If
Next Z
End Sub

 

[zelgar]

noticed a few errors in my code.....

this one is better:

Sub adjust()
Start = 3
Columns("A:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
last = Application.WorksheetFunction.CountA(Range("sheet1!C:C")) + 1
Range("A1:B2").Select
Selection.Delete Shift:=xlToLeft
For x = Start To last
If Left(Range("C" & Trim(Str(x))), 5) = "Brand" Then
b$ = Range("C" & Trim(Str(x)))
c$ = Range("C" & Trim(Str(x + 1)))
Range("a" & Trim(Str(x)) & ":a" & Trim(Str(last))) = b$
Range("b" & Trim(Str(x)) & ":b" & Trim(Str(last))) = c$
Rows(Trim(Str(x)) & ":" & Trim(Str(x + 1))).Delete
End If
Next x
w = 0
For Z = Start To last
If Range("C" & Trim(Str(Z))) = "" And w < 20 Then
Rows(Trim(Str(Z)) & ":" & Trim(Str(Z))).Delete
Z = Z - 1
w = w + 1
End If
Next Z
End Sub