FS: need help with Excel formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

daveallston@rogers.com - 07 Oct 2006 21:02 GMT

Hello
I am desperately searching for help with how to do two things in Excel
with using formulas. I'm sure to some of the Excel experts in this
forum, they may be quite simple, but I'm killing myself searching on
the net and in Excel Help for the answers and can't figure it out. So
if you can't help me, I'll be forever greatful!!

QUESTION ONE: How do I make a formula to copy text if the value of a
cell matches another in another tab. Specifically, I am trying to
associate hockey statistics from one tab to another. I have data to be
dropped in tab "injuries". I then am trying to make a formula in my
main tab, titled "main", whereby if the name in box A6 of "main" is
equal to a name found in the range B2:B500 in "injuries", then I want
in box R6 of "main" to copy the information in box H* of "injuries"
(where the * represents the matched line from the first part of the
formula) - i.e. if the player is injured and listed in the "injuries"
tab, then when the player is found in the "main" tab, it will pull his
injury info (which is text) from H25 of "injuries" and copy the same
text to box R6.

I hope that makes sense.

QUESTION TWO: This one may be a bit simpler. And is completely separate
from the above question. I want to create a formula, whereby if H1/J1
is equal to 0 over 1 (H1 = 0 and J1 = 1), then box K1 will show the
text "DNP" (or whatever, just some kind of text). Is this possible?

Thank you SO MUCH to whoever can help me solve these two problems...
Thanks
Dave
daveallston@rogers.com

Reply to this Message

Ian - 07 Oct 2006 21:22 GMT

Q2 In K1 put
=IF(AND(H1=0,J1=1),"DNP","")

Q1 A bit beyond me, but VLOOKUP may be worth a look.

Signature

Ian
--

> Hello
> I am desperately searching for help with how to do two things in Excel
[quoted text clipped - 27 lines]
> Dave
> daveallston@rogers.com

Reply to this Message

joeu2004@hotmail.com - 07 Oct 2006 21:22 GMT

> QUESTION ONE: How do I make a formula to copy text if the value of a
> cell matches another in another tab. Specifically, I am trying to
[quoted text clipped - 8 lines]
> injury info (which is text) from H25 of "injuries" and copy the same
> text to box R6.

I think the following does what you want. But the formula into R6:

=vlookup(A6, injuries!B2:H500, 7, false)

That searchs injuries!B2:B500 for an exact match to A6 and returns the
cell from the 7th column of the range B2:H500, which is column H.

Note that you do not really need to type injuries!B2:H500. As you type
the VLOOKUP() arguments, when you come to the second one, simply click
on the "injuries" tab and select the range B2:H500 with the mouse.

> QUESTION TWO: This one may be a bit simpler. And is completely separate
> from the above question. I want to create a formula, whereby if H1/J1
> is equal to 0 over 1 (H1 = 0 and J1 = 1), then box K1 will show the
> text "DNP" (or whatever, just some kind of text). Is this possible?

In K1, put:

=if(and(H1=0,J1=1), "DNP", "")

Reply to this Message

daveallston@rogers.com - 07 Oct 2006 21:28 GMT

Wow that is awesome. Thank you so much to you both for taking the time
to help me out.
I'm going to go try it out now, but it sounds like it will work
perfectly.
Phenomenal.
Thanks again!!!
dave

> > QUESTION ONE: How do I make a formula to copy text if the value of a
> > cell matches another in another tab. Specifically, I am trying to
[quoted text clipped - 28 lines]
>
> =if(and(H1=0,J1=1), "DNP", "")

Reply to this Message

daveallston@rogers.com - 07 Oct 2006 21:50 GMT

oh I do have one other follow-up question...

for the VLOOKUP function,
when there is no match found (i.e. the cell value in cell A6 of "main"
is not found in the "injuries" tab in the range B2:H500, the result in
R6 is:

#N/A

Is there an additional part I can add to the formula in R6 to say that
if there is no match, that cell R6 remains blank?
Thanks again,
Dave

> Wow that is awesome. Thank you so much to you both for taking the time
> to help me out.
[quoted text clipped - 36 lines]
> >
> > =if(and(H1=0,J1=1), "DNP", "")

Reply to this Message

joeu2004@hotmail.com - 07 Oct 2006 21:55 GMT

davealls...@rogers.com wrote:

> Is there an additional part I can add to the formula in R6 to say that
> if there is no match, that cell R6 remains blank?

I figured that would come up. Unfortunately, this requires that you
write (and execute!) the vlookup twice. For example:

=if(iserror(vlookup(A6, injuries!B2:H500, 7, false)), "", vlookup(A6,
injuries!B2:H500, 7, false))

Reply to this Message

Gord Dibben - 07 Oct 2006 22:55 GMT

=IF(ISNA(VLOOKUP(A6,injuries!$B$2:$H$500,7,
FALSE)),"",VLOOKUP(A6,injuries!$B$2:$H$500,7, FALSE))

Note I added the absolute references for the table so it doesn't change as you
copy down.

Gord Dibben MS Excel MVP

>oh I do have one other follow-up question...
>
[quoted text clipped - 50 lines]
>> >
>> > =if(and(H1=0,J1=1), "DNP", "")

Reply to this Message

Biff - 08 Oct 2006 02:05 GMT

Here's another one:

=IF(COUNTIF(injuries!$B$2:$B$500,A6),VLOOKUP(A6,injuries!$B$2:$H$500,7,0),"")

Biff

> oh I do have one other follow-up question...
>
[quoted text clipped - 53 lines]
>> >
>> > =if(and(H1=0,J1=1), "DNP", "")

Reply to this Message