# Use Of Vllok up to Consolidate the DATA

#### Balendra Kumar

##### Board Regular
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

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Andrew Poulsom

##### MrExcel MVP
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

##### Board Regular
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

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

#### Balendra Kumar

##### Board Regular
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

Replies
3
Views
284
Replies
1
Views
150
Replies
8
Views
176
Replies
6
Views
560
Replies
5
Views
120

1,191,124
Messages
5,984,784
Members
439,911
Latest member
dk73

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back