• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

To pull out specific cell value against relative references from Sheet 1 to sheet 2 table.

P

Pappa

New Member
Hi All, Good day

Can any one please help me to find a formula to pull out the reference cell from Sheet 1 to Sheet 2 against the name and entry which i made in sheet 1 table .

I have inserted an image for the your reference, with example , which i am looking for.

Eg: If I make an entry (Numeric-Alpha style only) in Sheet 1 table, like if I enter 12N in cell C6 against Name 1 , I wish to have letter C in Sheet 2 table against Name 1.

Similarly , If I enter 8N against Name 7 , I am looking for letter C to appear against Name 7 in Sheet 2.

If I revert the entry back to alphabet only, then it should disappear from sheet 2 table .

This is a short entry, but in actual i will continue it for the year ahead.

Thanks All in advance VIEW 123.JPG
 
Try,

In "Sheet 2" B2, formula copied down :

=INDEX(Sheet1!C$2:J$3,MATCH(RIGHT(INDEX(Sheet1!C$6:J$13,MATCH(A2,Sheet1!B$6:B$13,0),AGGREGATE(15,6,COLUMN(Sheet1!$C$1:$J$1)-COLUMN(Sheet1!$B$1)/(LEN(INDEX(Sheet1!C$6:J$13,MATCH(A2,Sheet1!B$6:B$13,0),0))>1),COUNTIF(A$2:A2,A2)))),Sheet1!B$2:B$3,0),AGGREGATE(15,6,COLUMN(Sheet1!$C$1:$J$1)-COLUMN(Sheet1!$B$1)/(LEN(INDEX(Sheet1!C$6:J$13,MATCH(A2,Sheet1!B$6:B$13,0),0))>1),COUNTIF(A$2:A2,A2)))

77781
 
Last edited:
Pappa
You should reread Forum Rules.
There are clear sentences, if someone wants to use Cross-Posting.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get a Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
Hi Bosco
Thank a Lot for the formula you provided:).

Its working perfect, and please excuse me for the cross posting, I was totally unaware of this rule

Gr8,:awesome: You have made my job effortless. Thanks again
 
You must log in or register to reply here.
Back
Top