HELP - Problems with Excel formulas

[Wizardbk]

I have a couple of workbooks that i need to link. I have a master list with a bunch of names and on that master list there is a check mark next to an item that the person has. Lets say for example John Doe has an apple, under apples there is a check mark. Now i have another couple of workbooks ex. a list of apples. What i am trying to do is if i enter a name on the apple workbook, i would like it to appear on the master list and then put a check mark under apples. Is there a way that i can do this? Also if I delete the name on the apple workbook can the name be deleted from the master list?

 

[davidmunoz]

I'm sort of confused on what your asking, but maybe creating a shared,MASTER workbook, would capture everything you are wanting to do. Shared workbooks allows you to set parameters of updates and who over rides who when a conflict occurs amongst everyone using the same workbook.

Hope this Helps!

 

[Wizardbk]

Im asking is there a way that I can add a name to the APPLE workbook and that name would be automatically added to the Master workbook

 

[davidmunoz]

If I understand correctly then you would have your Master workbook with all cells linked to the APPLE worksheet but you wouldn't be able to change the cell data in the master to show on the APPLE worksheet.

Your best approach would be to use the Protect and Share Workbook functionality found under Tools/Protection on the menu bar.

Hope this helps!

 

[MarkBeck]

Firstly; you should probably have these in one Workbook, on seperate Worksheets called "Master" and "Items".

If i understand your scenario correctly your master list is a grid; with Items as the column headings and names as a list.

In a conventional grid, one finds it practicable to present the longer list in a vertical list format, while the shorter list is presented as column headings.

If the Items represent the longer list, then simply list them in the "Items" worksheet (A1:A50) and in corresponding cells in the "Master" worksheet enter "=Items!A1", and copy the formula down the list.

If the names are the longer list, you can still do this but with the following formula;

=IF(INDEX(Items!$A:$A,COLUMN(B1),1)=0,"",INDEX(Items!$A:$A,COLUMN(B1),1)).

Drag this formula acress as many columns you reckon you will need. any unused/ Added/ Deleted items will automatically be reflected.


Mark

 

[Wizardbk]

Apples Oranges Peaches
Doe, John X
Doe, Jane X


This is basically the way my MASTER list looks, but with much more users. I have another workbook with a list of all users with apples. What i want to happen is when i enter a name on the list of apples then it would appear on the MASTER list and put a "X" under Apples. Hope this makes it easier to understand.

 

[MarkBeck]

I assume that just as with the list of apples, you also have lists for oranges and peaches.

It is generally more prudent to have ALL names in a single column (A) and all Items alongside them in column B.

I would offer you the following structure;

A) in the "Items" worksheet (not the Master), you will have the following formula in column C (alongside Columns A & B as above); =CONCATENATE(A1,B1).

B) In the "Master" worksheet you will have the following formula in each cell in the grid; =IF(ISNUMBER(MATCH(CONCATENATE($A2,B$1),Items!$C:$C,0)),"X","&quot.

This will automate the "X" whereever applicable.


There are other ways of achieving the same results, particularly using "Lookup" formulas such as "Vlookup" or an "Index/Match" combination.


Good Luck

Mark

 

[Wizardbk]

Thank you this does make the X populate wherever the name is. Is there a way that the name can also be automatically added for example, I added a new name to the apple list, I would like it to be automatically added to the MASTER list.

 

[Wizardbk]

I figured out the formula =IF(MATCH(A2,A:A,Items!$A:$A),A2,"&quot but now i need to join that with IF(ISNUMBER(MATCH(CONCATENATE($A2,B$1),Items!$C:$C,0)),"X","&quot. I tried using an AND statement but I keep on getting an error.

 

[MarkBeck]

First question; In the Master worksheet enter in A2 (assuming this is where names figure)"=if(isblank(Items!A2),"",Items!A2). This will change your list of names in the "Master" worksheet whenever you modify the "Items" worksheet.

Second question; I dont understand your formula (Match function, third field only accepts either = 1 (nearest LARGER figure, if not exact) or = 0 (Exact figure only) or -1 (nearest LOWER figure if not exact). for some reason you have "Items!$A:$A". This would not invalidate your function but will instead evaluate the value in the referenced cell and insert the resulting number as its argument. this might not be your desired value.

I also dont understand why you need to join these 2 formulas, or what the first formula in your question is intended to achieve.

Mark