Using a Text Value as Range by Indirect Function in a Vlookup to get a corresponding value

[e4excel]

Using a Text Value as Range by Indirect Function in a Vlookup to get a corresponding value

Dear Forum,

I amd trying to use labels as Range by usig the Indirect Function however that is not possible, so can someone please suggest something,

Example - I have 2 Seperate Ranges Executive_Rng and Team_Leader_Rng.

Also I have made labels using the TRUE part of VLOOKUP to display a text value which needs to be used as a Named Rnage..

Please refer the attachment, if I make the Executive_Rng a hard-coded range then I can use it in the formula but since I have a dynamic formula it cannot be used..

So please suugest a solution..

Regards
e4excel..

[JohnTopley]

I am not clear what you are trying to achieve here: it appears unnecessarily complex.

[e4excel]

Dear John,

I have 2 Defined Name Ranges for each Designation/Role , I wam going to do the Payout for these employees, since the format is same I can put them one below the other.
Everytime rather than copying, I have made these Ranges making use of the Sr No ..

I want to simply use 2 Intertwined VLookups as mentioned below
Considering ROW()=1

''' Thiis is the First Basic Lookup, now instead of typing Executive_Rng I have used the VLOOKUP with TRUE Style to derive the name Execute_Rng ( from Sr No- 1 to 4 ) and Team_Leader_Rng ( Sr No 5 - 8 )

Now if I use these 2VLOOKUPS together it works fine when my Executive_Rng is a hard-coded range but when its a dynamic range which is made using the formula then this fails as shown in my attachment.

Hope this clarifies my issue.

[JohnTopley]

In O3

=VLOOKUP(ROWS($1:1),IF(ROWS($1:1)<=4,INDIRECT($M$3),INDIRECT($M$4)),O$1,0)

The named ranges in the above are fixed as you cannot use dynamic ranges with INDIRECT.

However, I still believe you are making things complex when it should be simple with no obvious benefit.. (Adopt "KISS" principle)

[Bo_Ry]

INDIRECT doesn't work with dynamic range name


Please try with non Volatile formula at O14
=VLOOKUP(ROWS(O$14:O14),CHOOSE(1+(ROWS(O$14:O14)>MAX($A$3:$A$20)),$A$3:$D$20,$F$3:$I$20),COLUMNS($O14:O14)+1,0)

[e4excel]

Dear John and BO_RY,

I have only shown just 2 Defined Names for explaining the problem statement however in reality I have several Defined Names for differerent hierarchies as well as the Number of Rows is also different for each hierarchy and therefore I am using DynamiC Defined Names.

My expectation was to get to use the Dynamic Range which is coming out of the VLOOKUP TRUE Formula so that it takes the correct range each time.

Your solutions are nonetheless good but i have just shared enough data to explain...

[JohnTopley]

I still do see why cannot have one table as you have a field which identifies the "Type" of personnel ("Executive", "team Leader") - keep it simple!

[e4excel]

Actually, as an example i just showed plain data as Ranges but in reality i have different Pivot Tables made for different hierarchies as Data Range which keep on changing the rows based on Pivot Table Height, i have just added the Sr No in the Rght next to each Pivot Table over here for explanation I showed in this manner..

Maybe if its not possible. then...cant use Dynamic Ranges and their names in INDIRECT Function..

[e4excel]

Dear Experts,

So will it be impossible to have it the way I have explained , as I need just one formula which can dynamically refer different Ranges accordingly..
Please confirm if I am missing anything in this..

[JohnTopley]

Do not get fixated on having different ranges.

Perhaps post a file which is more representative of your situation.

[e4excel]

Dear John,

I had shared a file which was a representation of my data, i just showed the data instead of Pivot Tables but if you feel I will try sharing another file.
But I am not fixated to have a range but if that were possible it would have been a boon for me as I simply drag the same formula and as per the ROW NO the formula would have automatically selected the correct RANGE.

Regards

[JohnTopley]

... but not necessary with a single table: and if the "lookup" is on "Employee number" it is academic which "group" anyone belongs to.

Simple tables lead to simple solutions!

[e4excel]

I have seperate Tables its just that I am fetching the final data in a Single Table in the order of the Designations i.e Executive, Team leader, Assistant Manager, manager, Sr manager, etc so on..

I have several such Pivot Tables next to each other and each Pivot Table has a Sr No ...
Example if the last Sr No for Executive ends at 100 then Team Leader begins at 101 and ends at 126 then Assistant Manager would begin at 127...

The Pivot Tables serve my purpose for accurate data but then in the Final Data which is shared with the Team i need to arrange the data one below the other hence I was trying to base it on Sr No and not the Emp ID...

if this works out then I can simple run the formula from Top To Bottom without bothering the Designation Range as it would automatically assign based on the Inner Vlookup but it fails as my Main Range_Name is a Dynamic Range so if there was a workaround it would have been great...

[JohnTopley]

I suspect the only option is to use VBA as we have established that dynamic ranges - [your variable sized Pivot tables] - cannot be used with INDIRECT.

And - are you really using Excel 2007? Can you update your profile please as answers do depend on knowing the Excel version you are using

[e4excel]

I have just got my Excel Version upgraded too 2016 this week.

[JohnTopley]

I don't know if this will work for you as it is not clear to me how the Pivot Table "fits" with the tables in your example.

I defined the 2 ranges as fixed ranges (you can them as large as required).

In L3

=COUNTIF(INDIRECT(M3),">0")/3 ... Divide by 3 as there 3 columns with numbers and COUNTIF returns Rows * Columns)

This counts the number of actual entries (rows) in the range ("pseudo" dynamic!)

in L4

=K4+(COUNTIF(INDIRECT(M4),">0")/3)-1

in O3

=VLOOKUP(ROWS($1:1),INDIRECT(INDEX($M$3:$M$4,MATCH(ROWS($1:1),$K$3:$K$4,1))),O$1,0)

Copy across and down

[e4excel]

Thanks John, but this is not what I am looking for but I am taking a leaf from your idea nonetheless so thanks for all your efforts..

[e4excel]

Its not SOLVED but if this is the ROADBLOCK then I will have to adopt a workaround.

[JohnTopley]

So what is the roadblock now? Why won't it work? Obviously there are elements of your "problem" which are not apparent in any of the information given to date.

You never explained why you are creating multiple Pivot tables: what is the "raw" data for these?