referencing a sheet tab name in vlookup

  • Thread starter Thread starter edwint
  • Start date Start date
E

edwint

Hi,

I've been using vlookup() function successfully for a long time, bu
want to expand it so I can refer to data on another tab by building th
name of the tab through a formula or another lookup.
In the example where I have revenue number for several countries, eac
country on it's own tab (all same format of data) and I want to build
'total' tab to add all country revenue numbers on this total tab.
I've looked through various forums and websites but no references t
it.

Any help out there?

-Edwi
 
Try INDIRECT() ?

Perhaps something along these lines to illustrate:

Assuming the tabs are named Country1, Country2, etc

In "Total" sheet
-------------------
Assume the summary set-up is something like:
(sample below in A1:C3)

RevNo....Tab.........Amt
1234...Country1.....500
1234...Country2....1000
etc

where the Amt col is to be extracted from the 2nd col in the table_array

Table_array is assumed to be range A2:B5 in *each* of the country tabs
where col A contains the RevNo (revenue numbers)

Put in C2: =VLOOKUP(A2,INDIRECT(B2&"!$A$2:$B$5"),2,0)

Copy C2 down

The above will return the equivalent of

In C2: =VLOOKUP(A2,Country1!$A$2:$B$5,2,0)
In C3: =VLOOKUP(A2,Country2!$A$2:$B$5,2,0)
etc
 
A more direct approach for some applications may be to apply a name to each
of the ranges to be 'vlookedup' (the name can be the same as the tab name).

Regards

Max said:
Try INDIRECT() ?

Perhaps something along these lines to illustrate:

Assuming the tabs are named Country1, Country2, etc

In "Total" sheet
-------------------
Assume the summary set-up is something like:
(sample below in A1:C3)

RevNo....Tab.........Amt
1234...Country1.....500
1234...Country2....1000
etc

where the Amt col is to be extracted from the 2nd col in the table_array

Table_array is assumed to be range A2:B5 in *each* of the country tabs
where col A contains the RevNo (revenue numbers)

Put in C2: =VLOOKUP(A2,INDIRECT(B2&"!$A$2:$B$5"),2,0)

Copy C2 down

The above will return the equivalent of

In C2: =VLOOKUP(A2,Country1!$A$2:$B$5,2,0)
In C3: =VLOOKUP(A2,Country2!$A$2:$B$5,2,0)
etc
Click to expand...
 
Back
Top