You are here:

Excel/Excel Data Reference


QUESTION: Sheet1 is an overall status spreadsheet with rows representing personnel and the columns representing their job position. The cells represent the number of tasks the person is qualified on.

Sheet2 is the training tasks for a job position with rows representing the training tasks and the columns representing the personnel. The cell is simply an 'X' if they have completed training for that task.

On Sheet2, I have a total that uses =COUNTA to add up all the 'X's for each person. On Sheet1, I have the total for that job in a persons cell using ='Sheet2'!<cell of the total>

Using the attached image for an example:

I use ='Sheet2'!B$9 for Sheet1 cell C5...what can I do to quickly make C6 equal the value of one cell over and C7 equal the value of another cell over and so on?

My actual spreadsheet has 24 people with 8 possible job positions and hundreds of total tasks, so I want it to be easier to intially setup and add another person in the future.


ANSWER: Sheet1 cell C5 try this lookup formula:

 =INDEX(Sheet2!$9:$9, MATCH($A5, Sheet2!$4:$4, 0))

Now copy C5 down as needed.  It will keep working for that section no matter how many names you add in sheet2 row 4.

---------- FOLLOW-UP ----------

QUESTION: First off...your answer above was amazing and works great! Follow-up question, how can I have my names in column A on Sheet1 replicate to row 1 of all the other sheets? So that if I add a person to the first it adds them to the other sheets?

Let's say Sheet1 has names starting in A5 and you're going to add names as needed going down that column.

On the other sheet, in the first cell where you want that first name to appear (it can be anywhere), put this lookup formula:

      =INDEX(Sheet1!$A$5:$A$100, COLUMNS($A$1:$A1))&""

Now copy that cell to the RIGHT and your names will appear.  Keey copying for 100 columns if you wish, the cells will show blank when there are no more names.

Now as names are added to Sheet1 columnA they will appear on their own on the other sheets.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jerry Beaucaire


Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques:


Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: ===================== I have been offering free assistance as an Excel aid on many web sites for many years: ( - JBeaucaire) ======== ( - JBeaucaire) ======= ( - jbeaucaire)

Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2017 All rights reserved.