Linking two workseets and cells

[jzxwdy]

Hi
I am a newbie to excel and have the following problem:

I want cell A1 in worksheet 2 to pick up the following:

1. look in worksheet 1 on row a3 to e3 and find the word (New User)
then if cell a3 to e3 contain New User pick up cell b18 which contain
a number 1 2 3 etc.

Then show the number in cell A1 in worksheet 2

Driving me crazy this is, hence working on a Saturday

Any help much appreciated.

Cheers
Chri

 

[Domenic]

Hi Chris,

Try,

=IF(OR(ISNUMBER(SEARCH("new user",Sheet1!A3)),ISNUMBER(SEARCH("ne
user",Sheet1!B3)),ISNUMBER(SEARCH("ne
user",Sheet1!C3)),ISNUMBER(SEARCH("ne
user",Sheet1!D3)),ISNUMBER(SEARCH("ne
user",Sheet1!E3))),Sheet1!B18,"")

Oh, and do try to get some R&R!

Hope this helps

 

[RagDyeR]

Try this:

=NOT(ISNA(MATCH("new user",Sheet1!A3:E3,0)))*Sheet1!B18
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi
I am a newbie to excel and have the following problem:

I want cell A1 in worksheet 2 to pick up the following:

1. look in worksheet 1 on row a3 to e3 and find the word (New User),
then if cell a3 to e3 contain New User pick up cell b18 which contains
a number 1 2 3 etc.

Then show the number in cell A1 in worksheet 2

Driving me crazy this is, hence working on a Saturday

Any help much appreciated.

Cheers
Chris

 

[jzxwdy]

Hi
Many thanks for the reply i used that for another little problem that
had, but i still have the same problem.

I did not explain myself enough applogies for that, i did get some R&
in i went scuba diving on the south coast of England, on a wrec
called the Borgany 35mtrs deep.

If you dont mind i would like to send the spreadsheet which wil
explain.

What i am after is a result in (Summary TAB cell C4) from the (PLANNE
SCHEDULE TAB Cells B18 to BS18).

So when it states New User in Cell (Planned Schedule TAB B7 to BS 7
look in cells B18 to BS18 for a numeric answer i.e. 1 5 7 etc. the
show the Answer in (SUMMARY TAB Cell C4)

Phew Oh yeah any good books that i could learn this type o
functionalty from

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=58757

 

[Domenic]

Hi,

Try,

=INDEX('Planned Schedule'!$B$18:$BS$18,MATCH("New User",'Planne
Schedule'!$B$7:$BS$7,0))

*... i did get some R&R in i went scuba diving on the south coast o
England, on a wreck called the Borgany 35mtrs deep.*

Sounds great! Although, not being a good swimmer, myself, I prefer m
feet on "terra firma".

*Oh yeah any good books that i could learn this type of functionalt
from?*

Actually, the help menus in Excel explains both these functions (Inde
and Match) well.

Hope this helps

 

[jzxwdy]

Thanks Domenic.

I have copied and pasted the formula but it would only pick up the
first cell with New User in and the number in the first cell as well,
it would not calculate the entire row/s
as per attached spreadsheet.

So i had a read of Index and Match and managed to confuse myself even
more.

I think i might have to say i cannot do this one.

Thanks for the help, it's made me relaise how much you can do with
excel given the time to learn the beast.

Chris alias JZXWDY

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=587662

 

[Domenic]

I have copied and pasted the formula but it would only pick up th

first cell with New User in and the number in the first cell as well
it would not calculate the entire row/s

Oh, I see. For every cell that contains "New User", you'd like to su
their corresponding values in B18:BS18. Okay, a change of tactics
then.

Try,

=SUMPRODUCT(('Planned Schedule'!$B$7:$BS$7="New User")*('Planne
Schedule'!$B$18:$BS$18))

Is this what you're looking for

 

[jzxwdy]

WOW!!
Fantastic, thanks a mil.

 

[Domenic]

*WOW!!
Fantastic, thanks a mil. *

You're welcome Chris

 

[jzxwdy]

*Oh, I see. For every cell that contains "New User", you'd like t
sum their corresponding values in B18:BS18. Okay, a change o
tactics, then.

Try,

=SUMPRODUCT(('Planned Schedule'!$B$7:$BS$7="New User")*('Planne
Schedule'!$B$18:$BS$18))

Is this what you're looking for? *

WOW Fantastic, it works a treat.

Am i aloud to ask another??

Cheers
Chri

 

[Domenic]

Am i aloud to ask another??

Shoot! :

 

[jzxwdy]

*Shoot! *

Here goes:

I will attach part of the document again for clarity as i did no
explain myself very well last time.

But anyway on the schedule attached i have two tabs one called AL
COURSES CORE + WORD and one called Summary Stats, what i want to do i
link the Summary Stats to the ALL COURSES CORE + WORD by doin
something like the following:

1. In the Summary Stats Cell C14 i would like to link to ALL COURSE
CORE + WORD Cell E13 if Cell B5 has New user in the Cell.

The trouble is it might have Intermediate User in the cell as you neve
know what course they are doing on a daily basis, so Cell B5 might hav
Intermediate User or regular User as well, so depending on what is i
cell B5 and so on all the way down you have to link to Cell C14 etc.

2 The yellow text box in the Summary Stats tab explains the practica
of what i do it at the moment, which is what i am trying to explai
badly

Attachment filename: book1.eds.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=58782

 

[Domenic]

Hi Chris,

First, I should have mentioned that I don't open unknown files.

Unfortunately, I'm not clear as to what you're looking for. Can yo
give us some more details?

 

[jzxwdy]

*Hi Chris,

First, I should have mentioned that I don't open unknown files.

Unfortunately, I'm not clear as to what you're looking for. Can you
give us some more details? *

Hi Dmoenic.
Sorry about the file attachment, is there anywhere i can send the
spreadsheet as i cannot explain it?

If not, no worries you've a great help and i really apreiciate it.

Let me know

Many Thanks
Chris.

 

[Domenic]

Hi Dmoenic.

Sorry about the file attachment, is there anywhere i can send th
spreadsheet as i cannot explain it?

If not, no worries you've a great help and i really apreiciate it.

Let me know

Many Thanks
Chris.

Hi Chris,

Just wanted to let you know that I sent you a Private Message earlier.
I thought I would mention it just in case you don't have automati
notification and don't check for PMs regularly

 

[Domenic]

Hi Chris,

Try,

=SUMPRODUCT(('ALL COURSES CORE + WORD'!$B$5:$B$25="New User")*('AL
COURSES CORE + WORD'!$E$5:$E$25="n"))

This will count the cells in Column B that contain "New User" whos
corresponding cells in Column E contain "n".

Notice I've changed the range for Column E. I assumed the range yo
stated was a typo. Also, I changed it to an absolute reference.

I would love to get a list of similar formulas to edit, any idea
where one might find them.

I'm not sure. I'm learning through the different forums and newsgroup
available. Unfortunately, Excel's help menu doesn't explai
SUMPRODUCT's use in this manner.

But you can lookup array formulas which would accomplish the sam
thing. For example:

=SUM(IF(($B$5:$B$25="New User")*($E$5:$E$25="n"),1,0))

which would have to be entered using CTRL+SHIFT+ENTER. And you'll fin
other variations to this. Hope this helps!

Cheers

 

[Domenic]

Hi Chris,

Try,

=SUMPRODUCT(('ALL COURSES CORE + WORD'!$B$5:$B$25="New User")*('AL
COURSES CORE + WORD'!$E$5:$E$25="n"))

This will count the cells in Column B that contain "New User" whos
corresponding cells in Column E contain "n".

Notice I've changed the range for Column E. I assumed the range yo
stated was a typo. Also, I changed it to an absolute reference.

I would love to get a list of similar formulas to edit, any idea
where one might find them.

I'm not sure. I'm learning through the different forums an
newsgroups available. Unfortunately, Excel's help menu doesn't explai
SUMPRODUCT's use in this manner.

But you can lookup array formulas which would accomplish the sam
thing. For example:

=SUM(IF(($B$5:$B$25="New User")*($E$5:$E$25="n"),1,0))

which would have to be entered using CTRL+SHIFT+ENTER. And you'l
find other variations to this. Hope this helps!

By the way, I seem to be having problems with PMs, so let's forge
about using it.

Cheers

 

[jzxwdy]

Hi Domenic.
I have inputted:
=SUMPRODUCT(('ALL COURSES CORE + WORD'!$B$5:$B$24="New User")*('AL
COURSES CORE + WORD'!$E$5:$E$24="n")) and although i have put n in
timesin the cells it just picks up 1 as a count?

I also tried:

=SUMPRODUCT(('ALL COURSES CORE + WORD'!$B$5:$B$100="Ne
User")*COUNTIF('ALL COURSES CORE + WORD'!$E$5:$E$11,"n"))

And in the summary tab cell C14 i get as above 1 when i have put the
in 7 times again.

Cheers
Chri

 

[Domenic]

Hi Chris,

Make sure that the text you entered in your columns exactly match th
text you're using in your formula.

Look for leading or trailing spaces, or even an extra space betwee
the words "New User".

You can also try doing a simple COUNTIF for each column and see if yo
get 7 for each of them.

Let us know if you're still having problems.

Cheers!