VLOOKUP's Across Multiple Sheets

  • Thread starter Thread starter Nathan Horrigan
  • Start date Start date
N

Nathan Horrigan

Can anyone tell me if it is possible to do a 'VLookup'
formula across multiple worksheets in the same
spreadsheet? Appreciate any suggestions.
 
As far as I know, no. Excel's lookups depend on a worksheet being a true 2d
space. However, the sheets of a workbook do not form a true third dimension
as they are unordered so Excel's lookups are very limited in the third
dimension.

If you give a better idea of what your data looks like and what you need to
accomplish, perhaps we can find a solution.

As an aside, I remember playing with a spreadsheet over ten years ago called
Qubecalc which was a true 3d spreadsheet. I don't know if it still exists.

Dave
 
As far as I know, no. Excel's lookups depend on a worksheet being a true 2d
space. However, the sheets of a workbook do not form a true third dimension
as they are unordered so Excel's lookups are very limited in the third
dimension.
Click to expand...
...

Downloading and installing Laurent Longre's MOREFUNC.XLL add-in, available from

http://longre.free.fr/english/

provides a function named THREED which could be used to allow 3D ranges as
tables (2nd arguments) in VLOOKUP *IF* the first column in all worksheets were a
single logical lookup column, and all other columns were each a single logical
value column. For example, given

worksheet A
-----------
A 1 10
B 2 20
C 3 30

worksheet B
-----------
X 9 99
Y 8 88
Z 7 77

Then if (pseudoformula) =VLOOKUP("X",'A:B'!A1:C3,3,0) should return 99, then
use THREED as follows.

=VLOOKUP("X",THREED('A:B'!A1:C3),3,0)
As an aside, I remember playing with a spreadsheet over ten years ago called
Qubecalc which was a true 3d spreadsheet. I don't know if it still exists.
Click to expand...

Maybe not, but 123, Quattro Pro and Xess for Windows as well as several
Unix/Linux-only spreadsheets such as Applix are true 3D right now. It's also
possible that the spreadsheets in several of the more ambitious shareware
Windows suites are 3D, but I have no first-hand experience with them. The choice
isn't limited to fuzzily recalled decade-old products.
 
Harlan Grove said:
...
..


Maybe not, but 123, Quattro Pro and Xess for Windows as well as several
Unix/Linux-only spreadsheets such as Applix are true 3D right now. It's also
possible that the spreadsheets in several of the more ambitious shareware
Windows suites are 3D, but I have no first-hand experience with them. The choice
isn't limited to fuzzily recalled decade-old products.
Click to expand...

Can you transpose axis?
 
The spreadsheet I am working on has 13 worksheets, made up
largely of each sheet containg workorders completed during
a month. What I want to do is search on the last sheet
which contains each of the devices, and lookup which month
(with details from the month worksheet) they were
completed.

Appreciate any advise!
 
Can you transpose axis?
Click to expand...

If by 'axis' you mean along the worksheet 'dimension', then yes for 123, no for
Quattro Pro, don't have the others on this machine to test them. So drop QP from
the list of 3D spreadsheets narrowly defined, make it definite for 123, and
leave the others as maybes.
 
I downloaded the MOREFUNC.XLL add-in for the THREED function yo
described above and it works great!! You just added another dimensio
to my productivity as I eat VLOOKUPS for breakfast then all day lon
after that. Thanks for the tip
 
Back
Top