Variable Range in Formula

I'm stuck on this one, hopefullly someone can help. I have the following 
formula: 

=SUMPRODUCT((B2>=$B$5)*(B2<=$B$6))

Well "B2" in this formula is supposed to a variable range, but it wont 
calculate right. So I created a bunch of different range names on sheet2 
(i.e., B1:B100, A3:A7, etc...), and in B2 of sheet1 I created a combobox with 
the validation tool for the user to basically choose the ranges I created. So 
if they choose let's say the B1:B100 range in B2, how will that work in the 
above formula?

Thanks,
~Gabe

0
Utf
5/12/2010 4:45:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
545 Views

Similar Articles

[PageSpeed] 19

Use the INDIRECT function. E.g., 

=SUMPRODUCT((INDIRECT(A1)>$B$5)*(INDIRECT(A1)<=$B$6))

The INDIRECT function will take the content of A1 as a reference. So,
if, for example, A1 contains the text 'K1:K10' Excel will calculate
the formula as if it were written

=SUMPRODUCT((K1:K10>$B$5)*(K1:K10<=$B$6))

The INDIRECT function can take any text string, built up in any manner
you desire and change it to an actual reference that can be used in a
formula.  INDIRECTs can be nested as needed, allowing you to have a
chain of formulas that determine the final reference.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
	Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Wed, 12 May 2010 09:45:01 -0700, Gabe
<Gabe@discussions.microsoft.com> wrote:

>I'm stuck on this one, hopefullly someone can help. I have the following 
>formula: 
>
>=SUMPRODUCT((B2>=$B$5)*(B2<=$B$6))
>
>Well "B2" in this formula is supposed to a variable range, but it wont 
>calculate right. So I created a bunch of different range names on sheet2 
>(i.e., B1:B100, A3:A7, etc...), and in B2 of sheet1 I created a combobox with 
>the validation tool for the user to basically choose the ranges I created. So 
>if they choose let's say the B1:B100 range in B2, how will that work in the 
>above formula?
>
>Thanks,
>~Gabe
0
Chip
5/12/2010 5:29:47 PM
Wait nevermind I think I got it, how about: 

=SUMPRODUCT((INDIRECT(B2)>=$B$5)*(INDIRECT(B2)<=$B$6))

"Gabe" wrote:

> I'm stuck on this one, hopefullly someone can help. I have the following 
> formula: 
> 
> =SUMPRODUCT((B2>=$B$5)*(B2<=$B$6))
> 
> Well "B2" in this formula is supposed to a variable range, but it wont 
> calculate right. So I created a bunch of different range names on sheet2 
> (i.e., B1:B100, A3:A7, etc...), and in B2 of sheet1 I created a combobox with 
> the validation tool for the user to basically choose the ranges I created. So 
> if they choose let's say the B1:B100 range in B2, how will that work in the 
> above formula?
> 
> Thanks,
> ~Gabe
> 
0
Utf
5/12/2010 5:42:01 PM
Reply:

Similar Artilces:

Global variables - again
Hi, When I load my first form in the system, it reads in the following parameters from my INI file. User Name the DOS Path to the Data I store these two value in a global variable and only reference them from that point on. I use the user name to read in a row from the tblUserParm table that has all of the settings for that user. I use the DOS data path to get to different sub-directories under my Access db directory. I will also use it to make automatically re-do my links, but I am not at that point yet. Is this an appropriate use of global variables? Is there...

Truncate Table Table variable
Hi, Sql Server 2005 I am using table variable with in a stored procedure and within a table variable i am using identity field. Sometimes I required to truncate the table variable to rseed the identity, but truncate table variable, dbcc checkident are not working. Any idea... Declare @Columns Table(id Int Identity(1,1), [Name] VarChar(155)); Insert Into @Columns([Name]) Select 'A' Union All Select 'B' Select * From @Columns Truncate Table @Columns Insert Into @Columns([Name]) Select 'X' Union All Select 'Y' Select * From @Columns Ra...

Excel 97
We have a worksheet to insert daily sales figures into during the course of a month. Is there a way to lock the formulas in a cell so that if someone enters the wrong info they don't erase the formulas along with the bad info? Thanks, Jeff Here's a couple of steps: Select all data input cells to be left *unlocked* (If you hold down Ctrl key, you can select multiple discontiguous input ranges with the mouse in 1 swoop) Click Format > Cells > Protection tab Uncheck "Locked" > OK Click Tools > Protection > Protect Sheet Enter password > OK Re-enter to co...

Environment variables
Are Windows XP x64 %environment variables% available to Excel and VB equations? The general answer is yes, but you need to understand that there are different views of the filesystem and registry for 32-bit programs as compared to 64-bit programs and the OS. This also affects things like ODBC sources as well. -- Charlie. http://msmvps.com/blogs/russel <johnsuth@nospam.com.au> wrote in message news:c1.2c.3KklPW$0TR@PC1.BIGPOND.COM... > > Are Windows XP x64 %environment variables% available to Excel and VB > equations? > > ...

Help with a formula #4
I am new to Excel. Using Excel 2003. Need help with the following formula: Cell I43 =If(B40="no",SUM(E16+I16+M16*2)*22+(I43*B42)+(I43*B43)+(I43*B44) What I do know is that this is a circular reference. It is showing a value of 535. The value I hoped to see was 518 (I manually did the math). Here is the referenced cells and values: Cell E16=10 I16=10 M16=0 Cell B40=no B42=10% B43=0% B44=7.75% Is there another way to write this formula? Any help or suggestions will be appreciated. Thanks -- Lynn Hi, Am sure there is a diff way to write the same formula. But assuming you want the...

TEMP environment variable
Hi, Does somebody know what happens when I write in the 'TEMP' environment variable more than one directory, seperated by a ';' (For example 'C:\windows\temp ; d:\temp')? Does the OSs (98, 2000, XP) know to use BOTH directories as a temporary directories? Regards, Ram ...

Excel 07 cell reference formula only showing literal text not value
Hi, I've used Excel before but not 2007 version. I have a simple cell reference formula in a cell B2 that I'm trying to reference the value in A2. In B2 I have =A2. And all that is displaying in B2 is the literal text of the formula, it is not returning the value in A2. I've never experience this behavior in Excel. With a simple cell reference formula it should return the value it's trying to reference not the literal text. Is there something new in 2007 I need to do? TIA G ...

Selecting & copying a named range through a listbox
When i click on a button i want a listbox to pop up. In this listbox there has to appear a list with named ranges. The named range which is selected in this listbox has to be copied Can somenone give me some code for this. That's it ! Thanxxx, Luc Since your post is kinda vague I will have to ask some questions and makes some assumptions. 1.) Are you wanting to select multiple ranges to be copied from a listbox or select a single range to be copied from a combobox? 2.) Where do you want the range copied to? Another worksheet, workbook, somewhere else withi...

Excel "pseudo" ranges
I used a tool called "Name Manager" to summarize all of the ranges in a spreadsheet. It surfaced a large number of ranges that end with the following: "_.wvu.PrintArea", "_.wvu.PrintTitles", "_.wvu.Cols", "_.wvu.Rows" below are some examples: Balance_Sheet!Z_218A505F_A146_41E5_9BD6_6A8E8D88B893_.wvu.PrintArea =Balance_Sheet!$A$1:$P$141 Bud Exp Study-ITLF'!Z_218A505F_A146_41E5_9BD6_6A8E8D88B893_.wvu.PrintTitles ='Bud Exp Study-ITLF'!$A:$B Balance_Sheet!Z_218A505F_A146_41E5_9BD6_6A8E8D88B893_.wvu.Cols =Balance_Shee...

Object variable or With block variable not set
I'm trying to put a command button on a form using the command button wizard and I am getting this message "Object variable or With block variable not set". I cannot figure out what's happening. I tried opening different instances of Access and am getting the same problem with some but not all of my .mdb files. Hope someone can help. Thanks, Laura Laura Its hard to think what it would be with no example... did you edit the code generated by the wizard? or is it the wizard producing the error? Can you paste the code here? cheers Jeff "laura" <replyto@gr...

Copy and Pasting Formulas are incrementing
Hi i am trying to paste formulas from one column to another, however when I do a paste -> special -> formulas it seems in increment the cell references with the formulas e.g the correct formula would be A1 but when I paste formulas it comes up as B1 what is going on here? "anickless" <anickless@discussions.microsoft.com> wrote in message news:FF171A6F-58D1-4D75-9311-20BF681CDCE0@microsoft.com... > Hi i am trying to paste formulas from one column to another, however when > I > do a paste -> special -> formulas it seems in increment th...

Access Control variable values in one PropertyPg from another Pro
hi all, i am unable to access the property page control values from another property page. // snippet of the code CPropertySheet dlg(_T("Hellow"), this, 0); one a; //First Property Page two b; //second Property Page three c; //third Property Page dlg.AddPage(&a); dlg.AddPage(&b); dlg.AddPage(&c); dlg.SetWizardMode(); dlg.DoModal(); //================================================ if the first property page contains the ComboBox with some values i am trying to acces the values in the second property page as one* m_Main2 = (one *) GetParent(); CString str; fo...

Creating a formula that references the formula in another cell
I would like to create a formula that references the formula in another cell and not the product. I have searched and can't find a way to do it. Thanks for your help! --- Message posted from http://www.ExcelForum.com/ Hi only possible with VBA (creating a user defined function). Would this be a feasible way for you? -- Regards Frank Kabel Frankfurt, Germany > I would like to create a formula that references the formula in > another cell and not the product. > > I have searched and can't find a way to do it. > > Thanks for your help! > > > --- > Me...

define range using col and row names; not cell name
How do you set a range using these four names? They define whole rows and columns. The first column and row, along with the last column and row, define a range first_data_col last_data_col first_data_row last_data_row I can select columns with, as an example: mySheet.Range(mySheet.Range("first_data_col"), mySheet.Range("last_data_col")).EntireColumn.Hidden =3D False I'm trying to do a sort, but I have to define the range to sort, as in ws.Range("D7:L25"), but I don't have cell names, I have 4 whole row and column names ( such as ...

Recordset in excel with variables
I have successfully figured out how to retrieve data from a SQL server from within an excel sheet. The thing I can figure out is how I can use variables in that recordset from data in a particular cell. Example : Select * from inventoryAudit *that works currently in my sheet but I need to add a variable to the query Example : Select * from inventoryAudit Where transfer_dte Between varStart And varEnd * varStart & varEnd would be dates entered into 2 cells on the sheet, than a button would be pushed that would run the query and return the results into the sheet This is what I have...

Help with a formula #2
Hi I am doing an assessment for TAFE and need some help doing a formula, so if there is anybody out there willing to help, I would really appreciate it. Using lookups, vlookups of If's - we need to work out the tax payable on the gross wage. Currently my gross wages are in Column H starting at Row 5. Below is the tax table we have to use. I can do the first part using a lookup fine, but just cannot seem to add the second part in about the 30 cents so that it work. Any help gratefully accepted. Suzie Taxation Rates for Payroll if weekly pay is then between ...

Help with formula fill down
in sheet2 i have formulas that copy information from sheet3. sheet3:sheet25 are all the same templates. How can I fill down the formulas and have them change only the sheet name? All of the formulas is allready locked with the $ sign except the sheet name. a4='sheet 3'!$I$2 a5='sheet 4'!$I$2 a6='sheet 5'!$I$2 etc... -- comotoman ------------------------------------------------------------------------ comotoman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27292 View this thread: http://www.excelforum.com/showthread.php?threadid=4727...

Select range from ActiveCell do to Lastcell
From Leith Ross 2/7/2006 Dim EndCell As Range Set EndCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp) ActiveSheet.Range(ActiveCell, EndCell).ClearContents If I change the A to an N it selects a range from ActiveCell down to last entry in Column N. My ActiveCell is in Column A but Column N may not have data down to the last cell as in Column A How do I select a range of cells from an ActiveCell in column A across to Column N and down to last data cell in column A -- Thank you Aussie Bob C Little cost to carry knowledge with you. Win XP P3 Office 2007...

whats the formula for percent in excel
help me i have a test tomorrow Unfortunately, you're asking in the wrong place. This newsgroup is for questions about Access, the database product that's part of Office Professional. You'd be better off studying your notes, but if that's not an option, try reposting to a newsgroup related to Excel. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "wolfy520" <wolfy520@discussions.microsoft.com> wrote in message news:D851F69C-0BA2-4700-8032-BC694E9B202A@microsoft.com... > help me i have a test tomorrow It is...

Variable instances
Hi, I just want to check that i have the correct understanding of the following premise. I have a Class , say Class A that i use as a base class for two other classes, say Class B and Class C. I declare instance sof each class as follows. ClassB p_BVar = new ClassB( ); ClassC p_CVar = new ClassC ( ); Each class inherits a member variable from the Base Class called bool bStatus; Now if i change this variable using p_BVar->bStaus = .... this change should not be relected in p_CVar since p_BVar and p_CVar are two separate instances and do not point to the same address. Is this a corr...

how to convert a CString variable to unsigned short array one?
Hi, In my unicode VC6 app, how to convert a CString variable to unsigned short array? ....... CString strRst; unsigned short strOleChars[100]; strOleChars = strRst; //??? Thank you. Is ConvertStringToBSTR() what you are looking for? AliR. "David" <David_Wang_Xian@hotmail.com> wrote in message news:e6gbA3avFHA.720@TK2MSFTNGP15.phx.gbl... > Hi, > In my unicode VC6 app, how to convert a CString variable to unsigned short > array? > ...... > CString strRst; > unsigned short strOleChars[100]; > strOleChars = strRst; //??? > > Thank you. > > ...

Need Help creating a formula****PLEASE
I am a manager in a big office where agents log into their phones to get calls. We keep track of how many minutes an agent is "logged off" of their phone system. I would like to be able to have a formula to use in Excel to speed up the calculations for this info. Here is what the info looks like that I am given and what I need to calculate: Sunday, December 03, 2006 Agent Login Time Logout Time # of minutes logged out Doe, Jane 6:58am 8:51am (need to find # minutes from 9:39am-8:51am) Doe, Jane 9:39am ...

data validation not enforced with a named range source excel 2002 sp2
I have a cell with the following data validation settings that still allows any cell value inspite of the validation. The drop down list works, but is not enforced. How can I enforce this validation? Settings: Allow=List Ignore Blank=Yes In-cell dropdown=Yes Source="=Comment" (Comment is a named range) Input Message: Show message...=Yes Title and message entered. Error Alert: Show alert...=Yes Style=Stop Title and message entered. Source should be =Comment I don't know if that is what you have? -- Regards, Peo Sjoblom (No private emails please, for everyone'...

Referencing Variable Name Worksheets
I'm updating a formatted worksheet every day. I have a macro that processes, lets the user augment, and then saves that days data into a new worksheet with that days date. I need to reference that worksheet the next business day to error check. I cannot : PREVIOUS DATE IS IN CELL A1 s = range("a1").value worksheets(s).select Thank you for any help/suggestions Chris Cameron You might try sheets(1) sheet1 -- Don Guillett SalesAid Software donaldb@281.com "camerons" <camerons@epud.net> wrote in message news:117nvk3b9e8mg06@c...

Excel Formula's #4
I started having this problem once in awhile and now it's almost constant. When I attempt to add a formula, any formula, to a data-filled spreadsheet, I end up with the formula itselt in the cell instead of the results of the formula. Yet, if I copy just the data I'm referencing, and not the whole sheet, onto a blank sheet and re-create the formula, I will (not always) get my results the first time and can then copy and paste the results into my full worksheet full of data. For instance, if I'm trying to use =CONCATENATE on columns F & G in a full spreadsheet, I end u...