Data Publishing
Data Publishing
(OP)
Hi All,
I have a problem that I'm trying to solve but I don't know if Excel can handle it.
I'm using ETABS to review adding 3 steel floors on a 10 story existing concrete building. I print the column design results (gravity and lateral) to an excel spreadsheet from ETABS.
ETABS provides a file where all the data is in its own column and each level of each column is its own row. I want to be able to type into a "cover" sheet a column and have it show the ratio of required reinforcement (from analysis) and existing reinforcement (from plans). I don't want to use any filters or anything because I want to be able to put any column I want on a seperate sheet from the actual data so when a presentation is given on the subject it would be easy to change columns. I don't want to do all sorts of copying and pasting.
I'm familiar with the Index and Match functions but they wont' work here because of the 2 sets of rows, one for the actual column group and one for the level of analysis.
Any one have any ideas how I should go about this?
I have a problem that I'm trying to solve but I don't know if Excel can handle it.
I'm using ETABS to review adding 3 steel floors on a 10 story existing concrete building. I print the column design results (gravity and lateral) to an excel spreadsheet from ETABS.
ETABS provides a file where all the data is in its own column and each level of each column is its own row. I want to be able to type into a "cover" sheet a column and have it show the ratio of required reinforcement (from analysis) and existing reinforcement (from plans). I don't want to use any filters or anything because I want to be able to put any column I want on a seperate sheet from the actual data so when a presentation is given on the subject it would be easy to change columns. I don't want to do all sorts of copying and pasting.
I'm familiar with the Index and Match functions but they wont' work here because of the 2 sets of rows, one for the actual column group and one for the level of analysis.
Any one have any ideas how I should go about this?
RC
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke





RE: Data Publishing
Add a column with numbers 1 down to whatever rows you have. These are your markers.
Then "lookup" your column name, to catch the row number in the column added above. Use the number you just found to "lookup" each of the data across the various columns, and reference from this point to pick up other rows.
Use data validation and a drop down list if you have funny column names.
I'd need an example of data to improve on this.
RE: Data Publishing
Thank you for the quick response, sorry for being vague, here is the beset I could do quickly,
Col. Story As, req'd As, provided
F-1 3 3.5 6.4
F-1 2 3.75 6.4
F-1 1 4.05 6.4
F-2 3 3.58 6.4
F-2 2 3.73 6.4
F-2 1 4.05 6.4
F-3 3 3.51 6.4
F-3 2 3.72 6.4
F-3 1 4.05 6.4
I want to be able to type F-1 in a cell on a diff. sheet and then get all the information for that particular column at ever level.
RC
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
RE: Data Publishing
It seems you will have to type story number as well. And find index of the column first and then index of the story.
ColIndex = match(col, RangeCol,0)
StoryIndex = match(story,offset(RangeCol,ColIndex,1),0)+ColIndex - 1
As = index(RangeAs,storyIndex)
should work!
RE: Data Publishing
You put in the right direction and I was able to modify your suggestion to be suit my overall needs!
I love these boards!
RC
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke