# Excel 2007 :: Why Does Using Cell References In Formula Give Incorrect Value

Sep 30, 2011

Surely it's user error, but it honestly seems like Excel is not calculating 2 numbers correctly. At work, we have office 2010 where it is not calc'ing correctly; at home I have 2007 and it's fine, but anyway, here's my situation:

Cell A1: 0.003529
Cell B1: 0.48

If i put in Cell C1: "=B1/A1" i get a value of 136.000000 But that's not correct, it should be 136.01587... But if in Cell C1 i put "=0.48/.003529" then i get 136.01587...which is correct.

Why does using cell references in my formula give me the incorrect value?

## Excel 2007 :: Named Formula Incorrect Results

Sep 4, 2012

Using Excel 2007.

I setup a named formula: Spread1 =Sheet1!\$E\$2:\$BE\$2 where I entered % values to spread a an annualized figure over 53 weeks

On Sheet 2 I entered the amount to spread in \$D\$2 and in \$E.. I entered my formula: =Spread1 * \$D\$2. Everything appeared great!

But then I deleted a few columns on sheet2 so first calculation is now in \$G\$2

Calculations: Sheet2! \$G2:\$BG\$2 (the values in I,J,k while summing to the total are in the incorrect weeks based on the spread on Sheet1, BF and BG have #VALUE! errors)

Seems odd, if I highlight "Spread1" in the formula bar and [F9] I see an array of 53 possible values and the 3 values I expect are in the place in the array (elements 5,6,7).

How I can defeat it with the possibility that a user may delete a column?

## Excel 2007 :: Formula In Worksheet Contains One Or More Invalid References

Feb 7, 2012

Everytime I save my workbook, I get a dialog box that says:

"A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a vlid path, workbook, range name, and cell reference."

First off, it say's "this" worksheet no matter what worksheet is active.

Usually the culprit is a screwed up name that has a #REF in it somewhere. Not this time. All of the formulas appear to be working fine.

Is there an audit feature that will find the errors for me? (Excel 2007)

## Excel 2007 :: Auto-Update Macro Cell References

Jan 19, 2013

In Excel 2007.

I have a macro that is set to clear a range of cells. If I make a change to the worksheet (such as adding a column) that moves those cells in the worksheet, the worksheet formulas update, but the macro cell references do not. How can I make my macro update in accordance with changes in the worksheet?

## Excel 2007 :: Crashes When Cell References Using SUMIF To Another Workfile Are Above Row 65k

Nov 28, 2013

I have 2 excel files (both .xlsx) and in the first file i am creating a reference to cells in the 2nd file using SUMIFS function. The 2nd file has 275,000 rows of data. I can create the formula correctly, however when i save the file, it just crashes every time (says not responding). If i create simple reference (=A275000) then its fine so im not sure what the issue is.

## Excel 2007 :: How To Copy A Cell Without Auto-adjustment Of Fix Cell References

Jun 15, 2014

Assume I have a cell M24 with a formula like

=M10 + \$H24 - \$I24*0.35

As you can see B10 is a fix reference (due to omitted \$) which should NOT be auto-adjusted but be kept.

Now I want to copy the formular to lots of cells below cell M24. therefore I mark cell M24 and click copy in context menu.

Then I drag/expand the blinking cell border to lets say the 20 cells below. As I result I expect e.g. in cell M25 a formula like

=M10 + \$H25 - \$I25*0.35

Unfortunately I got

=M11 + \$H25 - \$I25*0.35

So the fix reference is adjusted as well.

How can I tell Excel 2007 to NOT auto-adjust fix references in formulas?

## Excel 2007 :: How To Give Array Value To Different Comboboxes In VBA

Oct 22, 2011

I have 4 comboboxes in a formcontrol (excel 2007, vba), each combobox has the name: Combobox_01 Combobox_02 Combobox_03 Combobox_04 I want to give all comboboxes the same array values "A","B","C","D","E","F" How do I do this efficiently?, I tried a For-NEXT loop, but I am not sure how to assign the For value from 1 to 4 to each combobox and it gives me an error...

## Excel 2007 :: Variable References To Multiple Workbooks?

Jan 10, 2012

i make a lot of "trending" files at work where i link a cell to a seperate workbook where all workbooks are in the same folder.

these workbooks all have the same name with the date at the end

example:
workbook 06-01-2011
workbook 06-02-2011
all the way to
workbook 12-31-2011

usually i use find replace to change the date one at a time, but doing that 365 (x how many cells i have referencing) times is redundant

i have seein INDIRECT of the cell changes but nothing for the workbook name chaning

here is what i am trying to do:
='C:File Path[WorkbookX.xls]Sheet1'!A1
where the X WorkbookX is variable (the date at end of file name)

i have a list of all workbook names or just the dates and would like to do something like:

=D1&\$A\$1
where D1 is the full path name
or
='C:File Path[Workbook"D1".xls]Sheet1'!A1
where D1 is the date name

using excel 07 i prefer not to use a macro for how i am doing this but if a macro is the only way then i cannot have it actually open the files to pull the data. i prefer no macro since i set it up for future dates so when the new file is created it automatically updates the reference.

my end result i usually have something like A1=referenced cell from first workbook, A2= cell from 2nd book, etc. sometimes B1 would = a different cell from first workbook and so on if i am trending multiple cells from the workbooks

## Excel 2007 :: Change Source Workbook For All External References At Same Time But Avoid Browsing?

Jan 7, 2013

Need to create Working order (in Excel 2007) for filling machine in my factory. Working order is changeable from day to day and the deference is product quantity. Product quantity in working order [File - Working order] is changing according to the each sales plan [7-Jan-13, 8-Jan-13] - (File Working order is Linked to the appropriate sales plan in this case - [7-Jan-13]).

Anyway, I need to change the source workbook "sales plan" from [7-Jan-13] to [8-Jan-13] by changing source workbook name in red cell [File - Working order] and not to browsing location?

All source workbooks [sales plan 7-Jan-13, 8-Jan-13]are at the same location in the same folder, only difference is the source workbook names. So, is it possible to change source workbook name (File - Working order - red cell) for all external references (File - Working order - green cells) at the same time, and avoid annoying browsing?

## Excel 2007 :: Formula To Return First Cell Reference In Another Cell Formula

Oct 1, 2011

Version: Excel 2007 WinXP

I'm basically looking for something almost like an inverse function to INDIRECT. This function would first look at a cell's formula as a text string, parse out the first valid cell reference in A1 format, and return that cell as a text string.

Detail: I have a spreadsheet with cells that point to other values. I would like to get only the row number from the first cell reference in the formula residing in a given cell. For example:

Suppose A1 has the formula =AL267. and A2 has the formula =SUM(AL94:AL235)

I would like a formula in B1 that returns the text string, "AL267" so that I would know this is the first reference.

Ideally it could be dragged down to B2 such that it returns the text string "AL94" (and not "AL235") because AL94 is the first cell reference in A2's

Currently I am copying the formulas after hitting ctl+` and pasting that text into a text editor, followed by text operations to manipulate the results into the desired values. Any solution that didn't involve going out to notepad.

## Excel Formula To Compare Text And Give Result

May 5, 2014

I have multiple sheets in excel file. Every sheet having same information for different areas. e.g. The bug was reported. One sheet might have answer in YES and other sheet might have No. I need to get answer YES if even one of sheet contains YES for the field.

## Why Counif Formula And Filter Option Not Give Same Results On Excel

Aug 24, 2012

I am using countif formula to count number of times each name appears in a row, but when I use the filter opeion and select a specific name, and highlight the whole row, the 'count' at bottom right of excel screen does not show same count as the outcome of countif. why is this so?Shouldnt they both be the same?

## 2007, Weeknum() Returning Incorrect Number

Aug 12, 2009

I need to use weeknum() for a number of situations in a project I'm working on. But when I use =WEEKNUM(DATE(2010;1;31)) it returns 6, instead of 4 as it should. Using it on today's date returns correct number.

## Excel 2007 :: VBA - Place Formula X On Sheet Y In Cell Z?

Jun 1, 2012

Excel 2007 I have 80 very similar sheets. The only difference is the sheet name (Phase A (1), Phase A (2) etc.). I want the formula located in cell B5 to be different on every sheet.

I have listed the desired formulas on a single seperate worksheet, named "Overview". On this sheet, the following columns have these data in them:
A - Sheetname (example: Phase A (1), Phase A (2) etc.)
B - Cell location (example B5, constant)

C - A formula (example: =Sheet1!G47, =Sheet1!G48, etc.)

I would like to create a macro that will loop through all the sheets, doing the following:
1. Check if the sheetname exists in column A of the Overview sheet
2. If it does, take the formula from column C of the Overview sheet
3. And put it in the cell specified in column B of the Overview sheet

Here is some code, to illustrate the above. I know the code is mostly nonsensical.

Code:
Sub Enter_formulas()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

[Code]....

## Excel 2007 :: Formula That Counts Individual Numbers Within 1 Cell

Jun 13, 2014

For work I keep track of errors in files my co-workers make. So in one individual cell it may look like this :

OP15698
OP25687

or

OP45986
OP74563

I simply need to count how many of those file numbers there are in one cell.

Is this possible? I also use Excel 2007.

## Excel 2007 :: VLookup Formula That Checks Cell Value Before Returning Value?

Dec 19, 2011

I am trying to find a formula for a vlookup that will check one cell for any value but if that cell has no entry then it will check a different cell to return the value how do i do this?

TEAMCSS Wetherill ParkCT NAME

above is where i need it to look CSS wetherill Park is cell C1 the cell that has no entry is H1, both of these cells have validation lists in them,

OS windows XP excel 2007

## Excel 2007 :: Using Formula For Detecting Blank Cell In Closed Workbook

Jan 27, 2012

Anyone know of a way to detect if a cell is blank in a closed workbook?

Here's what I'm trying to do:

=IF(ISBLANK(closed workbook cell reference),NA(),closed workbook cell reference)

also tried:

=IF(closed workbook cell reference="",NA(),closed workbook cell reference)

Neither one worked. I'm using Excel 2007.

## Excel 2007 :: Real Time Formula Change When Cell Selected

Mar 6, 2012

I am an Excel 2007 user.

I have a formula (below) that looks only at records based on specific filtering criteria or conditions; one of those conditions is the month of the year: ('Data Dump'!\$Q:\$Q,Summary!\$A\$26). This portion of the formula would change as a month is selected.

Opposed to having to rewrite the formula every time that I want to view a specific month, I want to automate the formula to change the first condition when a user selects a month from Column A. This would be a real time, automatic refiltering and recalculation as the user selects different months from Column A

Current Formula that filters and shows only January records.
=COUNTIFS('Data Dump'!\$Q:\$Q,Summary!\$A\$26,'Data Dump'!\$D:\$D,Summary!\$A\$2,'Data Dump'!\$M:\$M,Summary!C\$2)

Column A (January is in cell A26)
January
February
March
April
May
June
July
August
September
October
November
December

## Paste Text Paragraph Into Excel And Give Each Individual Word Into Its Own Cell?

Sep 10, 2013

How do I paste a text paragraph into Excel and give each individual word into its own cell?

## Replace Cell References In Formula With The Header Of The Cells Referenced In Said Formula

Dec 22, 2009

Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1

I need to reference the formula in D1 with the cells headers names.

In a perfect world, it would take
=A1+B1+C1:

and produce:
=Bananas+Apples+Oranges

## Excel 2010 :: Tables - SUMIFS Formula With Structured References And Greater Than Or Equal To

Nov 13, 2013

SUMIFS with less than & more than

I found this thread which works for normal ranges

I want to Sum for Values Greater than a specific date in my formula.

This first one works for a specific date (Relevant reference in red), so say 01/01/2014

Code:
=SUMIFS(Table2[[#All],[Basic]],
Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])

Code:

To Sum for ranges >= 01/01/2014, I have tried this below based on the thread above but Excel does not accept this.

=SUMIFS(Table2[[#All],[Basic]],
Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])

## Replace Character Used In Formula Where Formula References Cell

Feb 23, 2008

I am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url.

i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6

this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads

I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire

## Get Cell References From Formula

Aug 8, 2007

I am trying to code the following in VBA. Excel Cells have formulae like: '=+BZ165-BZ163-BZ162-BZ160-BZ159-BZ157-BZ153'. Now I want to write code in VBA which will be able to give me the individual cells referenced in this formula: BZ165, BZ163 etc. These names can be stored in individual variables or arrays.
I will then use these for further processing. Note in cases where formula refers to data in another sheet like: '=Projects!P49' or data in another excel like '='C:Documents and SettingshoskopDesktop[Annual.xls]Quarterly'!BA\$502': I would like to store the path , file name and sheetname in variables/arrays as well.
Any pointers as to how I should approach this problem?

## How To Sort Data In Excel If It Has Cell References In It

Jun 11, 2013

I created an excel workbook with multiple cell reference throughout the various worksheets. I did this because I was creating a dependent drop down list. When I try to sort my data alphabetically, all my cell name references get changed. How I can sort alphabetically without losing my cell name references?

## A Way To Get The Cell References In A Formula Change

Sep 6, 2009

Is there a way of getting the cell references in a formula change following a change in the content of a cell in the worksheet?

## Variable Cell References Within Formula

May 18, 2009

I have a number of Cell names LR0Cost, EX0Cost, IM0cost that hold %

formula might be

## Multiple IF Formula With Cell References?

Mar 12, 2012

can Excel do a double if formula by looking at cells and not fixed type info.

EG: I have a % achieved and \$ bonus to pay: however the % achieved & the \$ bonus to pay information in the cell will change each month

So... I want the formula to say
=IF (look for cell A1 (which has %achieved) and if found use A2 (\$bonus) to give me an answer... otherwise false

The second dillema I have is that A1 has 3 ranges from 100-150, 150-200 & 200-300 to calculate with a set % in A2

## Identifying Cell References In Formula ADV

Mar 26, 2013

I am working with some fairly long formulas and I am looking for a way to highlight those cells AND work in the document. I know you can click in the cell and it will highlight them, but if you click off the cell the highlights go away. Is there a way to keep them highlighted?

## Formula That References A Cell To Deterimine A Tab Name

Jul 2, 2008

I have a "CURRENT MONTH" tab in a workbook; in that Workbook, I have a standard report that pulls Total Revenues from cell C6 of the tab I want to reference.

For example: In my "CURRENT MONTH" tab B6 has the label "CURRENT MONTH REVENUES" C6 has the formula "='May 08'!C6"

Every month I have to change that formula to "='Jun 08'!C6" as an example for June. (it's not just one formula, there are dozens referencing May that I need to change to Jun).

I could do an Edit/Replace, but I'd rather just type in the current month tab name in another cell and have the formulas pulling the data referencing that cell.

## IF Formula That References A Cell And Returns A Different Result Dependant On The Number In The Cell Being Referenced

Oct 2, 2009

I'm trying to do a formula that references a cell and returns a different result dependant on the number in the cell being referenced.

For example I've said if A1 has a 3 in it then put the word TEST as the result, plus if it has a 4 put the word RESULT.

What I wrote as my formula is as follows-

=IF(A1=3,"TEST")+IF(A1=4,"RESULT")

It works fine when I only use one result but goes wrong when I add two. If I change the words I want to show to numbers it comes up fine but with words it just returns a Value error.