Bump up.

In Sheet1 of the workbook for which you provided a link, the following is to be seen:

{"Type", "", "Width", "", "Gauge", "", "Deflection", "", "Span", "", "Allowable Loads for Span";
"CSJ", "", 8, "", 16, "", 240, "", 16, "", ""}

Type appears to refer to the sheet CSJ (that's, a sheetname).
Width refers to which field in CSJ: "Length"?
Does Gauge refers to "Gauge" in CSJ?
Deflection refers presumably to "Allow Deflec."!
To which field in CSJ does Span refer?
To which field in CSJ does Allowable Loads for Span refer?

BTW, you' have nasty tables to work with.

======= I think we are misunderstanding each other. I tried your suggestion, and I was not getting the result that I need. It was giving me a reult of "l240" which is a value that in my query I should already have. The value that I need to find is the allowable load for that deflection value (based on the other values as well.) I have provided a link to a copy of the tables themselves. Here is what I need to be able to do. Take a look at the worksheet labeled sheet1. I need to fill cell K2 with a value based on the other values in Row 2. The coorect value for the current set is 90, which is found in sheet "CSJ" at location U28. What I need to know is if I can get there from here. If I cannot, what do I need to do so that I can. The tables were originally designed with printing in mind. There are 4 other spreadsheets much like this one, containg data that I would like to be able to query in the same manner. This set of tables is actually the least complicated of the five, and should be the easiest to start with.

In Sheet1 of the workbook for which you provided a link, the following is to be seen: {"Type","","Width","","Gauge","", "Deflection","","Span","", "Allowable Loads for Span"; "CSJ", "",8,"",16,"",240,"",16,"",""} Type appears to refer to the sheet CSJ (that's, a sheetname). Width refers to which field in CSJ: "Length"? Does Gauge refers to "Gauge" in CSJ? Deflection refers presumably to "Allow Deflec."! To which field in CSJ does Span refer? To which field in CSJ does Allowable Loads for Span refer? BTW, you' have nasty tables to work with.

In the sheet, spaces under the headings would change, based on what I am looking for. But, given the example I have set:

CSJ - would refer to the sheet labeled CSJ

Width refers to Joist Depth label found at the top of the individual tables (cells Q1-W1 in the example set "8" Joist Depth")

Gauge refers to the columns to the right of the "Gauge" label in cell R2

Deflection refers to the Allow deflec Column (J28 in the example.)

Span refers to Length in the table (I28-I30 in the example)

Allowable loads in the field I am trying to find with the given criteria. In the example set, the correct load is 90, found in cell U28.

The tables look great when printed, which is what they were originally designed for. They went into a catalog. I now have the task of trying to make the table data researchable on the computer, in the hopes of getting away from printing 50-page catalogs every other day.

Bumping up: Complex retrieval

John --

I don't know why I take up questions like this while knowing...

Here we go.

Concerns Sheet1:

Name via the Name Box A2 "Type", C2 "Width", E2 "Gauge", G2 "Deflection", and I2 "Span".

In J2 enter: =INDIRECT(Type&"!J3")

This formula fetchs a result computed in the relevant data/table sheet.

Concerns worksheet CSJ:

Insert 4 rows at the beginning. Don't protest -- I'm not changing those "peculiar" tables.

In A2 enter: # Columns [ just a label ]
In A3 enter: =IF(Type="CSJ", MATCH(9.99999999999999E+307, 6:6),"")

In C2 enter: # Rows [ just a label ]
In C3 enter: =IF(Type="CSJ", MATCH(9.99999999999999E+307,A:A) - ROW(5:5),"")

In E2 enter: Width [ just a label ]
In E3 enter: =IF(Type="CSJ", MATCH(Width&"*",5:5,0),"")

In G2 enter: Gauge [ just a label ]
In G3 enter: =IF(Type="CSJ", MATCH(Gauge,OFFSET(A6,0,E3,1,A3),0)+E3,"")

In E2 enter: Span [ just a label ]
In E3 enter: =IF(Type="CSJ", MATCH(Span,OFFSET(A2,0,E3-1,C3,1),1),"")

In J2 enter: Query Result [ just a label ]
In J3 enter: =IF(Type="CSJ", VLOOKUP("L/"&Deflection, INDIRECT(ADDRESS(I3,E3+1) & ":" & ADDRESS(I3+5,E3+6)), G3-E3,0),"")

Insert 4 rows at the beginning in all other data/table sheets that you have. Copy the range A2:J3 from sheet CSJ and paste in A2 in all other data/table sheets.

A comment if I may: The above is a bit "object-oriented" in that the relevant table picks up the query from Sheet1 if the query regards it.

Aladin

PS. Anyone interested in this complex retrieval can get a copy of the workbook. Just rop me a line.

Re: Bumping up: Complex retrieval

John -- I don't know why I take up questions like this while knowing... Here we go. Concerns Sheet1:

<Clip>

OK, I got that all keyed in, and it works, up to a point.

It appears that it is not pulling data from the last 8 rows of the sheet. If I understand it correctly, in sheet CSJ, cell C3 sets the number of rows to pull data from. Which row is it starting to count from? It should be counting from row 8, because that is the first row that data appears in, but it looks like it is counting from row 1, and cutting off the last 8 rows.

This is the formula as I typed it, maybe I missed something: =IF(Type="CSJ", MATCH(9.99999999999999E+307, A:A)-ROW(5:5),"")

Re: Bumping up: Complex retrieval

Would you give the parameters (according to sequence in Sheet1) of the query that it misses?

Re: Bumping up: Complex retrieval

John,

I think the formula

=IF(Type="CSJ", MATCH(9.99999999999999E+307, A:A)-ROW(5:5),"")

should be changed to:

=IF(Type="CSJ", MATCH(9.99999999999999E+307, A:A),"")

Aladin

============== : John -- : I don't know why I take up questions like this while knowing... : Here we go. : Concerns Sheet1: <Clip> OK, I got that all keyed in, and it works, up to a point. It appears that it is not pulling data from the last 8 rows of the sheet. If I understand it correctly, in sheet CSJ, cell C3 sets the number of rows to pull data from. Which row is it starting to count from? It should be counting from row 8, because that is the first row that data appears in, but it looks like it is counting from row 1, and cutting off the last 8 rows. This is the formula as I typed it, maybe I missed something: =IF(Type="CSJ", MATCH(9.99999999999999E+307, A:A)- ROW(5:5),"")

Another Fix & Recap

John --

I'll just list the formulas with fixes incorporated:

A3: =IF(Type="CSJ", MATCH(9.99999999999999E+307,6:6),"")

C3: =IF(Type="CSJ", MATCH(9.99999999999999E+307,A:A),"")

E3: =IF(Type="CSJ", MATCH(Width&"*",5:5,0),"")

G3: =IF(Type="CSJ", MATCH(Gauge,OFFSET(A6,0,E3,1,A3),0)+E3,"")

I3: =IF(Type="CSJ", MATCH(Span,OFFSET(A1,0,E3-1,C3,1),1),"")

J3: =IF(Type="CSJ", VLOOKUP("L/"&Deflection, INDIRECT(ADDRESS(I3,E3+1) & ":" & ADDRESS(I3+2,E3+6)),G3-E3,0),"")

These all concern the formulas in sheet CSJ.

Regards,

Aladin

John, I think the formula =IF(Type="CSJ", MATCH(9.99999999999999E+307,A:A) - ROW(5:5),"") should be changed to: =IF(Type="CSJ", MATCH(9.99999999999999E+307, A:A),"") Aladin ==============

Re: Bumping up: Complex retrieval

No need. It appears that the changes you posted earlier solved the problem.

You have made an engineer somewhere very happy, not to mention me. Thank you very much.

Would you give the parameters (according to sequence in Sheet1) of the query that it misses?