Hi I am trying to do 2 things that I need help with.

1. The first and more than likely a simple one... I am trying to
populate a cell (say for example A1) automatically with the same
information that is entered in a2.

I have been just using =A2 in the cell A1, but this inputs 0 until
someone populates A2. Is there an easy way to clean up the 0 or
should I use a function for this?

2. I have job titles which match up to competencies on a hidden
worksheet, the table would look like this:
Grade Competency Description
Grade A Communication Strong communicators demonstrate......
Grade A Planning Planning is all about.........
Grade B Innovation Is all about..........

There are 4 grades and I want to enable users to populate on a front
sheet their employee information (including a cell with Grade A,b,c or
D in a dropdown) then based on the grade that is selected the relevant
competencies are pulled from the hidden worksheet and dumped onto a
visible worksheet.

Please Help

A lot of questions!!

The easy one first in a1 type =if(a2<"",a2,"")

The next bit is more complicated but one way:-

On your fornt sheet type in a cell =VLOOKUP(A5,Sheet1!A:B,2,FALSE)

This gets the grade entered from A5 and looks in the hidden sheet (sheet 2)
for a matching grade in column A and reurns the competency from column B.

Unfortunately it now gets more complicated because there are more than 1
competency for a grade so we need to look for the second and subsequent
instance of the grade on the hidden sheet using something like:-

=INDEX(Sheet1!$A$2:Sheet1!$B500,SMALL(IF(Sheet1!$A $2:Sheet1!$B500=$A$5,ROW(Sheet1!$A$2:Sheet1!$B500)-ROW(Sheet1!$A$2)+1,ROW(Sheet1!$B500)+1),2),2))

This formula returns an error if it doesn't find a second instance but you
can get around that using =if(iserror...........

The last 2 number 2 are the sugnificant bit here they tell the formula to
find the second instance of GRADE on the hidden sheet and return the value
from column 2. You can add as many of these as you wish to find the 3rd and
additional competencies.

Can you build on that?

Mike

" wrote:

Hi I am trying to do 2 things that I need help with.

1. The first and more than likely a simple one... I am trying to
populate a cell (say for example A1) automatically with the same
information that is entered in a2.

I have been just using =A2 in the cell A1, but this inputs 0 until
someone populates A2. Is there an easy way to clean up the 0 or
should I use a function for this?

2. I have job titles which match up to competencies on a hidden
worksheet, the table would look like this:
Grade Competency Description
Grade A Communication Strong communicators demonstrate......
Grade A Planning Planning is all about.........
Grade B Innovation Is all about..........

There are 4 grades and I want to enable users to populate on a front
sheet their employee information (including a cell with Grade A,b,c or
D in a dropdown) then based on the grade that is selected the relevant
competencies are pulled from the hidden worksheet and dumped onto a
visible worksheet.

Please Help