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.

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

## Related Results

- Excel changes the formula without warning
- Assigning values to one cell which will change the formula in anot
- Can Excel automatically change the font color of an expired date?
- How to automatically change the range of data in a graph
- Automatically changing the background color of a cell
- Why does excel return a : when I put in a .
- Copy a formula that does not change the column or row number?
- Copy a formula that does not change the column or row number?
- Excel keeps auto-changing my formulas to include other cells.
- Can I prevent Excel from automatically altering formulas?
- Automatically change summary forumla when new row is added
- Why Excel changes colors of my charts ?
- Automatically change the order of fields in a sheet
- Replacing text-f why excel does not recognise
- Automatically Changing the Print Area for Pivot Tables
- How do you stop excel from changing the date format during find and replace?
- Automatically change the order of fields in a sheet
- Stop Excel 2007 from Changing Column Widths
- Changing the range for averages with out changing the formula.
- Automatically Change The Chart Y Axis Title Vba
- is it possible to automatically change the color of the bar chart as soon as it reach
- Change the formula of a target cell in a self-defined worksheet function
- Change the formula based on data in another cell.
- Formula Question:what is this and how do I change the formula while still having the

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

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.

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.

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

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.

Excel 2003 automatically changes the formula to =SUM(A1:A22)

I would be grateful for feedback

Thanks

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

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

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.

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....

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.

automatically changed the colors of the charts. How can I turn this option

off ?

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

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

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!

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.

rather than using cut-paste.

Please see attachment. Thank you for your great help!

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.

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.

Help greatly appreciated !

Thanks!

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 Functionif I call the function under the following sub, every is fine

Sub test() Range("A1") = PrintFormula("=PROPHETX|QUOTE!'QNGM8[DTN:NYMEX]?Last'") End SubHowever, 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

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.

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.