Nested if's limit and Named formulae

This is the second time trying to get an answer, with more detail than
last time, so here's hoping

I have a cost estimate spreadsheet, comprising ten or so worksheets,
the first one of which (Details) is a summary worksheet with a changes
log at the bottom of it.  This log contains the values Draft1-10 in
column B, the Date of change for each in column C, the worksheet
affected in column D and the nature of the change in column E. 
Further up the worksheet is a field that I want to populate to show
the current version.  I can pick this up by checking to see whether
there is a date in column C and text in column E as follows.

=IF(AND(E72<>"",C72<>""),B72,IF(AND(E71<>"",C71<>""),B71,IF(AND(E70<>"",C70<>""),B70,IF(AND(E69<>"",C69<>""),B69,IF(AND(E68<>"",C68<>""),B68,IF(AND(E67<>"",C67<>""),B67,IF(AND(E66<>"",C66<>""),B66,0)))))))

All was fine until someone came along with a request for more than 7
drafts as this breached the nested if's limit.

I've tried setting up 2 named formulae as follows :

DraftTentoSix
=IF(AND($E$75<>"",$C$75<>""),$B$75,IF(AND($E$74<>"",$C$74<>""),$B$74,IF(AND($E$73<>"",$C$73<>""),$B$73,IF(AND($E$72<>"",$C$72<>""),$B$72,IF(AND($E$71<>"",$C$71<>""),$B$71,0)))))

DraftFivetoOne
=IF(AND($E$70<>"",$C$70<>""),$B$70,IF(AND($E$69<>"",$C$69<>""),$B$69,IF(AND($E$68<>"",$C$68<>""),$B$68,IF(AND($E$67<>"",$C$67<>""),$B$67,IF(AND($E$66<>"",$C$66<>""),$B$66,0)))))

but when I view them in the names window I see truncated formulae and,
what appears to be line-return characters (square box characters)
after the first and second AND statements - "&#12416;" & "S" in the 2
"formulae" below.

DraftTentoSix
=AND(Details!$E$75<>"",Details!$C$75<>"")&#12416;Details!$B$75
AND(Details!$E$74<>"",Details!$C$74<>"")SDetails!$B$74
AND(Details!$E$73<>"",Details!$C$73<>"")

DraftFivetoOne
=AND(Details!$E$70<>"",Details!$C$70<>"")&#12416;Details!$B$70
AND(Details!$E$69<>"",Details!$C$69<>"")SDetails!$B$69
AND(Details!$E$68<>"",Details!$C$68<>"")

The result is that, when I put the formula 
=If(DraftTentoSix,DraftTentoSix,DraftFivetoOne)  I end up with #VALUE!

Whereas, if I put the formula  
=IF(ISERROR(IF(DraftTentoSix,DraftTentoSix,DraftFivetoOne)),
DraftTentoSix,DraftFivetoOne) I get the right answer.

Can anyone help/explain where i'm going wrong please

Thanks in advance
Fred Newton
Zurich Financial Services
0
8/25/2004 12:29:15 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
576 Views

Similar Articles

[PageSpeed] 24

Fred,

Break your IF into separate formulas. I've put the second part of your
example into H70:

=IF(AND(E72<>"",C72<>""),B72,IF(AND(E71<>"",C71<>""),B71,IF(AND(E70<>"",C70<
>""),B70,IF(AND(E69<>"",C69<>""),B69,IF(AND(E68<>"",C68<>""),B68,H70)))))

in H70:

IF(AND(E67<>"",C67<>""),B67,IF(AND(E66<>"",C66<>""),B66,0))

Column H could be hidden.

Untested.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Fred Newton" <fred.newton@uk.zurich.com> wrote in message
news:3fb44dd8.0408250429.400a3f7d@posting.google.com...
> This is the second time trying to get an answer, with more detail than
> last time, so here's hoping
>
> I have a cost estimate spreadsheet, comprising ten or so worksheets,
> the first one of which (Details) is a summary worksheet with a changes
> log at the bottom of it.  This log contains the values Draft1-10 in
> column B, the Date of change for each in column C, the worksheet
> affected in column D and the nature of the change in column E.
> Further up the worksheet is a field that I want to populate to show
> the current version.  I can pick this up by checking to see whether
> there is a date in column C and text in column E as follows.
>
>
=IF(AND(E72<>"",C72<>""),B72,IF(AND(E71<>"",C71<>""),B71,IF(AND(E70<>"",C70<
>""),B70,IF(AND(E69<>"",C69<>""),B69,IF(AND(E68<>"",C68<>""),B68,IF(AND(E67<
>"",C67<>""),B67,IF(AND(E66<>"",C66<>""),B66,0)))))))
>
> All was fine until someone came along with a request for more than 7
> drafts as this breached the nested if's limit.
>
> I've tried setting up 2 named formulae as follows :
>
> DraftTentoSix
>
=IF(AND($E$75<>"",$C$75<>""),$B$75,IF(AND($E$74<>"",$C$74<>""),$B$74,IF(AND(
$E$73<>"",$C$73<>""),$B$73,IF(AND($E$72<>"",$C$72<>""),$B$72,IF(AND($E$71<>"
",$C$71<>""),$B$71,0)))))
>
> DraftFivetoOne
>
=IF(AND($E$70<>"",$C$70<>""),$B$70,IF(AND($E$69<>"",$C$69<>""),$B$69,IF(AND(
$E$68<>"",$C$68<>""),$B$68,IF(AND($E$67<>"",$C$67<>""),$B$67,IF(AND($E$66<>"
",$C$66<>""),$B$66,0)))))
>
> but when I view them in the names window I see truncated formulae and,
> what appears to be line-return characters (square box characters)
> after the first and second AND statements - "&#12416;" & "S" in the 2
> "formulae" below.
>
> DraftTentoSix
> =AND(Details!$E$75<>"",Details!$C$75<>"")&#12416;Details!$B$75
> AND(Details!$E$74<>"",Details!$C$74<>"")SDetails!$B$74
> AND(Details!$E$73<>"",Details!$C$73<>"")
>
> DraftFivetoOne
> =AND(Details!$E$70<>"",Details!$C$70<>"")&#12416;Details!$B$70
> AND(Details!$E$69<>"",Details!$C$69<>"")SDetails!$B$69
> AND(Details!$E$68<>"",Details!$C$68<>"")
>
> The result is that, when I put the formula
> =If(DraftTentoSix,DraftTentoSix,DraftFivetoOne)  I end up with #VALUE!
>
> Whereas, if I put the formula
> =IF(ISERROR(IF(DraftTentoSix,DraftTentoSix,DraftFivetoOne)),
> DraftTentoSix,DraftFivetoOne) I get the right answer.
>
> Can anyone help/explain where i'm going wrong please
>
> Thanks in advance
> Fred Newton
> Zurich Financial Services


0
nothanks4548 (968)
8/25/2004 1:26:18 PM
=INDIRECT(ADDRESS(MAX(ROW(C66:C72)*(C66:C72<>"")*(E66:E72<>"")),2))

entered as an array formula (control+shift+enter when entering the formula
.... if successfully entered should be displayed in the formula bar
surrounded by curley brackets, ie {=formula})

-- 
Return email address is not as DEEP as it appears
"Fred Newton" <fred.newton@uk.zurich.com> wrote in message
news:3fb44dd8.0408250429.400a3f7d@posting.google.com...
> This is the second time trying to get an answer, with more detail than
> last time, so here's hoping
>
> I have a cost estimate spreadsheet, comprising ten or so worksheets,
> the first one of which (Details) is a summary worksheet with a changes
> log at the bottom of it.  This log contains the values Draft1-10 in
> column B, the Date of change for each in column C, the worksheet
> affected in column D and the nature of the change in column E.
> Further up the worksheet is a field that I want to populate to show
> the current version.  I can pick this up by checking to see whether
> there is a date in column C and text in column E as follows.
>
>
=IF(AND(E72<>"",C72<>""),B72,IF(AND(E71<>"",C71<>""),B71,IF(AND(E70<>"",C70<
>""),B70,IF(AND(E69<>"",C69<>""),B69,IF(AND(E68<>"",C68<>""),B68,IF(AND(E67<
>"",C67<>""),B67,IF(AND(E66<>"",C66<>""),B66,0)))))))
>
> All was fine until someone came along with a request for more than 7
> drafts as this breached the nested if's limit.
>
> I've tried setting up 2 named formulae as follows :
>
> DraftTentoSix
>
=IF(AND($E$75<>"",$C$75<>""),$B$75,IF(AND($E$74<>"",$C$74<>""),$B$74,IF(AND(
$E$73<>"",$C$73<>""),$B$73,IF(AND($E$72<>"",$C$72<>""),$B$72,IF(AND($E$71<>"
",$C$71<>""),$B$71,0)))))
>
> DraftFivetoOne
>
=IF(AND($E$70<>"",$C$70<>""),$B$70,IF(AND($E$69<>"",$C$69<>""),$B$69,IF(AND(
$E$68<>"",$C$68<>""),$B$68,IF(AND($E$67<>"",$C$67<>""),$B$67,IF(AND($E$66<>"
",$C$66<>""),$B$66,0)))))
>
> but when I view them in the names window I see truncated formulae and,
> what appears to be line-return characters (square box characters)
> after the first and second AND statements - "&#12416;" & "S" in the 2
> "formulae" below.
>
> DraftTentoSix
> =AND(Details!$E$75<>"",Details!$C$75<>"")&#12416;Details!$B$75
> AND(Details!$E$74<>"",Details!$C$74<>"")SDetails!$B$74
> AND(Details!$E$73<>"",Details!$C$73<>"")
>
> DraftFivetoOne
> =AND(Details!$E$70<>"",Details!$C$70<>"")&#12416;Details!$B$70
> AND(Details!$E$69<>"",Details!$C$69<>"")SDetails!$B$69
> AND(Details!$E$68<>"",Details!$C$68<>"")
>
> The result is that, when I put the formula
> =If(DraftTentoSix,DraftTentoSix,DraftFivetoOne)  I end up with #VALUE!
>
> Whereas, if I put the formula
> =IF(ISERROR(IF(DraftTentoSix,DraftTentoSix,DraftFivetoOne)),
> DraftTentoSix,DraftFivetoOne) I get the right answer.
>
> Can anyone help/explain where i'm going wrong please
>
> Thanks in advance
> Fred Newton
> Zurich Financial Services


0
mind-the-gap (250)
8/25/2004 7:51:45 PM
Uff, that produces #VALUE! when there are no hits (desired zero)
But that is fairly trivial to fix.  Test the whole expression for ISERROR(),
but there may be a better solution.

-- 
Return email address is not as DEEP as it appears
"Jack Schitt" <mind-the-gap@DEEPblueyonder.co.uk> wrote in message
news:e8KgxztiEHA.592@TK2MSFTNGP11.phx.gbl...
> =INDIRECT(ADDRESS(MAX(ROW(C66:C72)*(C66:C72<>"")*(E66:E72<>"")),2))
>
> entered as an array formula (control+shift+enter when entering the formula
> ... if successfully entered should be displayed in the formula bar
> surrounded by curley brackets, ie {=formula})
>
> -- 
> Return email address is not as DEEP as it appears
> "Fred Newton" <fred.newton@uk.zurich.com> wrote in message
> news:3fb44dd8.0408250429.400a3f7d@posting.google.com...
> > This is the second time trying to get an answer, with more detail than
> > last time, so here's hoping
> >
> > I have a cost estimate spreadsheet, comprising ten or so worksheets,
> > the first one of which (Details) is a summary worksheet with a changes
> > log at the bottom of it.  This log contains the values Draft1-10 in
> > column B, the Date of change for each in column C, the worksheet
> > affected in column D and the nature of the change in column E.
> > Further up the worksheet is a field that I want to populate to show
> > the current version.  I can pick this up by checking to see whether
> > there is a date in column C and text in column E as follows.
> >
> >
>
=IF(AND(E72<>"",C72<>""),B72,IF(AND(E71<>"",C71<>""),B71,IF(AND(E70<>"",C70<
>
>""),B70,IF(AND(E69<>"",C69<>""),B69,IF(AND(E68<>"",C68<>""),B68,IF(AND(E67<
> >"",C67<>""),B67,IF(AND(E66<>"",C66<>""),B66,0)))))))
> >
> > All was fine until someone came along with a request for more than 7
> > drafts as this breached the nested if's limit.
> >
> > I've tried setting up 2 named formulae as follows :
> >
> > DraftTentoSix
> >
>
=IF(AND($E$75<>"",$C$75<>""),$B$75,IF(AND($E$74<>"",$C$74<>""),$B$74,IF(AND(
>
$E$73<>"",$C$73<>""),$B$73,IF(AND($E$72<>"",$C$72<>""),$B$72,IF(AND($E$71<>"
> ",$C$71<>""),$B$71,0)))))
> >
> > DraftFivetoOne
> >
>
=IF(AND($E$70<>"",$C$70<>""),$B$70,IF(AND($E$69<>"",$C$69<>""),$B$69,IF(AND(
>
$E$68<>"",$C$68<>""),$B$68,IF(AND($E$67<>"",$C$67<>""),$B$67,IF(AND($E$66<>"
> ",$C$66<>""),$B$66,0)))))
> >
> > but when I view them in the names window I see truncated formulae and,
> > what appears to be line-return characters (square box characters)
> > after the first and second AND statements - "&#12416;" & "S" in the 2
> > "formulae" below.
> >
> > DraftTentoSix
> > =AND(Details!$E$75<>"",Details!$C$75<>"")&#12416;Details!$B$75
> > AND(Details!$E$74<>"",Details!$C$74<>"")SDetails!$B$74
> > AND(Details!$E$73<>"",Details!$C$73<>"")
> >
> > DraftFivetoOne
> > =AND(Details!$E$70<>"",Details!$C$70<>"")&#12416;Details!$B$70
> > AND(Details!$E$69<>"",Details!$C$69<>"")SDetails!$B$69
> > AND(Details!$E$68<>"",Details!$C$68<>"")
> >
> > The result is that, when I put the formula
> > =If(DraftTentoSix,DraftTentoSix,DraftFivetoOne)  I end up with #VALUE!
> >
> > Whereas, if I put the formula
> > =IF(ISERROR(IF(DraftTentoSix,DraftTentoSix,DraftFivetoOne)),
> > DraftTentoSix,DraftFivetoOne) I get the right answer.
> >
> > Can anyone help/explain where i'm going wrong please
> >
> > Thanks in advance
> > Fred Newton
> > Zurich Financial Services
>
>


0
mind-the-gap (250)
8/25/2004 8:04:40 PM
Earl/Jack,

Thanks for the suggestions.  Thus far i've opted for Earl's solution
as I can get my mind around straight forward formulae, however I will
try out the Array formula solution as it's something new to learn.

Thanks again
Regards
Fred Newton.
0
8/26/2004 9:45:08 AM
Reply:

Similar Artilces:

Change Host Name
My users have trouble e-mailing to this particular domain (@aci.on.ca). I cannot e-mail this domain either. Whenever an e-mail is sent to this domain an e-mail is sent back with the following message: Could not deliver the message in the time limit specified. <server1.myinternal.local #4.4.7>. I spoke with their tech guy and his response was I have to change my host (Helo) name in my exchange to the proper entry. Right now, the host name is pointing to server1.myinternal.local. We're running exchange 2003. My question is what do I change this host name to? Thanks for an...

Pivot Table drill down and 256 character limit
When you double click on a value in a pivot table, a new worksheet tab is opened that displays the raw data used for that value. It appears that text fields in this new sheet are limited to 256 characters. Is there a way to have the entire text value show up? Thanks for the assist. Doug This is Excel 2003, SP3 Maybe you could use some sort of indicator/abbreviation. Then use =vlookup() to return the longer string???? Doug_F wrote: > > When you double click on a value in a pivot table, a new worksheet tab is > opened that displays the raw data used for that ...

As I type in the Formula Bar it blinks
I work with Excell 2002 and as I type in the Formula Bar it blinks and trembles. The problem gets worse as the text becomes longer. That makes it impossible to work with Excell. How much are you attempting to enter into the formula bar? If you exceed one line, I've found it can do that quite a bit. Are you using Excel for a lot of text? Perhaps you should be using Word instead. <-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*-> Hope this helps! Anne Troy (better known as Dreamboat) Author: Dreamboat on Word Email: Com.Piersontech@Dreamboat (Reverse...

Help with displaying formulas in charts
I am looking for a way to display zero values in my charts as gaps. Right now I there is a big slant in my chart that I am trying to get rid of. The range I am using has formular in them, so even when the cells evaluate to blanks, they are displayed as zeros on the chart. Any help will be greatly apprecaited. Thanks Two possibilities I can think of" 1) Add an IF statement, to your formula, in order to present N/A instead of 0 [ use the function NA() ] Something like that: =IF(A1*B1=0,NA(),A1*B1) -------------------------------------- 2) Hide all the rows where the cell...

How do you add name to a chart in Excel?
Hi, I am trying to add names to 3 charts, all in the same sheet But this code is giving runtime error Worksheets("Analysis").Activat With ActiveShee .ChartObjects("Chart 1").Chart.Name = "A density .ChartObjects("Chart 2").Name = "T- Distribution .ChartObjects("Chart 3").Name = "R-Distribution End Wit Any help is appreciated Thank What error? And what line does the VBE highlight? -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solution...

Formula help needed #5
lets say I have the number 770 I need a formula to help me break the 770 into pieces. I need 200 of the 770 to be multiplied by 0.120. I need 500 of the 770 multiplied by 0.10. And the remainder of the 770 which is 70 to be multiplied by 0.095. So in the end it'll look like this.... 200 KWH @ .120 24.00 500 KWH @ .100 50.00 70 KWH @ .095 6.65 can this be done? thanks Hopefully this will help: A1 is where you input your value, in this case is 770 A2 is =IF(A1>200, 200, A1) A3 is =IF(A1<200, , IF(A1>700, 500, A1-200)) A4 is =IF(A1<700, , IF(A1>...

Splitting formulas in cell
Hi, I have a cell that combines two cells from another sheet. Example: Sheet:MCD, Cell:C2 contains the following [='Monthly Calander'!$M$3&'Monthly Calander'!A11] On the first sheet 'Monthly Calander' the data is coming from both horizontal and vertical where the combined cells are simply vertical thus creating a lot of new work to try and get the data split from the 1st sheet next to the combined data on the 2nd sheet. What formula can I put in Cell D2 to get the first half of the formula in Cell C2 and then use a simliar formula in E2 to get the 2nd p...

Formula for daily/weekly hours worked
What formula do I use to calculate daily/weekly hours for employees? Hi Denise There are a few way you can do this - depending on how you store the start time and end time. Have a look at this microsoft page and it will give you some good ideas. http://office.microsoft.com/en-us/access/HA011102181033.aspx?pid=CL100570041033 Good luck -- Wayne Manchester, England. "Denise" wrote: > What formula do I use to calculate daily/weekly hours for employees? It would depend on what your database structure looks like. Post an example of your table structure and a couple of re...

Rename the default outlook folder name or change to other folder
Hi Anyone know how I can change which calendar folder outlook thinks o as the default? Either by renaming the current default or makin another of my contacts folders the default I hunted throughout the registry but cannot find where outloo defines who/what its default folders are. The reason for this is that I have a problem whereby my outlook 200 default Contacts folder was renamed to Spryte (don't ask) in th distant past. Until now that has not been a problem but our ne customer management software interacts with outlook through a interface that first determines what the default fold...

BCP Export file name
Hi. I am using BCP to export data of a table through a view as bcp db..view out D:\Export.txt -w -T -t"," -r","\n What I want is that file in which data is exported (Export.txt) is named dynamically or its name should be from value of column in a table. Let if I have a column in table with value “TEST”. The file should be named as TEST.txt instead of Export.txt Regards, Muhammad Bilal You'll likely have to do this within a script like a VB script or powershell script. Build the command line in a string and set the file name as a variable. ...

Help with decling balance formula
I am trying to setup a worksheet with this scenrioas an example: Opening Balance off $300 in Columun O6 First deduction of $30.00 in Columun N7 I want to show a declining balance as additional deductions are entered in Columun N Thank you in advance for your support. HankL Hi HankL, If the deductions in Column N are +ve, try: =O6-SUM(N7:OFFSET(N7,COUNT(N:N)-1,)) If the deductions in Column N are -ve, try: =O6+SUM(N7:OFFSET(N7,COUNT(N:N)-1,)) If you know the last possible cell in Column N that will be used for the deductions, replace 'OFFSET(N7,COUNT(N:N)-1,)' with that cell ...

Vlookup from a formula field
Hello, I would like to ask for help. Am I able to vlookup from a field? Here is what I'm doing: - on sheet two I have a lookup field (Column 1 is the percentage, column 2 is the amount paid if that percentage is reached) - in Sheet 1 I have a quota listed in A1; the actual amount reached in A2, and the percent to quota in A3 (A2/A1) In A4 I would like to have the lookup return the paid out amount based on the percentage shown in A3, however I am receiving a #N/A. The only thing I can think of is that I cannot use a vlookup for this type of calculation. Any sug...

Excel formula fill. Help needed...
Group, I are trying to fill a number of rows from a formula that is based on previously calculated data. For example, column D has values based on Column B where D3 is the sum of D2+B3. I auto filled Column D the desired number of rows and the rows filled in properly. Now, Column E is based on Column D minus a constant of 234 with the same pattern as Column D where E3 is the difference of D3-I4 (the cell with the value 234). Now, how would I fill in Column E the desired number of rows (85). Thanks, Jim =D3-$I$4 "Jim" <Jim_mace@Hotmail.com> wrote in message new...

Using A variable for form name
Hi, I have a set of tables and forms with the same names. I've populated a listbox with the form/table names. I'd like to be able to show a form upon clicking the name in the listbox or a button. I've been trying diff things like the following (this is from a button): Text15 = Me.List10.ItemData(List10.ListIndex) a = Text15.Text a.Visible = True which is not working. Is it possible to show a form using a variable? Or is there another way of going about this? Thanks! I'm assuming Text15 is supposed to contain the name of a form? As long as the form is open...

Displaying or showing a defined cell name in another cell
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Is there a way to display a defined cell name in another cell? <br><br>I would like to define a name for A1 to be &quot;_1A1&quot; and have that displayed in a different cell rather than the value of cell A1. I am ranking data based on the value in the cell, but would like to have the defined name returned in an adjacent cell: <br><br> A B C D E <br> 1 rank value cell name <br> 2 10 1 10 _1A1 <br> 3 2 ...

formula help:adding without regard to +/-
How do I tell excel to add a column of numbers without regard to +/- sign. I want to know the gross total of all adjustments, not the net. Here's the scenario. A house sells for $200,000, we make adjustments for differences, say -$1,000 here, +3,000 there, I want excel to tell me that I've made $4000 in adjustments - not +2000. Thanks for your help. Vivian =SUM(ABS(E1:E10)) entered with ctrl + shift & enter or =SUMPRODUCT(ABS(E1:E10)) entered normally -- Regards, Peo Sjoblom "Viviank" <Viviank@discussions.microsoft.com> wrote in message news:...

Problems with Import: the company name does not get imported
I am trying to import my outlook contacts in CRM. The problem is that the company name does not get imported. Am I doing something wrong? What can I do in order to fix the problem. Thanks in advance for your help, Feliks Hi! I faced a similar problem. You can export the contacts to an excell file. Then you can move the "company" column to its own worksheet. After that, you need to import the company names into CRM as Accounts. You could use either the DMF or roll your own. I chose to roll my own. Also, since I am too lazy to move columns around in a spreadsheet. (I'm ...

Formulas act like text
When I try to enter a formula, it acts as if it is text. If I try co copy from the formula line and paste to the formula line of the destination cell, the same thing happens. When I click to the right of the formula in the formula line, it says VLOOKUP in the Functions space to the far left, even though there is no vlookup function being invoked in the entire sheet? I don't even know how to describe the problem well. Any suggestions would be appreciated. Bill Any cell in which you enter a formula? Could be you are in View Formula mode. Hit CTRL + '(backquote above Tab key)...

Excel on Mac -- Where is formula bar?
My friend has a Mac is now learning Excel. She has toolbars Standard and Formatting checked. The formula bar is missing from up in the menu of the toolbar functions. Can someone tell us how to get the formula box to appear in its usual place up in the toolbar menu? She has the Fx button, which makes the formula bar appear when clicked, but it disappears when she clicks "cancel." On a Mac, how do you get the formula bar to display and remain displaying? Thanks, I thought that one of the Mac users would have answered. I have never used a Mac but assuming that it is not too di...

Excel thinks it knows the formula I want
In cell B1 type in the formula "=A1 & A2" as quickly as possible. When I do it on an XL2000 SR1 machine, Excel "takes over" the formula and returns "=A1 &1:1" after I type tye ampersand. Why is this and is it possible to turn this "smart" feature off? Tim, I've never seen that happen in any version of Excel, but it could be caused by a sticky shift key. SHIFT+SPACE is the shortcut to select an entire row, so when you type the ampersand (which requires SHIFT) and then hit space, perhaps your shift key is still down. -- Cordial...

Money Making "FORMULA"! You have to see to believe! It really works!
This is the World's Most Powerful Automatic Money-Making Mega-Sponsoring System! Make more Sales and Sponsor more people in one week than most people dream of doing in an entire year. Tap into the Magic of this Continuous Ultimate Money-Making Mega-Sponsoring System that works for you Making Sales and Signing up New Affiliates 24 hours a day, 7 days a week! Building your Business has never been Easier, Faster and More Profitable, Experience the Awesome Power of your own Money-Making Mega-Sponsoring System Today! Get plugged in Now! http://simurl.com/todgib --- MAF Anti-Spam ID:...

select and move columns by their name in header row
Hi I get a large ws with 70 - 100 cols each month. There is a header row, but the names of the cols are not always in the same order. I want to select 3 or 4 entire columns, and move them to a blank sheet in the same workbook "mdata". This code doesn't work in MS Excel 2007, but I can't figure out how to correct it. Sub cleanup() Sheets("cases-dump").Select date = WorksheetFunction.Match("Procedure Date", Rows("1:1"), 0) icd9 = WorksheetFunction.Match("Pre-op Diagnoses 1", Rows("1:1"), 0) cpt1 = Worksh...

Reference Global vars in cell formulas?
Is it possible to reference global/public vars in cell forumulas? I want to create a function that sets a global variable say true and false, and have a cell formula that has an if statement: =if([Globalvar], do something, "") I can't use a UDF because it causes other Macros I have to fail with RunTimeError 1004. It's something about UDFs not allowed to alter other cells, but the UDF wouldn't be modiying cells, but my non-UDF macros are. So, I'm experimenting with a different approach. Ideas? Hi, If you decalre the variable like this then i...

Formula as Text
This is driving me batty. I entered a formula and it is being treate as text. The expression is not evaluated. Doesn't matter what th formula is, even =A1 goes in as text. If I press F2 and the F9 th formula is evaluated and the result replaces the original entry. Help! Please! HALinN -- Message posted from http://www.ExcelForum.com hello Hal in NY, check Tools -> Options -> Calculate, is set to Autocalculate. this ma be your problem. ht -- Message posted from http://www.ExcelForum.com Hal Either you have View Formulas checked in Tools>Options>View or the cells...

Oddly named QueryDef not visible in Queries pane
I posted earlier about a database that I couldn't add to SourceSafe. For the moment, I don't really need the source code control elements, but I need to examine all the objects in the database because I'm going to rename some of the queries, columns etc. and need to know where they're referenced in the front end. I'm using some code that I found in this group that loops through all the database objects and use the application.SaveAsText to write them to a file: this bit goes through the querydefs: For i = 0 To db.QueryDefs.Count - 1 app.SaveAsText acQuery, db.Query...