Cross reference issue

[ahhhmed]

I have a table of 20 rows and 6 columns (A1:F1)

Headings: A1 (NAMES ) , (B1:F1) PROFESSION1 to PROFESSION5. The names are then distributed in the table according to nationality.

I need to create another table from the original one in which I want the A column showing all the names, and instead of writing these names in the profession columns, I need a formula that shows the number (1) in the relevant cell.

For example ( A4 has JOHN in it ) John is a doctor. D1 has the heading ( DOCOTRS ). Now I want to have the number (1) in (D4)


How can this be done?

 

[Hui]

Ahhhmed


Can you post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

Can you also add an area which shows the data and an area which has the expected result

 

[ahhhmed]

Here's a sample file for your concern, Hui


http://www.4shared.com/file/9M9txbcP/sample.html

 

[keymaster]

Hi Ahhhmed...


In your example file, in K2 write =IF(B2<>"",1,"") and then fill the entire range. This should give the output as you want.

 

[ahhhmed]

Good point, Chandoo; But the problem is that the names in columns B:F are changeable. John may be a doctor once, but a businessman next time - This is an example, of course - How can we make a formula that follows the change in profession for the same name?