Offset() returns reference, first not value (proof)
In cells B10:D10 I have 111, 222, 333
In Cell D4 I have 3
In cell D6 I have =sum(b10:Offset(b10,0, D4-1))
which displays 666 << which is correct.
But if in the formula bar (on cell D6) if I highlight
(evaluate) the portion Offset(b10,0, D4-1) and
press F9 it equates to 333
Showing =sum(b10:333) << Which of course is not
a valid formula
So "D10" is being returned, versus the Value of D10
Just thought I'd pass along this recent (todays) enlightenment to those in
the group that didn't...Description and reference charactors not enough
Description and reference charactors are not enough. With more pressure on
finance departments to deliver accurate and complete information this is a
big problem within GP. Especially when journals and othertransactions are
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the m...Pivot Table VBA Reference Book & Blank Cells
I have a Pivot Table in Access 2003 and need the blank/null cells on the
data axis to display "0". This is easy to do in Excel but I have searched
through all of the options in Access and cannot find it.
What is the VBA code required to set this option?
Can anyone suggest a good reference book or website for coding Pivot Tables
...dynamic reference to data in multiple closed workbooks
Hi, I'm working with a job list (generated out of a different program) and
data in associated closed workbooks. For example, the job list has names like
HD-100311-TA031110, SHT-100312, 032110Mag and I can generate a list of these
jobs along with other information I need. For every job there's an associated
workbook and, without opening the workbook I want to pull, for example,
'[\\de-mt1\clients\QC Reports\Proofs Adjusted\HD-100311-TA031110 proof
adjusted.xls]'Upload'!A$2 where the only information that will change is the
job name. I'm novice enough not to...Circular Reference Warning ???
I receive a this message 'Circular Reference Warning'...One or more formulas
cantian circula reference and may not calculate correctly.
How is this happened? I checked all the formulas I think they're okay. Am
I missing something? How I do fix it? and how do I clear this message
Circular Reference warning?
Your help is appreciated?
When the warning comes up it gives you some tools to trace the error.
Circular reference error are no always obvious to find but if the warning
came u pthe error is there somewhere.
...Worksheet reference ?
Hello, Is there any way when copying a worksheet to update
the reference from another worksheet
ex: sheet2 as a formula =Sheet1!A1+1, when i copy the
sheet2 in the new worksheet sheet3 the formula is the same
can we makem a reference to the last sheet or update the
sheet +1 to be automatic =Sheet2!A1+1
Note - wb must be saved for this to work.
>Hello, ...Indent/Outdent Circular Reference Error
My indent/outdent function is not working for new or existing tasks. When I
select indent / outdent I receive the following error:
"This outline change would create a circular relationship.
Indenting these taks would create an illogical relationship with other tasks.
Check the task dependencies for the tasks from which you are indenting, and
then try again."
I am confused because this error will even impact new tasks which are not
linked. Linking (predecessors/successors) does not seem to be a factor.
I am not linking to summary tasks in the .mpp.
...How to refer to active cell on spreadsheet?
Excel 2000 on Windows 2000
I'm looking for functionality similar to "ActiveCell" in VBA. I have
week-ending dates in Column A. Columns B through H are the days of the
week, Mon - Fri, so cell B1 = "Mon", C1 = "Tue", etc. I want B2 to be
whatever the date value in Column A in the current row is, minus 6 (my
weeks end on Sunday.) So if my active cell is B6; A6 = 1/19/03; and
then I want B2 to read 1/13/03 (Monday's date), or 1/19/03 - 6.
A B C D E F G H
--------------------------------------------------...relative references when copying Sheets containing form controls
I've been given a spreadsheet that I need to take copy various sheets
from, the sheets contain form controls, when I copy the sheet to a new
workbook, named ranges and so on come across fine, but the references
to the input ranges of for example the drop down box controls become
hard coded to the original sheet, I don't want this..
Is there anyway to alter the way excel copies so that the references
remain relative rather than absoloute????
What did you include in the addresses for those references?
I put a listbox from the forms toolbar on a worksheet.
If I used a range like: $b$...help with previous sheet reference formula
Subject: excel formula
From: "TJ" <firstname.lastname@example.org> Sent:
4/28/2004 11:30:51 AM
trying to create a payroll spreadsheet using "sheets" as
payperiods. Having trouble creating incrimental increase
of accrued Vacation Sick and Comp without re-entering
data on each sheet. need a formulas for the following:
"previous sheet value" + or - "Accrued time" equals new
I have 26 Sheets representing pay periods. I need a
"previous sheet reference" instead of a specific sheet
reference. Please help
t...Creating a Bar Graph with a reference point
I would like to create a Bar Chart/Graph (horizontal) that would have
two sets of values and one category group. I would like to display
this as followed: value set 1 to be a bar and the value set 2 to be a
reference dot for value set 1. Say my Data looks like the following:
CatergoryGroup Val1 Val2
Month1 1 4
Month2 2 3
Month3 7 5
Again, I want Val1 to be bars. Val 2 I want to be dots on the same
I looked at the Column Charts and there is the "Plot Data as Li...Named range row/column reference
I am using named ranges in a VBA macro
StudentNbr is A26:A45
TestWeek is E25:X25
TestScores is E26:X45
As you can see this is a grid of data with student numbers down th
left side, test week numbers across the top and the grid containin
I would like to update the cells in TestScores based on the positio
of the values in the StudentNbr and TestWeek named ranges using:
Range("TestScores") (vStudentNbr,vTestWeek).value = vTestScore
*Is there a way to get vStudentNbr and vTestWeek as relative reference
to the beginning of their respective named ranges? *...Apache Axis and Visual Studio .NET Web Reference
I noticed that adding in Visual Basic .NET a Web
Reference to an Apache Axis web service results in a URI
in the generated code that no longer has a port number. I
tracked down the problem to the WSDL file created by
disco.exe from Axis WSDL URL: even though the Axis WSDL
contains the port number in the soap:address, the WSDL
created by disco.exe no longer contains the port number
Is this a known problem? Has a fix been issued that
solves this problem?
Thanks you for your assistance in this matter.
Georges, what version of VB.NET are you using?
I ...Absolute absolute cell reference
Is there a way of defining a cell to reference (eg. just
equal) another cell absolutely, even if rows or columns
are inserted in between.
Cell A1 you define =C1
If you insert a column between A and C I still want A1 to
reference the new C1 not D1
($s don't do this)
Use the OFFSET function. Try this in A1:
"jon west" <email@example.com> wrote in message
> Is there a way of defining a cell to reference (eg. just
> equal) another cell absolutely, even if rows or columns
> ...cross reference two
I'm need to write a formula based on information in a table - as an example:
High Performing Location Market Area Non-Market Area
1 $1,000,000 $750,000 $500,000
2 800,000 600,000 300,000
3 550,000 400,000 200,000
I want to lookup the information in the top row and on the left column, then
multiply by a factor referenced in ...Can't open file: "The .VBP file for this project contains an invalid or corrupt library references ID"
I'm running Microsoft Excel v.X on a Mac with Panther (OS X). An Excel file
which I used a lot now cannot be opened - it says "The .VBP file for this
project contains an invalid or corrupt library references ID". I have no
idea what this is - it's a single file with just 2 sheets in it, and is
fairly simple - no links to outside objects, no graphics, etc. Just a single
file. What can I do to get it opened?? Microsoft Word can read it, but I'd
like to keep working on it in Excel... Thanks in advance!
I have a worksheet for each month, and reference my ending balances in the
previous month in the current month. I now want to create a summary
worksheet and need to reference the current months balances. I'd like to
create a reference whereby I can replace the "month" portion of the name,
using a cell on the summary sheet.
example: tab names are AB_nov_2009, AB_dec_2009 etc.
my reference would be: =AB_nov_2009!b4
I'd like to use a cell to change the nov to dec without having to go to each
cell and change the month.
Do you want to start with A...Don't want to type domain reference in OWA @ log on
Is there a way to not type in the domain reference in OWA when the
user is logging in? My users are confused when logging on from 2000
Yes, You can!!
ESM\Frist Administrative Group\Server\Exchnge Server\Protocol\HTTP\Exchange
Virtual Server\Exchange right click proterties\Access\Authenticate in the
Default Domain,pls type your domain name after restart your default web
site(using IIS Admin MMC Tool)
"ctrlgrid" <firstname.lastname@example.org> ���g��l��s�D
> Is there a way to not type in the domain re...cell reference of a formula
I am trying to get Excel to "know" which cell a formula is in and then count
a certain number of rows and columns to find where the data that needs to be
used in the calculation is.
For example if I have data that needs to be used in the calculation in C2:C6
and the formula is in B7, I need Excel to count one column to the right and 4
rows above from B7 to find the data and work out the result of the formula.
I can do the part about counting the rows and columns, but I can't find how
to get Excel to work out the cell reference for the formula cell. Is this
poss...XML Document reference between asp.net pages
I have just started working with XML documents. I have just built a
form that creates an xml document and it sends it to biztalk where i
get an xml document as a response. i read a node value from the xml
document and depending on the result i open a new page.
I was wondering what the best method is for referencing the response
xml document created on the previous page??? i.e. should i save it or
cache it or what ??? As i say im quite new to this so any help greatly
...Formula to reference another worksheet, locate data, then record i
It has been over 10 years since I did my Excel studies and I've
unfortunately forgotten everything I haven't used regularly. My office has
recently upgraded to Office 2007 (upgrade not being the descriptor I'd have
chosen!) and I'm struggling with Excel. I've found my way around most issues,
but I'm REALLY stuck now and suffering Friday-itis on top of it all!
Essentially I have a multi-sheet workbook for my debtors ledger. Each page
has separate columns for the customer numbers, names, total debts in each age
range (7 days, 14 days etc), totals and lastl...data refresh cell reference
I have a workbook that has several different spreadsheets, each with
different query that gets refreshed through ODBC when the workbook i
used. Each query requires that a date range be entered.
It will always be the case that the same date range will be entered fo
each of the 6 queries.
Question: is it possible to have the query reference specific cells i
the workbook, so that my users don't have to enter the same "To" an
"From" dates six different times? In Access I'm able to referenc
values on forms, but the same logic doesn't seem to apply with ...How to reference to a part of the XML doc?
I have this structure:
Is it possible to refactor this at text doc level to something like this:
<reference to the repeated_part>
<reference to the repeated_part>
Pls note: I want parse this DOC and...Access VBA Object Reference
The problem that I am having is that some computers in our office
office xp and others have office 2003. I made and access database
that has a bunch of vb behind it in office 2003. When I got to run
in office xp it blows up. Under the references tab it says that it
MISSING:Excel 11.0 Object Reference Library. Is there a way to use
that library under office xp.
Thanks in advance.
Excel11.0 Object Library means Excel2003. The same library for OfficeXP is
10.0. If you want to have your access application to work with multple
version of MS Office, then you need to devel...Help, change range names back to cell references?
Created a worksheet and named many cells for formula purposes, but now I
want to duplicate the worksheet for other jobs. When I create the new
worksheet, the formulas retain the range names and refer back to the
originating worksheet not the current one. What's the easiest way to change
all the formula's to refer to the urrent worksheet. Deleting the name screws
up the formulas.
I think you just think it is referring to the same name. If you used a
regular copy|move sheet to make a copy of the sheet in the same workbook, it
also kept the named ranges on tha...