BleepingComputer.com: Excel Formula

Greetings,

I am in need on assitantce.

It has been a long time since I've worked with Excel spreadsheets and formulas. I need to work on a spreadhsheet that was changed. All data was kept in one sheet. Now the data needs to be kept in two separate sheets IN the SAME workbook.

There are different areas in the worksheet with fomulas. All of them pretty much look at data that is coming from a worksheet called WHC Data so basically the formula with be in the second worksheet showing whatever informaiton is containt in the the first sheet.

=SUMPRODUCT(($C$27:$C$505="Dr. Ray")*($O$24:$O$505="Not Healed (next column)"))+SUMPRODUCT(($C$27:$C$505="Dr. Ray")*($O$24:$O$505="Healed"))


I can't remember how to look at data in other worksheets. If I can make this work I should be able to modify the rest of the fomulas.


Thank you

This post has been edited by boopme: 13 July 2009 - 08:55 PM

As you make the formula, just go to the other sheet (click the sheet tab at the bottom). Excel will insert Sheet name and ! before the range
=C1*Sheet1!A1
This example was just for 1 cell. C1 was on the currentSheet (Sheet2), and A1 was on the other sheet (Sheet1).

This post has been edited by tos226: 13 July 2009 - 01:27 PM

If I do that how do I then have the result for the sum ?

(($C$27:$C$505="Dr. Ray")*($O$24:$O$505="Not Healed (next column)"))

+

SUMPRODUCT(($C$27:$C$505="Dr. Ray")*($O$24:$O$505="Healed"))

The basic formula for sumproduct will be looking like this, so you can make all the pieces fit.
=SUMPRODUCT(Sheet1!C5:C9,Sheet1!D5:D9)

If you tell which of the 4 ranges you specified is on which sheet it'll help

I would post a snip picture of the sheets but I am not able to




Formula #1 Location is B10 - Spreadhsheet name --> Summary Sheet
=SUMPRODUCT(($C$27:$C$505="Dr. Ray")*($O$24:$O$505="Not Healed (next column)"))+SUMPRODUCT(($C$27:$C$505="Dr. Ray")*($O$24:$O$505="Healed"))

Formula #2 Location is C10 - Spreadhsheet name --> Summary Sheet
=SUMPRODUCT(($C$27:$C$505="Dr. Ray")*($O$24:$O$505="Healed"))/$B$13

Both of the formulas are coming for a sheet called WHC Data and the ranges are C27 TO C505 and O27 to O505

Column C contains names of people
Cloumn O contains words Not Healed (next column) or Healed depending or what the result are

In the sheet called Summary Sheet cell column A has names of people
In the sheet called Summary Sheet cell column B it would have the result from Formula #1
In the sheet called Summary Sheet cell column C it would have the result from Formula #2

Hopefullythis makes sense, I have could post a screen shot it would probably make more sense

I thought your original question was just about how to get at some data in another sheet and I assumed the writing apart from the range addresses was an explanation to the reader of the column contents.
I now think it's more complicated, and I can't imagine at this point what it is you're trying to do.
I think a screen shot will be useful, so long as you put sample of your data close together, as C and O over 500 rows are too far and too big to see on one screen.

Explain what ($C$27:$C$505="Dr. Ray") is supposed to mean/do
Likewise explain what ($O$24:$O$505="Not Healed (next column)") is supposed to mean/do
Explain what you expect the multiplication to do.

What is the relationship of Names and that Healed or NotHealed categories.
And what sort of data is in your range $C$27:$C$505
Finally what is the relationship of that range to what's in the corresponding(? Or off by 3 cells) range $O$24:$O$505

All your source data for calculations are on WHC sheet, and the Summary sheet will contain 2 calculated values, right?
Or, are you trying to fill the column B and C on Summary with what you put into B10 and C10?, why?

Perhaps someone else will better imagine what's going on. But let's see how you can help the helpers

The rows go to 500 but it doubt it that it will ever reach that number. Currentnly the information stops at row 15.

Explain what ($C$27:$C$505="Dr. Ray") --> Column C starting with Row 27 contains names of Doctors.
($O$24:$O$505="Not Healed (next column)") --> Column O starting with Row 27 Containts whether the patient was healed or not healed

So basically we would look at Column C for Dr Ray and figure out how many healed and how many not healed patients he has.


In the second sheet there is a column where all the names of the doctors are listed. Next to each name the number each in its own column would not how many patients were healed and how many not healed.



How do I attach at file or paste a picture?

Ok, we might be closer now but not close enough yet
Any row is a logical record containing doctor name, something else, and healed number and notHealed number, right?
Column C contains various doctor names, Dr.Ray being one of many, right?
There may be more than one row with Dr.Ray, right?
The reason is the patient rows belonging to Dr.Ray, right?
You want to select those rows which contain Dr.Ray in Col.C, right?
Then you want to look at column O and P for some sort of a flag telling number healed in Column O , and number notHealed in Column P - is this correct?
And you just want to present totals for each doctor on the Summary sheet, right?

If you answer the way I IMAGINE, I repeat, IMAGINE what you have and need, I'll post the answers today, tomorrow at the latest.
I don't know where multiply comes in, but VLOOKUP or MATCH() and INDEX() and/or SUMIF functions will be in the picture. We'll see.

This post has been edited by tos226: 13 July 2009 - 07:48 PM

Correct, the spreadsheet that contains the data we want has multiple columns. We need data off 2 of those columns. One columns has names of different doctors.

Since the formula pulling the data to the second spreadsheet it would list next to each doctor who is on the list (not within a formula just typed) a total number of patients. The total number would come from healed and not healed words (each word represents 1 patient).

The second formula is looking at the doctors again but this time the second part of the formulas is looking at patients that were healed.

So if Dr Gray has 6 healed and 4 non-healed the total would be 10
For the second formula it would show that Dr Gray has 6 healed.

The spreadsheet has actually 2 other areas that look at the data from the first spreadsheet pretty much the same way. I know if I cant get the above to work the rest will be simple.

This is until I get to a smal area that I am still trying to figure out what and why is the data displayed. But the end user has been on vacation for a couple of days.

Thank you for any help. Dang, explaining this is not an easy thing to do

Hey, imagining what you're trying to do isn't any easier, believe me

Checking your algorithm:
http://img520.imageshack.us/img520/5167/bcxls241201.jpg
Is this close? Meaning - is this the kind of results, logically, you expect?
Never mind how it looks, never mind where it all is, never mind how I got there.
I am trying to see what you expect, so that simple formulas can be written the way you want them.

If this is close, what you will be doing is filling you Summary doctor list with a CountIF or SumIF formula, which will say if summary.DoctorName matches Data.DoctorName, add it in. Similar for patients summary sheet.

Sorry for the delay.

I actually found a developer who know for to make formulas work.

Thank you for you time and support.