In the workbook, there are 2 sheets. Sheet1 contains a list of columns in a building, with the relevant coordinates of the column. Sheet2 contains a list of column types and the reference number of the individual columns (ie, AA03 is a Type 1 column etc.)
So, for clarity:
"Sheet1"
Column ref X Y Z type
AA01 0.0 0.0 0.0 (to be found from Sheet2)
AA02 0.0 9.0 0.0
AA03 0.0 18.0 0.0
etc.
"Sheet2"
Type Column ref
1 AA01, AA03, AA07 (selection of others, each in individual cells)
2 AA02, AA04, AA05 (etc.)
3 AB02, AC03, AD04 (etc.)
Is it possible to get Excel to look up a column reference in the array on the second sheet and determine what column type a given column reference is - i.e. can it search the array on Sheet2 to find which row/column type column reference AA03 is, for example.
It's kind of like the INDEX function, except that instead of providing an array and giving it a row and column reference, we want to give it an array and a criteria to match, and then it to tell us what row it's in.
The idea behind this is to get everything in a list where each row contains the column ID, the XYZ coordinates of the column, and the column type, so that we can filter by column type to get a list of coordinates for each column type which we can then use create a script to insert large blocks of one type of column into a CAD program without having to do it manually.
Hopefully this is clear enough for people to understand what i'm trying to do and tell me if it's possible or not.
Many thanks in advance for the help.
So, for clarity:
"Sheet1"
Column ref X Y Z type
AA01 0.0 0.0 0.0 (to be found from Sheet2)
AA02 0.0 9.0 0.0
AA03 0.0 18.0 0.0
etc.
"Sheet2"
Type Column ref
1 AA01, AA03, AA07 (selection of others, each in individual cells)
2 AA02, AA04, AA05 (etc.)
3 AB02, AC03, AD04 (etc.)
Is it possible to get Excel to look up a column reference in the array on the second sheet and determine what column type a given column reference is - i.e. can it search the array on Sheet2 to find which row/column type column reference AA03 is, for example.
It's kind of like the INDEX function, except that instead of providing an array and giving it a row and column reference, we want to give it an array and a criteria to match, and then it to tell us what row it's in.
The idea behind this is to get everything in a list where each row contains the column ID, the XYZ coordinates of the column, and the column type, so that we can filter by column type to get a list of coordinates for each column type which we can then use create a script to insert large blocks of one type of column into a CAD program without having to do it manually.
Hopefully this is clear enough for people to understand what i'm trying to do and tell me if it's possible or not.
Many thanks in advance for the help.