Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Linking cells based on selection from another cell

Hi,


i have data in two sheets(same file) both the sheets have certain common data like name, employee ID etc.


I want to pull certain data from sheet 2 based on what i have selected in a particular cell in sheet 1


I have put employee name in rows and months in column and have data corresponding to each employee for that month.


and when i select a mont from a drop down list in sheet 1, i want to have the corresponding data of the same employee and month as in sheet 2


HOW DO I DO THAT ???


Help would be really appreciated.

MacBook Pro, OS X Mavericks (10.9.4)

Posted on Feb 26, 2015 6:44 AM

Reply
5 replies

Feb 26, 2015 2:13 PM in response to Niranjan6725

How about this as a starter:

User uploaded file

Table 2 is the data you entered with months in a column and names across the top. I formatted the month column as text. Same with the month column in Table 1. You could leave them both as "automatic" but I recommend text. Whichever you do, the formats in the two columns have to be the same or there will be no match.


Table 1 lets you enter a name and a month and it will find the data from Table 2. The formula in cell C2 of that table is

=IF(AND(B2≠"",A2≠""),OFFSET(Table 2::$A$1,MATCH(B2,Table 2::$A,0)−1,MATCH(A2,Table 2::$1:$1,0)−1),"")

Fill down to complete the column


Kind of a long formula but it breaks down like this:

The outer part, IF(AND(B2≠"",A2≠"") ... ,""), is simply to blank the result if you have not yet entered a name and date. Otherwise you'll have a lot of error triangles.


The first MATCH formula is looking for a match for the specified month in Table 2 column A. It returns a number that is how many rows down from cell Table 2::A1 it found the matching month. Let's call than number "Y".


The second match function is looking for a match to the name in Table 2 row 1. Again, it returns a number. Let's call it "X"


The OFFSET function creates a reference to the cell that is Y cells down and X cells to the right of Table 2::A1. That is the data you are looking for.

Feb 26, 2015 9:17 PM in response to Badunit

Hi,


i tried your formula, but i am getting a syntax error, i have attached the screenshots below for ref.


Table 1


User uploaded file


Table 2


User uploaded file


Hwat i want is when i select a month in Table 1 - T2 i want the corresponding data for that from Table 2 corresponding to that particular employee.


I put all my data in table 2, so when make a selection in T2 (Table 1) i want it pull data from Table 2 and put it in Table 1.


How your able to understand my issue here.

Feb 27, 2015 4:19 AM in response to Niranjan6725

The wording of the syntax error may tell you what the problem is.


I am not going to analyze it too much but I see a few things wrong of the bat:


The base cell in your OFFSET formula is not a cell (such as Month - Wise Attendance::$E$1), it is a range (Month - Wise Attendance::E1:P1). In addition, because you did not specify absolute referencing (the $ signs on the cell reference), if you fill the formula to other rows/columns, the base cell will be different in those other cells. Using a range instead of a cell is probably a syntax error.


In the MATCH formulas, the second parameter needs to use absolute referencing ($ signs) if you plan on filling the formula down or across to other cells.


You do not need AND(C4<>0), you can simply use C4<>0. This is not an error, just not necessary.

Feb 27, 2015 8:00 PM in response to Niranjan6725

Hello


If I understand it correctly, you may try something like the following tables.


User uploaded file



Table 1 A1 no A2 A3 A4 1 A5 2 A6 3 A7 4 A8 5 A9 6 A10 7 B1 employee id B2 B3 B4 100 B5 101 B6 102 B7 103 B8 104 B9 105 B10 106 C1 name C2 C3 C4 A A C5 B B C6 C C C7 D D C8 E E C9 F F C10 G G D1 work days D2 jan D3 =HLOOKUP(D2,Table 2::1:2,2,0) D4 =D$3 D5 =D$3 D6 =D$3 D7 =D$3 D8 =D$3 D9 =D$3 D10 =D$3 E1 attendance E2 =MATCH(D2,Table 2::1:1,0) E3 E4 =INDEX(Table 2::A:P,MATCH(B4,Table 2::B,0),E$2) E5 =INDEX(Table 2::A:P,MATCH(B5,Table 2::B,0),E$2) E6 =INDEX(Table 2::A:P,MATCH(B6,Table 2::B,0),E$2) E7 =INDEX(Table 2::A:P,MATCH(B7,Table 2::B,0),E$2) E8 =INDEX(Table 2::A:P,MATCH(B8,Table 2::B,0),E$2) E9 =INDEX(Table 2::A:P,MATCH(B9,Table 2::B,0),E$2) E10 =INDEX(Table 2::A:P,MATCH(B10,Table 2::B,0),E$2)




Notes.


I used employee id instead of name to look up data in Table 2.


E2 is calculating the column index of given month (in Table 1::D2) in Table 2.


Formula in E4 can be filled down accross E4:E10.


Tables are built in Numbers v2.



Good luck,

H

Linking cells based on selection from another cell

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.