MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formula problem


Posted by Glenn on March 08, 2001 6:32 PM

I have a linked workbook where I want to have selected headings from my source worksheet transported to my destination worksheet.

Specifically, my source worksheet has a list of people who will have one, and only one, appearance in either booth 15, 16, 17, 18, 19, 20 or 21. The booth numbers are headings. What I need is a formula in my destination worksheet that will transport the heading number, of the booth that each person is in, from the source worksheet.

Since, only one of the six headings will have a 1, which indicates a person's booth, I thought I could use an IF formula but I haven't been able to figure it out.

Can someone tell me if this can be done or offer another alternative.

Thank You


Posted by Mark W. on March 09, 2001 6:56 AM

Please provide sample data.

Posted by Glenn on March 09, 2001 9:14 AM

Mark W.

BELOW PLEASE FIND SAMPLE DATA.

I want those the booth number for each family transferred/Linked to another worksheet.

BOOTH
Family 15 16 17 18 19 20
James 1
Rogers 1
Fitch 1
Michaels 1
Daniels 1
Moore 1
Toms 1

Thanks
Glenn


Posted by Glenn on March 09, 2001 9:20 AM

Mark W.

Second submission. Hopefully this is clearer.
BELOW PLEASE FIND SAMPLE DATA.

I want the booth number for each family transferred/Linked to another worksheet.

BOOTH
Family 15 16 17 18 19 20
James 1
Rogers 1
Fitch 1
Michaels 1
Daniels 1
Moore 1
Toms 1

The data should be 1 under
James 18
Rogers 15
Fitch 18
Michaels 17
Daniels 19
Moore 16
Toms 20

Thanks
Glenn


Posted by Mark W. on March 09, 2001 11:10 AM

Okay, Glenn, let's assume that Sheet1!A1:G8 contains

{"Family",15,16,17,18,19,20;
"James",0,0,0,1,0,0;
"Rogers",1,0,0,0,0,0;
"Fitch",0,0,0,1,0,0;
"Michaels",1,0,0,0,0,0;
"Daniels",0,0,0,0,1,0;
"Moore",0,1,0,0,0,0;
"Toms",0,0,0,0,0,1}

Note: the zeroes shown here are really blank cells.

If Sheet2A1:A7 contains

{"James"
;"Rogers"
;"Fitch"
;"Michaels"
;"Daniels"
;"Moore"
;"Toms"}

Then you can enter the formula
{=MAX(INDEX(Sheet1!$B$2:$G$8,MATCH(A1,Sheet1!$A$2:$A$8,0),0)*Sheet1!$B$1:$G$1)}
into cell Sheet2!B1 and copy down.

IMPORTANT: It should be noted that this is an
array formula. In case you're not familiar with
this type of formula -- the MUST be entered by
depressing (simultaneously) the Shift+Ctrl+Enter
keys. In fact, the braces (at the beginning
and end of this formula) that I've shown are NOT
typed by you -- they are "added" by Excel upon
entry.

Posted by Tim Francis-Wright on March 09, 2001 1:07 PM

Simpler formula?

I think that the following formula (also an
array formula) also works:
=SUM(IF(A1=Sheet1!$A$2:$A$8,Sheet1!$B$1:$G$1*Sheet1!$B$2:$G$8))

Posted by Mark W. on March 09, 2001 1:58 PM

Unless...

a family has more than 1 booth.

Posted by J. Marcus on March 09, 2001 2:20 PM

Re: Unless...


According to the criteria in the qestion "only one of the six headings will have a 1" (i.e. only 1 booth per family).

Posted by Mark W. on March 09, 2001 2:51 PM

Re: Unless...

Yep... that's what it says today! But given the
choice between two designs, I'd much rather pick
one that is well behaved when confronted with
real-world possibilities. Currently, the Fitch's
and the James's are sharing a booth. What if one
of them decide to branch out and acquire a 2nd.
As Carly Simon would say...Anticip-a-a-a-t-i-o-n.

Posted by J. Marcus on March 09, 2001 3:16 PM

Re: Unless...

Well what about just ignoring the original question completely and change all the conditions.
That way you could come up with a really complicated formula.

Posted by Glenn on March 09, 2001 7:49 PM


Mark, my first sheet, Players! A2:A8 contains from Family to Toms…K2:Q2 contains 15-20 and related marks (i.e. James has 1 under 18)
{"Family",15,16,17,18,19,20;
"James",0,0,0,1,0,0;
"Rogers",1,0,0,0,0,0;
"Fitch",0,0,0,1,0,0;
"Michaels",1,0,0,0,0,0;
"Daniels",0,0,0,0,1,0;
"Moore",0,1,0,0,0,0;
"Toms",0,0,0,0,0,1}
Note: the zeroes shown here are really blank cells.


My Sheet2 A4:A10 contains
{"James"
;"Rogers"
;"Fitch"
;"Michaels"
;"Daniels"
;"Moore"
;"Toms"}

I tried to replicate your formula and input the following into cell Sheet2!B4 and copied down:
{=MAX(INDEX(Players!$K$2:$Q$8,MATCH(A4,Players!$A$3:$A$10,0),0)*Players!$K$2:$Q$2)}

Note: Per your instructions I depressed (simultaneously) the Shift+Ctrl+Enter to get the brackets.

Unfortunately, the result for all cells keeps coming up as 441. The formula you provided works using your example but I can’t seem to get your configuration to work for my workbook.

Any ideas? I'm gonna try Tim's formula again. But his kept giving me 0's.

Thanks for all your help.
Glenn

Posted by Glenn on March 09, 2001 8:41 PM

Re: Simpler formula?

Tim-

Formula works with Mark's numbers but I keep getting a result of 20 in all the cells that I pasted and filled down the following formula.

{=SUM(IF(A4=Players!$A$3:$A$8,Players!$K$2:$Q$2*Players!$K$3:$Q$8))}

The cell references are those described in my followup to Mark.

Thanks
Glenn

Posted by Mark W. on March 09, 2001 11:02 PM

Re: Unless...

When you learn a little more about data structures will continue this discussion. I suggest you begin by learn what a 1:M (1 to many) relationship is. While this user say's that there are no more than 1 booth per family his data structure suggests otherwise.

Posted by J. Marcus on March 10, 2001 3:32 AM

Re: Unless...


Mark W.
What about being a little bit less than a plonker?
I suggest you begin by attempting to acquire the art of humility.
JM

Posted by Aladin Akyurek on March 10, 2001 3:50 AM

{"Family",15,16,17,18,19,20; "Toms",0,0,0,0,0,1} Note: the zeroes shown here are really blank cells.

Glenn,

Both of the array formulas should work. If it's the case that any given player may belong at most to one booth, Tim's simpler formula will do the job. If it is for any reason possible to have 2 or more booths to which a given name (player) may belong, Mark's formula will give you the highest numbered booth the player belongs to.

A close look at the formula that you tried out reveals that you've got the ranges that you feed to both of the formulas are wrong.

It seems you have your source data on sheet1 and you have on sheet2 some (or all) player names for which you want to lookup the booth number. It's much cleaner if you do the following:

Sheet1:

Select cells in which you have the booth numbers and name this range of cells BOOTHS via the Name Box (or via the option Insert|Name|Define).
Select the cells in which you have the (player) names and name this range PNAMES via the Name Box.
Select cells in which you have 1's that indicate the players's booth numbers and name this range ASSIGNMENTS via the Name Box.

Sheet2:

Go to the cell next to the first player's name and array-enter (as explained to you: hit CONTROL+SHIFT+ENTER at the same time to enter) one of the formulas Mark and Tim have susggested:

=MAX(INDEX(ASSIGNMENTS,MATCH(A4,NAMES,0),0)*BOOTHS)

or

=SUM(IF(A4=NAMES,BOOTHS*ASSIGNMENTS))

and copy down (not Copy and Paste, but AutoFill) the entered array-formula for the rest of players.

I assumed that the first player's name is in A4 on sheet2 and one of these formulas will go in B4 on sheet2.

Aladin

Posted by Glenn on March 10, 2001 7:41 AM

Aladin:

BINGO!!!

I followed your instructions and everything worked out perfectly.

Thank you so much for your help.

Glenn

Posted by Glenn on March 10, 2001 7:57 AM

Re: Unless...

Dear J. Marcus and Mark W.:

I want to thank you both for your help. I was able to resolve the formula problem.

You both know a heck of a lot more about Excel than I do and I appreciate BOTH of your efforts. Please stop the sniping and keep helping us excel neophytes.

Sincerely,
Glenn


Posted by Glenn on March 11, 2001 7:17 AM

Aladin-

Your formula worked fine. One additional question.

Assume the same information on my two sheets. Assume I had a separate heading "friends" which I wanted treated just like my booth problem, however the players cells (under the "friends" heading) could contain either 0,1,2,3 or 4. Is there a way that anytime a "friends" cell contains a 1,2, 3 or 4 the data could be transferred with the heading "Friends" and the cell contents written as a superscript?

I know that the easiest thing to do would be to just use a formula that would transfer the number to my second sheet under a heading of "friends". However, I was curious as to the possibility of displaying the transferred data utilizing a superscript.

Any enlightenment you can give me on the subject would be appreciated.

Thanks
Glenn