or statement

[bluepenink]

hello all

can someone tell me why this formula i have is not working?

=IF(OR(ROW()-12>SUM(DistrictSum),ROW()-12>SUM(AEDistSum)),"",LEFT('- "District" -'!$A$3,1)&'- "District" -'!$C$3&LEFT('- "District" -'!$A$4,1)&'- "District" -'!$C$4)

it works fine with (districtsum)

but when i include the "OR" statement for it to check in another worksheet range "AEDistSum" it does not pull all the numbers.

any suggestions?

 

[Joe4]

but when i include the "OR" statement for it to check in another worksheet range "AEDistSum" it does not pull all the numbers.

In messing around with SUMS from named ranges on other worksheets within the same workbook, it seems to work fine for me.

So perhaps there is a problem with how you have written your formula. Can you explain exactly what it is this formula is supposed to be doing?

 

[bluepenink]

hello Joe4

basically,

If either the sum from districtsum or the sum from AE Dist Sum is less than the row - 12, your formula is being applied...i think
this was my original formula with the row-12

First I created a defined name DistrictSum = X21,X31,X41,X51,X61 to simplify things.

Then in D13 of Win Sheet dragged down

=IF(ROW()-12>SUM(DistrictSum),"",LEFT('- "District" -'!$A$3,1)&'- "District" -'!$C$3&LEFT('- "District" -'!$A$4,1)&'- "District" -'!$C$4)

i THEN wanted to capture the data for another worksheet called AE district, so i applied the same concept and put a "OR"

but its only showing 1 value, when it should show 6 in total? ur thoughts?

 

[Joe4]

It seems rather odd to me that you would be comparing a SUM to the current row number the formula resides in.
That is what ROW()-12 does. It subtracts 12 from the current row number than the formula resides in.

Is that correct? Are you really comparing a SUM to a calculation on a row number, or are you trying to compare it to a value in the row?

 

[bluepenink]

hello

Basically, i now have two defined ranges:

district
DistrictSum = X21,X31,X41,X51,X61
AE district
AEDistSum = X21,X31,X41,X51,X61

each one of the above rows would contain a # (i.e. 1-10)

in my formula basically, i want to know if AEDistSum and DistrictSum contain a value i.e. 2 in total,
then show

LEFT('- "District" -'!$A$3,1)&'- "District" -'!$C$3&LEFT('- "District" -'!$A$4,1)&'- "District" -'!$C$4)
-which is basically the month and week the # is comprised in

so the 2 would total the sum from both the AE and district worksheet? do u follow? thxs

 

[bluepenink]

basically

its row - 12 b/c my data starts at row 13

so its saying

current row - 12 > sum(district) then do AS REQUIRED

it does that fine...

but when i add inthe criteria "OR"....it messes things up? any idea why?

 

[bluepenink]

pls see a sample file in the file here

https://skydrive.live.com/redir.asp...sid=5E19C3EAA0C78B45!102&authkey=7Z9ug0JH*98$

go to worksheet called "win sheet"

thx u!

 

[Joe4]

I am still not real clear. Unfortunately, I am unable to download files from the internet from my current location (corporate policy), so I cannot look at your file.

There are some tools you can use to post screen prints. They are mentioned here: http://www.mrexcel.com/forum/showthread.php?t=508133.

Otherwise, it might be best to post an example. I think we just need to following details to analyze your formula:

- What values are in cells A3, A4, C3, and C4?
- What values do SUM(DistrictSum) and SUM(AEDistSum) return?
- What row are you placing this formula in?
- What is your expected result?