Use Of Vllok up to Consolidate the DATA

[Balendra Kumar]

Hi

I am using the Following formula to Consolidate the Data form three Sheets

=IF(ISERROR(VLOOKUP($A6,'Sheet1'!$B:$AT,COLUMN(AR6),FALSE)),0,VLOOKUP($A6,'Sheet1'!$B:$AT,COLUMN(AR6),FALSE)+IF(ISERROR(VLOOKUP($A6,'Sheet2'!$B:$AT,COLUMN(AR6),FALSE)),0,VLOOKUP($A6,'Sheet2'!$B:$AT,COLUMN(AR6),FALSE)))+IF(ISERROR(VLOOKUP($A6,'Sheet3'!$B:$AT,COLUMN(AR6),FALSE)),0,VLOOKUP($A6,'Sheet3'!$B:$AT,COLUMN(AR6),FALSE))

This Formula is Working file till Columns AQ but after that it Stops Functioning

I am getting The Data from the Pivot tables on the Sheet 1 sheet 2 and Sheet 3

Regards
Balendra Kumar

 

[Andrew Poulsom]

What do you mean it stops functioning? The range B:AT has 45 columns, so a column index of 44 (AR) should be fine.

 

[Balendra Kumar]

yes this is What I have faced How Can a formula that Works fine from Colums C to Column AQ suddenly stop functioning

 

[Andrew Poulsom]

You haven't explained what you mean by stops functioning.

 

[Balendra Kumar]

I have three Pivot table to evaluate the Performance of the Sales person these Person did their Work at three Different Levels .Any one of them can function at any of the Levels

Thus to Consolidate What a Sales person did at a particular Level I have three Pivot Tables
For Each Levels

Then I have made use of the above formula to calculate the Consolidate Each Individual Performance at all the Level the worked

This was a Week wise data for 15 month for 75 Individual. And the Last Column was not working

To verify the Formula I reduced the size of my Data and have taken the Detail since June 2008

Still only the Last Column is not working

It is surprising to me

Balendra