There's a couple of things that can be done.
But it depends on what "control" you have over the other worksheet.
Can you "name" the range on the other worksheet?
For example, in the Budget_Estimation_Tys.xls file, Sheet1, name
cell B2 "Groceries"
Then, cell ## "Insurance" (or whatever)
Then in your formula, when you select the cell in the other sheet,
Excel will "automatically" use the range name, and your formula becomes:
=IF($H$2<='C:\Documents and Settings\All Users\Documents\Budget
\Finances\2010\[Budget_Estimation_Tys.xls]Sheet1'!Groceries,"Within
budget","Over budget")
Now, the problem is with having the formula "update" when you change your
pull-down.
you COULD change the filename reference to:
"'C:\Documents and Settings\All Users\Documents\Budget
\Finances\2010\[Budget_Estimation_Tys.xls]Sheet1'!" & G2 (assuming your
pull-down is in G2)
but then, you're changing the "IF" comparision so that it's comparing
a number to a string of characters.
What you have to do is tell Excel that the string is "indirectly" referring
to a cell location.
You do that by using "Indirect"
so:
=IF($H$2 <= indirect("'C:\Documents and Settings\All Users\Documents\Budget
\Finances\2010\[Budget_Estimation_Tys.xls]Sheet1'!" & G2),
,"Within budget","Over budget")
should work..
(although I don't know if you need the single quotes ('))
I'd first try the formula using named ranges, then
replace the named range with the Indirect() function.
Paul
----- Original Message ----
> From: Susan <susan.m.ander...@comcast.net>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Wed, October 13, 2010 11:36:04 PM
> Subject: Re: $$Excel-Macros$$ Incorporating a List into a formula - Excel 2007
>
> Here is my formula
>
> =IF($H$2<='C:\Documents and Settings\All Users\Documents\Budget
> \Finances\2010\[Budget_Estimation_Tys.xls]Sheet1'!$B$2,"Within
> budget","Over budget")
>
> H2 is the Average per month calculation , this is correct and the
> formula is working correctly for averaging.
>
> B2 is the estimated budget amount for Groceries. How do I get Excel
> to recalculate the formula that will change B2 to the next cell
> reference that matches what I've selected from the drop down list from
> the other file?
>
> Maybe it cannot be done because I'm using two different worksheets?
>
>
>
>
>
> On Oct 13, 8:41 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> > A lot is going to depend on what your data looks like and what
> > you're trying to accomplish.
> >
> > For instance....
> > To ME, when you say that you want the "formula" to change,
> > my first interpretation is that if you select "Groceries",
> > you want the average, but if you select "Utilities",
> > then you want the sum... because THAT (to me) is the "formula".
> >
> > If what you really mean is that you want the CRITERIA to change..
> > well that's different...
> >
> > Let's say that you have a pull-down list of criteria in cell H1:
> > Groceries
> > Utilities
> > Mortgage
> > Insurance
> > Auto
> >
> > and you have a list in A2:B100 that has things like:
> > Category Amt
> > Auto $53.00
> > Utilities $66.00
> > Auto $141.00
> > Utilities $92.00
> > Utilities $45.00
> > Insurance $168.00
> > Auto $202.00
> > Auto $184.00
> > Groceries $207.00
> > Auto $65.00
> > Groceries $119.00
> > Auto $166.00
> > Auto $172.00
> > Utilities $173.00
> >
> > Now, if you're wanting to calculate the average of the selected criteria,
> > you could use:
> >
> > =ROUND(SUMIF(A2:A31,H1,B2:B31)/COUNTIF(A2:A31,H1),2)
> >
> > is this the kind of thing you're looking for?
> >
> > Or am I totally off-topic?
> >
> > Paul
> >
> > ----- Original Message ----
> > > From: Susan <susan.m.ander...@comcast.net>
> > > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > > Sent: Tue, October 12, 2010 3:36:04 PM
> > > Subject: $$Excel-Macros$$ Incorporating a List into a formula - Excel 2007
> >
> > > Hi,
> > > wondering if someone can help. I'll try to be as specific as I can.
> >
> > > I want to have a formula that will change according to the Category
> > > word I select from the drop down selection function. Otherwise I have
> > > to copy and paste the formula I have now and manually enter in each
> > > different category word.
> >
> > > the formula I have now is an AVERAGE of two cells (using the same
> > > category word lets say Groceries), within the same sheet and I want
> > > the formula to change when I select from the drop down selection
> > > function from the category (LIST) cell.
> >
> > > thanks,
> > > Susan
> >
> > > --
> >
>>----------------------------------------------------------------------------------
>
> > >-
> > > Some important links for excel users:
> > > 1. Follow us on TWITTER for tips tricks and links :
> > >http://twitter.com/exceldailytip
> > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
> >
> > > To post to this group, send email to excel-macros@googlegroups.com
> >
> > > <><><><><><><><><><><><><><><><><><><><><><>
> > > Like our page on facebook , Just follow below link
> > >http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&;...
>
> --
>----------------------------------------------------------------------------------
>-
> Some e important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
