CODEWITHSUNDEEP
excel
user2987549
user2987549

Reputation: 1

Excel to find value from one sheet & show to another sheet

I have a sheet1 where column A= employees name & column B=Status (present, absent, etc), I prepare Daily attendance in each sheets, so I have 31 sheets in a month. Now I have a new sheet November. in this sheet column A is employees name and column B, C, D, etc for each days status. show me status of each employes in each days B, C, etc

Upvotes: 0

Views: 3877

Answers (1)

sherwoor
sherwoor

Reputation: 185

You would use the VLOOKUP statement to fetch the status from the other sheets. This is in the format: vlookup([cell which has the value you want to lookup], [range of cells which contain the table], [column you want to return from that table]). Hopefully this is explained in the example below:

For ease of explanation, imagine your sheets are named Nov1, Nov2... Nov29, Nov30 and NOVEMBER.

Nov1:

  A               B
1 Employee Name Status
2 Albert        Present
3 Brian         Absent
4 Catherine     Present
5 David         Present
6 Edward        Present

Nov2:

  A               B
1 Employee Name Status
2 Albert        Absent
3 Brian         Absent
4 Catherine     Present
5 David         Absent
6 Edward        Absent

Then, in your sheet NOVEMBER, which is the month summary, use the formulae:

  A               B                                C
1 Employee Name   Nov 1st                          Nov 2nd    
2 Albert          =VLOOKUP($A2,Nov1!$A$2:$B$6,2)   =VLOOKUP($A2,Nov2!$A$2:$B$6,2) 
3 Brian           =VLOOKUP($A3,Nov1!$A$2:$B$6,2)   =VLOOKUP($A3,Nov2!$A$2:$B$6,2)
4 Catherine       =VLOOKUP($A4,Nov1!$A$2:$B$6,2)   =VLOOKUP($A4,Nov2!$A$2:$B$6,2)
5 David           =VLOOKUP($A5,Nov1!$A$2:$B$6,2)   =VLOOKUP($A5,Nov2!$A$2:$B$6,2)   
6 Edward          =VLOOKUP($A6,Nov1!$A$2:$B$6,2)   =VLOOKUP($A6,Nov2!$A$2:$B$6,2)

Using the $ symbol will mean you can copy down the column and the formulae will update automatically. Ensure that the list of employees in your separate daily sheets are in alphabetical order.

So this should result in something like this:

  A               B                                C
1 Employee Name   Nov 1st                          Nov 2nd    
2 Albert          Present                          Absent
3 Brian           Absent                           Absent
4 Catherine       Present                          Present
5 David           Present                          Absent 
6 Edward          Present                          Absent

Upvotes: 1

Related Questions