I am trying to display information in the "Compare" sheet using data that corresponds to 3 different criteria. I would like to enter in a pattern name (AM, MD, PM, PO, etc.) that corresponds to the sheet containing data that is organized per "ID" and "Phasing Data". I've reviewed several different posts pertaining to index and match and feel a bit overwhelmed. Any suggestions would be greatly appreciated.
Hello Sctraffic, Welcome to the forum!.
See the attached with INDIRECT function. Hope this helps
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
Hi Sctraffic
Look at the enlcosed file for the direct formula in the Cell. however, it is taking more time for calculations.
Thank you for your help. That's exactly what I was looking for.
Sorry to bother you, but in my attempts to familiarize myself with the formula, I'm not understanding the "CurrSheetData" entry. Is there an easy explanation. Thanks in advance.
Sctraffic,
CurrSheetData is a dynamic named range. Press Control+F3, then you can see these named ranges. Select a name down Refers to: you can see the formula used for this.
When you use this from B4, it will look for the Patt in B4 which is AM, when you change to others, say SA this will look on this sheet. Basically this will look for the sheet entered in the current row in col_E. When you copy to B5, this will look the value in E5, in B6 value in E6, B7 value in E7 etc... This is relative reference not absolute reference.
One last question...hopefully...i understand what is being accomplished with the CurrSheetData, however, i'm not seeing where any equations actually look to the Patt cell for direction. How does it know which pattern tab to look into for that value? Thanks.
Sctraffic,
The name range here is relative reference to row# not absolute row reference & absolute to column# not ralative. So, the active row is very important when you create the name. Your all patt are in E4 to down. So Col_E is locked (absolute)
On compare sheet the heading is in row#3, C3:M3. Row#4 to down is datas. Before you create a name select B4 or any cel in row#4
Take this name. CurrLength, Before you create select B4
=MATCH(REPT("Z",255),INDIRECT("'"&Compare!$E4&"'!A:A"))
See the red highlighted. locked the column reference with $ not the row. When you use this name range in Row#5, it would become.
=MATCH(REPT("Z",255),INDIRECT("'"&Compare!$E5&"'!A:A"))
This will adjust to the current row. the meaning here is, The row # of Last text contains, in the sheet located in current row
In B4, the sheet is AM
In B5, the sheet is MD
In B6, the sheet is PM
etc....
Same method in CurrSheetData
Used relative row reference & absolute column reference.
=OFFSET(INDIRECT("'"&Compare!$E4&"'!$A$1"),0,0,CurrLength,16)
See the red highlighted. Row# is relative.
In this row (Row#4) this will look for the patt in E4, which is AM
When you copy to B5, it would become,
=OFFSET(INDIRECT("'"&Compare!$E5&"'!$A$1"),0,0,CurrLength,16)
In this row (Row#5) this will look for the patt in E5, which is MD, NOT 'AM'
etc....
Hope this help you.
It did help thanks again. I didn't know how much power excel has.
I have copied the compare sheet in order to list out several other catergories, however, I am faced with having to enter the "ID" number in manually. Can you suggest and way to have this value entered from either the data tabs or maybe create a separate tab that hosts this information to reference. Thanks.
I would like to return the phase that is associated with the "ON" listing. Any suggestions. Thanks in advance.
Last edited by sctraffic; 07-26-2011 at 02:23 PM.
I would like to return the phase that is associated with the "ON" listing in the db101 sheet in the "REF" column similar to the way it is in the columns labeled 1 thru 8. Any suggestions. Thanks in advance.
Last edited by sctraffic; 07-26-2011 at 02:22 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks