Free Microsoft Excel 2013 Quick Reference

Why Excel does automatically change the formula?

Hi,

When I am using a defined name, say EndDate, in formula it works fine (i.e.
: =EndDate). But when I precede the defined name by the name of the sheet
where the cells defining the name are located, Microsoft Excel replace
automatically the name of the sheet by the name of the workbook. For example
if the name ‚ÄúEndDate‚ÄĚ refers to cells contained in the sheet named ‚ÄúRef‚ÄĚ in
the workbook named ‚ÄúMyBook.xls‚ÄĚ. If I enter the formula ‚Äú=Ref!EndDate‚ÄĚ in a
cell of another worksheet of the same workbook, Excel will automatically
replace the formula by: ‚Äú=MyBook!EndDate‚ÄĚ.

Thanks for any help.


Post your answer or comment

comments powered by Disqus
Hi,

I got a rather strange problem, I am sure this is somehow a 'feature' but an unwanted one from my perspective.

******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutI6J6=
ABCDEFGHIJK4           5 JanFebMarAprMayJun Q1Q2 6 100010001000    30000 7           Sheet1 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I am trying to enter a number for each of the months. I6 and J6 contain sum of subsequently the first and second 3 months. Once I get to april and fill in let's say another 1000. Excel Automatically changes the formula in I6 to include April.

******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutI6J6=
ABCDEFGHIJK4           5 JanFebMarAprMayJun Q1Q2 6 1000100010001000   40001000 7           Sheet1 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I know the workaround is to make the references absolute, but I was wondering why this would happen ?

How is is possible that Excel is allowed to change the formula ?

To me this is very dangerous because it undermines the integrity of the workbooks.
Is there any way to disable this kind of behaviour ?

Thanks,

Richard

Does anyone know if it is possible to assign a value to a number, say 1, that
when entered into a cell it will automatically change the formula in
corresponding cells. EG. If 1 = +10days
I enter 1 in b3, then the formual in b5 will change from +20days, to +10days
etc.

I don't even know if this is possible with excel, but would very much
appreciate any feedback possible. Thanks.

I am tracking expiration dates for training. With numerous people working
for me, tracking their training is vital, and the easiest view for all
training classes required is on Excel. I am wondering if Excel can
automatically change the color of a date that I input as the expiration date
approaches and as it passes.

I appreciate any help. If it is capable of doing this task, I cannot find
how to do it.

Hello,

I need help with my spreadsheet. I have a spreadsheet that has data ranging from January 2006 to December 2007. I graph the data from the previous month to the preceeding 12 months, in order to get a 12 month range. For example, for October, I graph data from October 2006 to September 2007. What I currently do is hide the months that I don't want to show on my graph every month.

Is there anyway that Excel can automatically change the range for me? For example, for November, can the range automatically move to graph figures from November 2006 to October 2007, without having to hide any unnecessary data?

Any help would be greatly appreciated.

Thank you!

xl_amateur

Dear all,

I need Excel to automatically change the background color of a cell. The
problem is as follows:
I have a number, say 15 in A2. I need Excel to change the background color
of b2:p2, as this is 15 cells on the right from that.

Is that possible,
All the best,
and thanks,
Martin.

Does anyone know why when I enter =SUM(A1.A22)
Excel 2003 automatically changes the formula to =SUM(A1:A22)

I would be grateful for feedback

Thanks

how can I copy a formula that does not change the column or row number?
I want to use the same formula with set column and row but when I copy from
cell to cell it goes to the next column or row

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200606/1

how can I copy a formula that does not change the column or row number?
I want to use the same formula with set column and row but when I copy from
cell to cell it goes to the next column or row

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200606/1

Excel keeps auto-changing my formulas to include other nearby cells. How can
I create and save a formula in a spreadsheet without the program changing my
formula? Each day I have to correct the formula because Excel automatically
changes it. I don't kow how to stop this from happening and I can't find
anything that helps me with this problem.

If anyone can help with this I'd certainly appreciate it.
I have a spreadsheet that shows rolling 12 months performance, with a 12 month average column which shows the current yearly average (or in some cases the yearly total).
When I update this sheet, I copy the column showing the first month and paste it on the end making it the last month.
Everytime I do this, excel changes my 12 month average formulas to one column less than it should be.
For example, I copy column C and paste it into column N.
My formula originally reads =C1:N1, but excel changes it to =C1:M1.
I went through and edited the formulas to include $ but it still changes it.
Can I stop excel from automatically changing my formulas?
The purpose of the sheet is to keep the range constant, being 12 months.
Editing copying and pasting the formula each month sure is getting old....

I have a spreadsheet. It has rows of data and at the bottom it has a summary row with totals for the columns.

Each day I add a new row to the bottom of the data. Then I ammend the formula to include the newly added data row.

For example, today the data goes down to row 106, and on row 107, there are the summaries. I insert a new row under 106 and the summary row moves down from 107 to 108. I then need to go and manually ammend the forumlas in the summary row (on row 108) from =SUM(B2:B106) to =SUM(B2:B107).

Is there anyway I can ammend the formula so when I insert a new row of data, the summary row automatically changes the formula to include the new row?

I'm using MS Excel 2007.

Thanks.

After I have finished updating my charts and open them again, Excel has
automatically changed the colors of the charts. How can I turn this option
off ?

Does anyone know how to automatically change the order of fields in a sheet

rather than using cut-paste.

Please see attachment. Thank you for your great help!

Original Field Order:
Date Acct Amount Unit Purpose Detail Obj Sub Obj Rev Sub

Rev Doc # Description Vendor Action Period

New Field Order:
Acct Period Obj Sub Obj Rev Sub Rev Date Amount Unit

Purpose Detail Doc # Description Vendor Action

Hi,

I am trying to replace a character which always appears in the 63 letter of several statements. Example : A balanced return from income and capital growth over the long~ term.

However I have tried the Find and Replace tool and also the Substitute tool, both are not recognising the character, which is ~

Can anyone think of why excel does not recognise ~ or how I can replace it ?

Thanks

I would like Excel to automatically adjust the print area for a pivot table.
As I select different options, the # of rows change and I have to manually
select the print area to receive the correct printout.

I found one other post about this and I tried to follow the instructions but
I couldn't get it to work. I think I had to write a macro but I'm not sure.
This was what the other thread suggested:

dim i as long
dim j as long
dim rng as range
set rng=range("b1..e1") ' the address of first row of data you want to
print out
j=0
for i = 1 to rng.columns.count
j=worksheetfunction.max(j,rng.cells(application.Rows.Count-rng.Row,1).end(xlup).row)
next i
activesheet.pagesetup.printarea =
rng.Resize(j-rng.row+1,rng.Columns.Count).Address

I consider myself to be an advanced user in Excel but I'm not good with
writing code. I can record a new macro, no problem --> but I'm a novice at
writing one.

If you have a solution to this problem, please write the instructions for a
"dummy". I won't take it personally

I am using Excel 2003.

--
Thanks!

When I remove "Release date:" from "Release date:24 December 2008," excel will automatically change the text to "24-Dec-08." Yes, I did try to format the column to "text" before using find and replace.

This wouldn't be a problem, except that half the dates are in Dutch, so only half the dates are changed, which means that I can't make all date formats in a column uniform.

Do anyone know how to automatically change the order of fields in a sheet

rather than using cut-paste.

Please see attachment. Thank you for your great help!

I am using Excel 2007 and often cut and paste data in number and scientific
number format. The problem is that Excel will often decide that I need to
see a large number of numbers after the decimal place and will change the
width of the columns to fit that many numbers. I am working with a lot of
data and so I would prefer to keep all of my columns at a set width of 8.43
to reduce the clutter. It becomes really annoying to have to change the
column widths back to the size that I want each time I add new data.

Can anyone please tell me how to stop Excel from automatically changing the
column width each time new data is entered?

Thank you.

Hi

I would like to create a column to list cells that would be excluded from an
average. I don't want to change the formula because I want to lock those
cells. Is this possible? The main reason I want to lock the formula is
because I want it to be clear if any values are excluded.

Example of what I have:

Columns A Text descriptors
Columns E:Z numbers to be averaged (sourced from several other sheets,
includes many blank cells, locked)
Column AA: =Avg(A:Z) (locked)
Column AB: =Stdev(A:Z) (locked)
Column AC: =AB/AA (locked)
Possible Column AD: cells that should be excluded from the average, not
locked. For example AD3: =B3 or be blank.

How could I rewrite AA and AB to take into account AD and not include those
values.

Other should have some freedom to remove outlying points based on the
standard deviation and COV but I want it to be very clear what points they
excluded and easy for others to play around with excluding different values
or no values.

Is there a VBA code to automatically change the Y Axis Title each time a cell value changes. This is for a chart with IS a worksheet (it is not a chart IN a worksheet...) (I don't know the object name of worksheet chart)...

Help greatly appreciated !

Thanks!

hi, is it possible to automatically change the color of the bar chart as soon as it reached the maximum point?...

HI Guys

I got a problem when I wrote a self-defined worksheet function. The idea is very simple: change the formula of a target cell. A simplified function is like this:
 Public Function PrintFormula(ByVal FormulaString As String) As Variant
    Dim Tar As Range
    On Error Resume Next
    Range("C1").Formula = FormulaString
    PrintFormula = Empty
End Function
if I call the function under the following sub, every is fine
Sub test()
    Range("A1") = PrintFormula("=PROPHETX|QUOTE!'QNGM8[DTN:NYMEX]?Last'")
End Sub
However, if I call the function in a cell
like: PrintFormula("=PROPHETX|QUOTE!'QNGM8[DTN:NYMEX]?Last'")
I got error, the Range("C1").formula is always empty and the error is Application-defined or object-defined error.

Do you guys have any idea about this problem? Thanks

Hi, I want to change the formula in a cell based on the entry in another cell.
eg:

(B2) has an entry of 80%

I want the formula in B3 to be: =20+5+(B12*3)

And if (B2) has an entry of 90%

I want the formula in B3 to be: =10+5+(B12*3)

Then if B3 entry is 95%

I want the formula in B3 to be: =5+5+3+(B12*5)

Can this be done? I need help as I am new to this.

Greetings,
I am now stuck trying to figure out a complex spreadsheet created by an
ex employee. If I click on Cell D34, I see the following in the formula bar
(while the cell appears empty):

{=SUM(IF(A50:A105=$D3,C50:C105))}

If I click on the formula in the formula bar to change from A105 to A120,
the
{} braces disappear and the formula appears in cell D34. Even if I retype
the
{} it still shows in the cell.

what is this and how do I change the formula while still having the cell
appear empty ?

thanks,
Darryl


No luck finding an answer? You could always try Google.