Define range to avoid circular reference
Is there any way to exclude a cell from a range.
For example, in Cell F10, I'd like to calculate the sum of all OTHER cells
in that row, excluding F10 itself. But SUM(A10:Z10) includes the cell F10,
and therefore generates a circular reference.
If I were doing a single cell, I could say SUM(A10:E10) + SUM(G10:Z10).
However, when I need a similar calculation for all cells in a range, this
would be a chore to create these formulas manually for each cell.
I think what I need is a range operator to define a range which excludes
single cells, something like "A10:Z10 excluding F...INDIRECT formula not working
What am I doing wrong here...
This formuals does not work:
=INDIRECT("'"&"YUNUS"&"-"&A3&"-"&B3&" "&"GD0" &"'!Q1")
where A & B are cell references used in the naming of many worksheets
I am attempting to obtain the value in Cell Q1 of worksheets:
On Thu, 8 Mar 2012 22:27:39 -0000, Yunus <email@example.com> wrote:
>What am I doing wrong h...Print range
We have 2 - 8 1/2 x 11 sheets that we put on one worksheet as one page, but print as 2 pages. Is there a way to designate 2 print ranges at one time and print the entire worksheet at one time, as two pages? It is a wide financial document, with multiple columns that extends over more than one page in width. Currently, I set two different print ranges and print the sheet twice. Anything easier? Thanks.
You could do your normal print setup one more time and this time Record a
Macro while doing it.......then in the future just run the macro and it will
do all the stuff for you automatically...Charting only Used Range with Named Ranges
All right I have been working on this for awhile and I cannot seem to
get it perfect. I have a number of charts that take daily security
information and chart it as a time series. I am trying to automate
this by using named ranges. When I do this the chart's x axis extends
much further than the data which is charted. Is there a way to use a
countif or sumproduct funtion in the offset formula to only chart the
used data? Any workaround would be appreciated.
cooter24's Profile: htt...INDIRECT
I would be grateful for any halp with this.
I am trying to reference a cell in a different workbook using the INDIRECT
function but I am returning #REF. This is the formula I am using :
='S:\Regular Procedural Reports - Test Folder AM\Credit Database 2010
Can anyone please help?
The function you'd want to use is =indirect().
But =indirect() won't work if the sending file is closed.
Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.f...Overlapping Date Ranges
I have 4 columns, 2 start date and 2 end dates. In most cases, both are the
same. In some, the start date is the same and the end date is different and
vice versa, how can I accurately get the number of days overlap?
I need so much help, but right now, some Excel help will suffice!!
Your signature talks about Excel help, but you've posted to a newsgroup
related to Access, the database product that's part of Office Professional.
If your question really is about Excel, you'd be best off reposting to a
newsgroup related to Excel.
If it's an Access question, what...indirect INDIRECT
definitely works. You got it. Thanks. One more
problem, which I'll post separately...If I use the
Indirect method, I can only be on step removed from the
value. In other words, it seems I can't use in the
Indirect argument a cell (or combination of cells) that
spell the name of the range I want. Example:
A1 = b2 b2 = 2 C2= A C3 = 1
A2 is named test
Indirect (A2) gives me 2...great
Indirect (text(c2,"#")&text(c3,"#")) gives b2, as opposed
to 2, which is what I want.
>-----Orig...Copy Range and Assign a Defined Name to the Pasted Range
I need some help with the following:
I am looking for the code which will allow me to copy a range
from one worksheet to another and at the same time assign
a defined name to the pasted area. For example, the range
A1 to A30 on worksheet 2 is copied and pasted to worksheet 1
starting in cell B30. A defined name of "result" is also created
at the same time.
The trick is the worksheets will not always be the same and
neither will the pasted ranges or the address where the
range is to be pasted.
Any help would be greatly appreciated.
Th...Question indirectly related to Money 2004/2005
I've got a question that is indirectly related to Money 2004 onwards.
I have designed a software application for recipes that has an interface
similar to Money 2004+, in the following ways:
1. UI Layout is the same (ie, Menubar, toolbar, taskpad, main panel, help
2. A tabbed toolbar like Money 2005 is also included
3. Color scheme for the menubar is similar to Money 2004
4. The task-based homepage is the same as in Money 2004, and uses the same
color scheme, style and layout. The html and css files of Money were used as
a starting/learning point.
I would also like to...How to make a SumIf range not a range ... but a sum of specific cells
Here is what I am trying to do: If D3 = AL3 then I want it to return
the sum of H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3
I know it is something simple I am missing .......
Just use If()
> Here is what I am trying to do: If D3 = AL3 then I want it to return
> the sum of H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3
> I know it is something simple I am missing .........vlookup combined with INDIRECT
I am using a vlookup to retrieve info from other files (number of hour
spent on a specific job number). I am using INDIRECT to specify th
RANGE for the VLOOKUP as I want to access many files (employe
I am using a CONCATENATE to build file names for all the timesheets fo
each employee for each time period - this is the cell that the INDIREC
function points to for the range of the VLOOKUP.
This is close to working for me except that INDIRECT requires al
external files to be open othewrwise it returns a #REF!
does anyone have any ideas on this
---------------------------...Return range from row number
I have a table from which I need to find specific values based on the rows
they reside in.
I need to search the (n)th row to return the column number where a specific
number (x) is located.
Top row of table is G29:CZ29
A1=value to match (x) from another worksheet
A2=row to search (n) from another calculation
Is there any way to achieve this by converting the known row and its
endpoints into a "range" to be inserted in the MATCH function?
e.g. if A2=93, the "range" I need to search is G122:CZ122
Thanks in advance
Hi hope you can help!
I have a number of copies/versions of the same spreadsheet (layout)
which I now have to do a column content count on ie
but I only need to count this if another total in the same column is
greater than 0 zero
I can do this with an IF statement, but of course when I copy the
formula into the other workbooks the original sheet reference
remains. I thought I might be able to use INDIRECT() but can't see if
this can be tied to an IF()
As always any help or other suggestions would be welcome.
Wherever you're specify...Export a Named range to Tab seperated text file
I would like to create a macro to export a named range in Excel to a tab
seperated text file, can anyone assist please ?
Dim bk as Workbook, bk1 as Workbook
Set bk = ActiveWorkbook
Set bk1 = Workbooks.Add(xlWBATWorksheet)
bk1.SaveAs Filename:="C:\Data\MyFile.txt", _
"Peter Kamau" <firstname.lastname@example.org> wrote in message
> I would l...Charting Date Ranges
Trying to chart a series of date ranges (sort of in a timeline-typ
fashion). Chart keeps showing beginning and end dates as two separat
bars. I need them to span the entire year showing what amount of tim
they take up.
Where am I going wrong?
| Attachment filename: chart1.jpg
|Download attachment: http://www.excelforum.com/attachment.php?postid=366530
~~ Message ...Selecting a Range
I have this formula in a cell but I want to add a condition. I want
it to average the data in question but only if:
Whats the easiest way to insert that in there?
This is an ARRAY formula that must be entered using ctrl+shift+enter AND the
ranges must be the same size.
Don't use 2000 in one 2002 in another and 2013 in a third
Microsoft MVP Excel
"JimS" <email@example.com> wrote i...Comparing a Range of Values
I have named two ranges of cells and I want to compare one range t
another to see if they are identicle. I tried =exact(range1,range2
but get an error. Can this be done
jpx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=101
View this thread: http://www.excelforum.com/showthread.php?threadid=48737
try the correl() function.
> I have named two ranges of cells and I want to compare one range to
> another to see if they are identic...Formulas SUM(INDIRECT(CONACATENTE( formula
Can anymone explain this formula in excel in simple english. Step b
step. I am looking at the imput and output tables. I am looking at th
url link with a spreasheet methodology below
You will notice this formula on one of the sheets.
I know sum is add up
Really don't know its use with the indirect function. Don't understan
I know the concatenate function is join cel...Selecting Range issue/ nightmare
Operating System: Mac OS X 10.6 (Snow Leopard)
I get "missing or illegal fields" <br>
when I select ranges in Excel. <br>
If I have first name, last name, email etc <br>
List of 1000. <br>
I miss a dozen last names I cannot select the range? <br>
I have to have a perfectly filled list every dang cell to select a range? thanks!
You don't have to have *any* cells filled to select a range. From the
complete lack of descriptive detail about the structure of your data & how
you're trying to sele...Having Excel calculate the number of times a range of values occur
I am wanting to have Excel be able to calculate the number of times that a
value within a certain range of values occur in my spreadsheet. For
instance: I am looking to see how many times a patient's blood levels of a
drug was between 10 - 20. Each time the data is collected it will be a
number with one decimal point.
Drug level on date x Number of values between 1-10
Thanks for any help you can offer.
Am Fri, 16 Oct 2009 15:56...Sum Indirect
Struggling with this indirect formula - got #ref error
A4 = sheet number
any one able to assist?
Message posted via OfficeKB.com
"BNT1 via OfficeKB.com" <u19326@uwe> wrote in message
> Struggling with this indirect formula - got #ref error
> exel 2003
> A4 = sheet number
is it possible to create a formula so it adjust automically to number of row
or columns in a range, for example if there were values in the range C1 to C5
my countif formula would automatically adjust to countif(c1:c5) and if there
values in cells c1 to c1000 the formula would adjust to (c1:c1000)
Not without code, but why not just make it c1:c5000 ?
Please rate when your question is answered to help us and others know what
> is it possible to create a formula so it adjust automi...Range compariosn
I have multiple sets of ranges (10) and I would like to show the best
of. Currently I have nested if statements to give me the "best of",
but I was wondering if there was another/better way of doing this? Any
This is what the ranges look like
rate 15 30 45 60
5 100 101 102 103
5.125 101 102 103 104
5.250 103 104 105 106
AVG(x) x x x x
rate 15 30 45 60
5 101 102 103 104
5.125 102 103 104 105
5.250 104 105 106 107
AVG(x) x x x x
x = ...Selecting Dynamic Data Range
I have been searching for awhile now to find an answer..but with n
I have 2 years of data broken down by month (columns) and manufacturin
facility (2-9 rows)..each cell represents a revenue. My boss has aske
me to give him the ability to set the start month and end month an
only that data is shown....
Here is how it is laid out:
I use a pivot table to get this format, since the initial data is muc
more complicated...any ideas how I could provide something so if m
boss only wants to see...Indirect references with VLOOKUP
RE: Excel 2007
I have a workbook, with a SUMMARY worksheet (tab), where the first column
(A) contains stock ticker symbole, e.g. XOM. In addition, there are many more
worksheets (tabs), all labelled in this way 091231, 100131, etc That is,
In all those pages I have a I have an area A2:G50, where the first column
contains a stock ticker symbol, and columns B, C, etc. have various data.
In the SUMMARY worksheet I want to callup data from the other sheets. For
example, say SUMMARY!A4 is XON
I could have =VLOOKUP(A4,'091231'!A2:G50,2,1)
and that would return...