i would like to use an IF formula to query two ranges of cells (on separate

worksheets in the same workbook) but don't know how to enter more than one

range in the formula. please can you help

worksheets in the same workbook) but don't know how to enter more than one

range in the formula. please can you help

- DSUM formula using multiple criteria
- Transporting Formula Using Nmaed Ranged
- Averages formula for multiple ranges
- How can you use multiple ranges in a function?
- How can you use multiple ranges in a function?
- "IF" formula for multiple ranges
- Making range().value in macro to use multiple range
- Multiple Ranges in Pivot
- If not Intersect multiple ranges
- Conditional Sums Using Multiple Criteria
- Multiple Ranges & Conditions
- Create formula using multiple pop-up user input boxes?
- Pasting a formula in multiple cells without changing the range
- Using two ranges as ONE RANGE in countifs with conditions. Formula returns an error
- Can Excel output a formula using a range of values for multiple variables?
- Pasting a formula in multiple cells without changing the range
- Formula COUNTIF Multiple Ranges with One Criteria
- Formula using named range which is a filename
- Pivot tables using multiple consolidation ranges
- Multiple Range used for charting in Combo Box
- Count if formulas using multiple sheets
- Using Multiple Formulas
- VBA - Selecting multiple cells using named ranges
- Use named ranges in array formula

Please help.

I have found myself in the midst of massive excel project with so many constraints placed on me that I have reached my wits end.

I can not use VBA in the project. It must be accomplished strictly using formula and standard functions from Excel 2007.

I can not change the layout. I must use the poorly designed sheet as is.

It is possible I may be able to blow the whole thing up and do it right at some point, but I can't do that now.

I have resolved most of the issues through endless searching on this forum and others and through countless hours of beating my head into a brick wall.

Here is my dilemma.

On one data sheet there is a column that contains text that I would best describe as a formula concept. You can think of this as text of a formula using named ranges. So something like =(Data1/8)+(Data2/16)+(Data3). Realize that there may be as may as 15,000 records. Most of them will be blank indicating that special conditions do not apply. When there is text information there it indicates that special conditions do apply and what calculations are needed to be applied. Each formula may be unique and not applied to another record. Also records will be added on a regular basis.

Further complication is that the named ranges are limited to the scope of each of 1 of 6 calculation sheets. Each of the 6 sheets has a cell defined as data1, data2, data3 etc. that is limited to the scope of the sheet. Each of the 6 sheets may have different value of data 1, etc.

I actually managed to use the EVALUATE function in a named range to get the formula pulled to the 6 sheets and the formula resolved to the correct amounts. However when the value of the named ranged data1 changes, it does not update the value of the named range including the EVALUATE function.

I tried recalculating the sheet but that did not work. I could not get it to update the value other than by closing and reopening the sheet.

I have attached a simplified example of what I am working with. Please realize the sheet2 is actually repeated 6 times and each different sheet 2 has a ranged named data1 restricted to that sheet and that may be of different values.

I can change the naming conventions in whole or part, but I can not use VBA or change the page layout.

Thanks in advance for any ideas on this conundrum.

SAMPLE.xlsx

are in multiple ranges and I don't want to count empty cells in those

multiple ranges? For example: my numbers are in ranges A4:A11 and A16:A23 and

A30:A44. Keep in mind that some of the cells in those ranges are empty so I

don't want to count them. Thanx in advance for your help...

and return a number if it finds any instances of that number.

For instance:

I have say 6 cells, (A1, C17, F13:G14) and I want to find out if any of

them contain the number "1" and if they do, I want the result of B2 to

be "1".

The way I thought I could accomplish this was to go to cell B2 and do

the formula =if(countif((A1,C17,F13:G14),"1")>0,1,0)

This seems to work for a single range such as

=if(countif(F13:G14,"1")>0,1,0)

I just dont know the proper syntax for including multiple ranges in a

function.

If anyone knows how to include multiple ranges in a function (if its

possible), or knows a simpler way to do what I am trying to do, the

help would be greatly appreciated.

--

solinar

------------------------------------------------------------------------

solinar's Profile: http://www.excelforum.com/member.php...o&userid=31159

View this thread: http://www.excelforum.com/showthread...hreadid=508216

For instance:

I have say 6 cells, (A1, C17, F13:G14) and I want to find out if any of them contain the number "1" and if they do, I want the result of B2 to be "1".

The way I thought I could accomplish this was to go to cell B2 and do the formula =if(countif((A1,C17,F13:G14),"1")>0,1,0)

This seems to work for a single range such as =if(countif(F13:G14,"1")>0,1,0)

I just dont know the proper syntax for including multiple ranges in a function.

If anyone knows how to include multiple ranges in a function (if its possible), or knows a simpler way to do what I am trying to do, the help would be greatly appreciated.

If value of CellX is greater than 5000 and less than 10000, then CellZ value shall be 2,

but if value of CellX is greater than 10000 and less than 15000, then CellZ value shall be 3

but if value of CellX is greater than 15000 and less than 20000, then CellZ value shall be 4

but if value of CellX is greater than 20000, then CellZ value shall be 5

I hope I've made it clear. If you have any questions, PLEASE let me know. I've never worked in "IF" formulas before, so this is pretty new to me.

Thank you.

I have a macro set up with countif and sumif formulas to calculate my virtual horses race count, nomination fee, stakes returns etc. It works well but it could be improved.

I previously searched the whole cell so all the horses in my table would have their data calculated from the whole season. The problem I have is I may buy a horse mid season and would like to calculate the data based on the date I bought the horse.

I have managed to adjust my old macro so that it searches ranges as opposed to cells, but I cannot work out how to write in the code how to search for multiple ranges.

To explain further it may be I buy several horses on the same day, so the ranges from when I want to start calculating their data from will be the same. At the minute I can only work out how to change the macro to search one range. Is there a way to adjust the macro so that in my example attached the three sample horses in my table are all calculated from the same set of formulas?

I have pasted the code below to show how the macro is set up:

Sub MyHorseSearch() Application.ScreenUpdating = False Dim cell As Range For x = 3 To 5 If Cells(x, 1).Interior.ColorIndex <> 15 Then End Else With Application.WorksheetFunction 'formulas for G1 Graded racing' Range("b3").Value = .CountIf(Sheets("G1 Graded").Range("d7:d700"), Range("a3").Value) Range("c3").Value = .SumIf(Sheets("G1 Graded").Range("d7:d700"), Range("a3").Value, Sheets("G1 Graded").Range("v7:v700")) Range("d3").Value = .SumIf(Sheets("G1 Graded").Range("d7:d700"), Range("a3").Value, Sheets("G1 Graded").Range("t7:t700")) Range("e3").Value = .SumIf(Sheets("G1 Graded").Range("d7:d700"), Range("a3").Value, Sheets("G1 Graded").Range("u7:u700")) Range("f3").Value = Range("e3") - Range("d3") End With End If Next Application.ScreenUpdating = True End SubI thought that if I changed it to for example

it would calculate them but it came up 'run time error 13: type mismatch.'

I am wanting this set up because I may be tracking hundreds of horses, and if I enter formulas directly into excel cells it will really slow the sheet down.

If anyone has any guidance on where I am going wrong I would be really grateful of the help.

Thanks

Gemma

I have to keep the file in compatibility mode though, as 2003 users will need to use it.

When i use the pivot table wizard and add the two ranges i'm using and finish the wizard, it comes up with the following fields to choose from: Row, Column, Value & Page1. I want it to say: Account, Date, Details, Amount, Job, Ref etc.

The data is one account with every transaction over the whole year, it's about 90,000 lines. I want the pivot table to recognise that it is one set of data and give me the information i need, but it only shows me these 'Rows' and 'Column' choice. I want to be able to choose my data set, column and row from the headings at the top of each range (which are exactly the same)

Please help me!!

Thanks

Charissa

Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A2:A100")) Is Nothing Then Exit Sub ...........What I want to do is use multiple ranges like so:

Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A2:A100, D2:D100")) Is Nothing Then Exit Sub ...........

However, I want different vba dependant on which of the above triggers.

So.

If Range("A2:A100") triggers,

Do this

If Range("D2:D100") triggers,

Do That

Something like the above.

Or do I run two completely seperate

"Private Sub Worksheet_Change(ByVal Target As Range)" .........

In one sheet?

ECP Standard $1,199.00

ECS Committed $449.00

ECS Committed $449.00

MCS Committed $298.00

MCS Committed $298.00

MCS Committed $298.00

ECS GOA $150.00

ECS Committed $430.00

ECS Committed $430.00

MCS Standard $349.00

ECP Committed $1,000.00

ECS Standard $599.00

ECP Standard $1,000.00

ECP Standard $1,000.00

ECP Standard $1,000.00

ECS Standard $599.00

TCS Committed $175.00

So I want to be able to come up with 3 different sums based on A = either EC/MC/TC and then if B = Committed. Not sure how to solve for SUM when using multiple ranges, and conditions...

IF(RANGE $A2:$A34="MC*" AND RANGE $B2:$B34="Comm*",SUM($C2:$C34)

Hope that makes sense....

Any suggestions would be greatly appreciated.

Rich -

Smile like you mean it!

I'm trying to create a macro so that whenever i hit the keys assigned to the macro, it will bring up a serious of pop-up input boxes, so that i can enter data (both numeric and text) and then have it create a pre-defined formula using the information that was entered into the boxes.

For instance, one formula i would like to make a macro for would be =cw_act("X","Y->Z"), where X, Y, and Z would be populated by the input boxes. Any help would be greatly appreciated. Thanks!

the same throughout the sheet. When pasting it obviously changes the range

to the next cell in sequence. I'm using the following formula

=SUMPRODUCT(--(A2:A4000=70),--(B2:B4000="Product Type"),C2:C4000) and want to

kep the range A2:A4000, same for column B and C.

I have a total of 3 worksheets. The first worksheet shows record details for site A while the 2nd worksheet shows record details for site A as well, but this is more of a supplementary worksheet. The 3rd worksheet is for reporting.

What I would like:

Use a countifs formula to count values found in multiple ranges based on conditions. Please see the formula I've used in cell C2 in "reporting sheet"

=countifs('Site A Main'!A:A+'Site A sub'!A:A, $A2,'Site A Main'!B:B+'Site A sub'!B:B,$b2,'Site A Main'!C:C+'Site A sub'!C:C,$C2)

When I press enter, excel is telling me that the formula contains an error.

The reason I'm + ranges as ONE is because two worksheets are involved for site a.

Do you understand where I am going with this? Maybe countifs is not suitable here when it comes to group two ranges as ONE RANGE to apply the conditions on?

Thank you for any possible guidance.

With my previous formula there are 3 variables, and within all my calculations I will ever perfrom with this formula the 3 variables will always have a range of values between "X=1-50", "Y=1-49", "Z=1-48".

so thats 50*49*48= alot of calculations to solve (117600), Is there anyway to have excel output multiple solutions to my formula, with all the variables being between those ranges? (Im having a hard time wording this correctly, I hope it comes off right)

Im not looking for a hundred thousand rows of information in reality, but if theres a way to cutdown on the amount of actual typing/pasting whatever, I would love to know.

Thanks for any help -GK

the same throughout the sheet. When pasting it obviously changes the range

to the next cell in sequence. I'm using the following formula

=SUMPRODUCT(--(A2:A4000=70),--(B2:B4000="Product Type"),C2:C4000) and want to

kep the range A2:A4000, same for column B and C.

For example, the range is column X, AD, AJ, AP, and AV with the criteria if the cell says "Pending." I'm trying to create a summary to count how many cells in each of those columns says pending. Also, the summary is on a different worksheet tab than the data. I've tried a few different formulas, but none of them seem to work.

Any ideas?

I'm still learning how to use all the functions in Excel, so bear with me!

Thanks for the help!

Kat

If I re-write the formula as =Customers!Company_Heading it does not generate any errors but also doesn't update any changes.

There's also a formula involving Vlookup to a workbook called Price List.xls using a named range called Prices.

When Quotes runs the macro involving the two formulae it brings up a dialogue box asking me to Update Values:Prices , which I do. (It also did that when I first altered the formulae) It doesn't ask me to Update Customers.

So, to put it simply, Is my syntax correct? Why do I get the Update Links message, and how can I avoid it? and why doesn't the Company Heading bit update itself?

Any clues gratefully received

When I use multiple consolidation ranges in the pivot table wizard & enter the above dynamic ranges I would have expected all of the column headings from both worksheets to appear in the layout options for the pivot table. Instead I only get buttons for "Row" "Column" & "Value". The resultant pivot table is of no use.

Am I missing the point here or can you only do this if the column headings in the different worksheets are the same? Also, what is the "Page fields option" and "item labels" on the pivot table wizard step 2b meant to do?

Any help would be most appreciated.

Troy

I m trying to create a combo box which after selection of an option, a chart will be displayed accordingly to the selection. However, I am experiencing a problem whereby I m unable to use a range of values (Multiple range) for the chart. Can anyone help with this problem? I used the code:

VB:Thank you in advance.=OFFSET(K6,0,$H$1)If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

For example, I want to report out how many latino students (sheet 1) are reading on level (sheet 2).

Is there a trick when using multiple formulas in on cell. I need to use a sumif formula and a vlookup formula. Which do I use first? Does it matter?

I have tried both ways, but it just doesn't seem to work. Any suggestions?

Thanks,

Michael

How do I reference multiple ranges using named ranges?

Instead of selecting:

Code:

I want to utilise named ranges i.e. instead of AP2 & AS2 I want to use

Code:and

Code:

I tried the following with no luck:

Code:Any ideas?

is the current array formula as entered in cell E8.

{=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="March")*Replacemen ts!$N$3:$N$15)}

Following are the named ranges from another worksheet withing the same

workbook.

Name range for Column $C$3:$C15 = RBU

Name range for column $K$3:$K$15=RMO

Name range for Column $N$3:$K$15=RLeas

How would I rewrite the array formula to use only the named ranges? Any

help will be greatly appreciated.

TIA

Jan