Science & Industry

='Another Sheet'!C3

Will show you the value of the cell C3 in the sheet called Another Sheet.

If you've structured your capmins table like I think you have (i.e. poorly), then you're kinda boned.

Look at the VLOOKUP function. It's short for Vertical Lookup. You give it something to find, a place to look, then a column number to return, and it'll fetch that for you. There's a sister-function called HLOOKUP. No points awarded for guessing what the H stands for.

VLOOKUP(lookup_value, table_array, column_index_number, [range_lookup])

It will do what you want, but you'll have to restructure your capmins sheet like so;

        Trit    Pyre    Mex     etc
Rev     452341  23103   9865
Phoe    6854312 365413  8973
Nag     6561    35132   6555
etc

This is perfect for VLOOKUP now.

=VLOOKUP('Another Sheet'!C3, 'capmins'!A:G, 2, FALSE)

Will get you the amount of Trit needed to build whatever is specified in C3.

VLOOKUP only lets you select a column by number, not by a word or label. If you want to get really clever, you can get around this limitation by sneaking in a dummy line like so;

        Trit    Pyre    Mex     etc
COLUMNS 2       3       4       5
Rev     452341  23103   9865
Phoe    6854312 365413  8973
Nag     6561    35132   6555
etc            

Then use

=VLOOKUP('Another Sheet'!C3, 'capmins'!A:G, HLOOKUP('Another Sheet'!C4, 'capmins'!1:2, 2, FALSE), FALSE)

DISCLAIMER: There's a clear downside of this (compared to your filter method) is that it's very strict on what material types it supports. That is to say, you have to have an entire column for each material type. Fortunately, capitals are pretty good in this regard as they only require asteroid minerals... of which there is only seven that are relevant.

[PS] Always select FALSE for range_lookup. I'll save you the long and complicated explanation.

[PS] [EDIT] Both VLOOKUP and HLOOKUP only look in the first column (or row), and only return cells to the right (or down). Should have mentioned that sooner, because it's why you can't use it on your current table.

VLOOKUP is the simple way to do this, but as noted you need the columns set up in a certain order to use it. The more complex but less limited method is to use INDEX to identify an array and then MATCH to filter multiple columns in it.

=INDEX(Test2!$I$5:$U$325,MATCH(1,(Test2!$L$5:$L$325=C4)*(Test2!$R$5:$R$325=0),0),1)

Here I'm going to a sheet called Test2, referencing columns I to U, looking up an ID from cell C4 in column L that has the value 0 in column R, returning the value in column I.

In this case Test2 has my downloaded Market Order data and I'm looking for how much remains in a Sell Order.

Look up INDEX MATCH and you'll find plenty of guides to using it.