Held dragging INDIRECT FUNCTION
Im using this function.........
INDIRECT($A$2 & "!Q1")
When I drag it down a column I want the Q to increment.
If anyone can help me with this it would be much appreciated!
When copied down will return references to:
"ChronicTiger" <u17652@uwe> wrote in message news:5a5b756ad125f@uwe...
> Im using this function.........
> INDIRECT($A$2 & "!Q1")
> When I drag it down a column I want the Q to i...Excel Function Reference
Any good URL that can help me understand excel functions and has examples?
Me, have a look at Peter Noneley Excel Function Dictionary, you can found a
link on Ron de Bruin site here
http://www.rondebruin.nl/id.htm at the bottom of the page.
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
"A P" <email@example.com> wrote in message
> Any good URL that can help me unders...Is there a quick reference guide for excel 2003 -2007
I think I have seen one somewhere but can't find it. Is there is a reference
guide showing how to find find things in excel 2007 that are not in the same
place as they are in 2003?
you can give this a try
of download this xls workbook
"Noella" <Noella@discussions.microsoft.com> wrote in message
>I think I have seen one somewhere but can't fi...Absolute Cell Reference Across A Range
Can anyone tell if there is a means of setting a range of cells (fo
example a1:c20) as absolute cell references without going into eac
cell and F4-ing.
I'm copying and pasting a bunch of tables where the whole table need
to have absolute references, and it's a pain in the backside having t
go through each cell and press F4!
Any help would be greatly appreciated,
SamuelT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2750
View this thread: http://www.exc...Image Reference Disc ?
I have a monthly calendar that has, on the bottom row, two image links (I assume) to get the prior month and next month days.
When I installed Excel, I had an F disc, but I do not remember making any reference to it in the installation. I want to remove this partition (the F disc), but when I do, the above mentioned months show a box with a red X.
I have a folder on my F disc labeled Temporary Internet Files, which I'm guessing Excel is using in some way, which I find strange because I would have thought the prior and next month days would have come with the spreadsheet.
Is there any w...Changing worksheet references
After copying worksheets from one workbook into a new one the cell references
in the new workbook refer to values in the old workbook A typical cell
reference in the new workbook might look like ='C:\Some
Directory\[SomeWorkbook.xlsm]SomeWorksheet'!$AQ$4'. The correct reference in
the new workbook should be =SomeWorksheet!$AQ$4.
There must be several hundred such references in the new workbook. Is there
some way, other than manually, to correct the references to the current
Make sure the new workbook is saved. Then, you shoul be able to ...Using INDIRECT function to specify source data
I have a simple line chart that plots monthly data points from a stock index
over time. Each month I have to manually modify the range of cells that are
the source data to include the new month's number.
Question - Is it possible for me to use the INDIRECT function to avoid the
manual update by having the ending cell reference consist of another cell
whose value automatically changes to the new ending row number when the date
changes to the new month? Here's an example to illustrate:
Current manual method:
- "Value" field in source data
='SheetName'...using text in a cell as part of a reference
i have an excel file with 119 worksheets.
each worksheet has a certain name.
worksheet 50 is named "dogs"
on worksheet 1 in cell A1 is the text "dogs"
on worksheet 1 i want cell B1 to =dogs!j20.
is there a way i can automate this by making B1 =(text in A1)!j20?
an answer to this would save me so much time and i would be ever s
Message posted from http://www.ExcelForum.com
=INDIRECT(A1 & "!J20")
lsu-i-like <<firstname.lastname@example.org>> wrote:
>i have an excel file with 119 worksheets.
>each worksheet has a cer...Conditional formatting using indirect reference and "AND"
When the same are used in conditional formatting, there is no change
in appearance of one cell or the other. I am trying to format
conditionally some cells based on the values in another sheet. I can
do this with the indirect statement, but not in combination with AND.
Why is this?
When the folowing formulae are entered into a cell in excel...
This yeild the value "false":
This yields the value "true":
=AND(M1>INDIRECT...Problems with external references when creating a drop down list
I have a large spreadsheet with several large drop down lists. The lists are
all in a separate workbook so I have defined a name with an external
reference to the lists. The problem is that the drop down lists will only
work when both spreadsheets are open but I don't want to open the spreadsheet
containing the lists everytime as it is a really large workbook. How can I
get the drop down list to work with just the workbook open where I have
validated the cells?
Write 2 macros..
in the workbook that is the 'source' of the dropdowns, have it export the
list to a ge...Macro to reference to a cell in indiv sheets
I have a column A that lists the name of all the sheets
in my workbook. I'd like to have column B equals to cell
C10 of the corresponding sheets in column A.
I already have a macro to list all the sheets in my
workbook so I'd like this macro to update column B at the
same time I run the macro.
Assuming your worksheet list starts in A2:
You could do this without macros by entering:
B2: =IF(A2<>"",INDIRECT("'" & A2 & "'!C10),"")
and copying down as far as necessary.
If your concern is havi...Quickbooks Invalid Vendor Reference
Does anyone know what this means or how I might resolve them?
>The following purchase order(s) could not be posted to QuickBooks:
>3612677 (Details: There is an invalid reference to QuickBooks Vendor "East Fork Nursery" in >the Bill. QuickBooks error message: Invalid argument. The specified record does not exist in >the list.)
>84084 (Details: There is an invalid reference to QuickBooks Vendor "Natural Resources >Recovery of" in the Bill. QuickBooks error message: Invalid argument. The specified record >does not exist in the list.)
Initially, supp...How to reference an attached mdb?
In my main Access application, I referenced another mdb file of
which I need to run aform from the main.
Thank's to Marshall Barton, the foloowing code is ok.
Public Function ppSendEmail()
' init de la gestion d'erreur: la biblioth=E8que peut ne pas avoir
On Error GoTo ppSendEmail_Error
' AccXP_Mail: the name of the project of the AccXP_Mail.mdb
' modDivers: the name of the code module
' openFrmCourriel: the public function that opne the form
On Error GoTo 0
Ex...Cell reference being changed to a specific criteria in IF formula...HELP!
Can anyone possibly help with the following?
I have the following set of data:
Row No Column C Column E Column M
9 Package ID Package Reporting Date Reporting Date + 30days
10 2.0 Offshore Pipelay 18 Jan 2012 17 Feb 2012
13 Phase (Cell Ref: C13) Due in 30 days (Cell
15 Detailed Design Formula
21 Offshore Pipelay
The formula in Cell Ref: M15 is as shown below:
=3DSUMPRODUCT(IF('Offshore Pipelay 2'!$D$3:$D$1000=3D$E$10,0...pls Help-Smart list reference
I need help in something I think it is so simple in the screen of Sales
Order Setup "from tools-->setup-->sales-->sales order processing-->button
I have added City field to hold the city of that order and with lookup
button to open the the user
class lookup ""as I have the city = user class ""
so I want the value of the user class to be retrieved in the city textbox
and saved in external table "I already save it in external table"
then when I choose the order that is saved before from the lookup
window of sales types id ,i...Getting the cell reference
Thanks both for the QUICK response - I am amazed!! I was hoping for
simple formula as judging by your guys feedback, I am certainly
novice user. I will try get my head around the INDEX formula info.
Your help is much appreciated.
Nick Hodge Wrote:
> How are you trying to do this as you are in the misc group?
> LastRowInA = Range("A65536").End(xlUp).Row
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> "DuncanG&q...Can you reference cell values in Headers and Footers in Excel 200.
I want to customize my Header on an Excel spreadsheet to include the value
conatined in certain cells on my worksheet such that I will not have to
change the header every time I change the value of the referenced cell. Does
anyone know how to do this?
only possible with VBA using an event procedure. e.g. put the following
in your workbook module for cell A1
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
.CenterFooter = wksht.range("A1").value
E...How to determine when a window is indirectly hidden/shown?
I have a custom control that does some timer operations. I'd like for it
to kill the timer while it's not visible.
How can I detect when it is hidden or shown?
I had hoped that WM_SHOWWINDOW would be all I needed, but that is only
sent if the control is *directly* hidden/shown... yet if the parent
window (eg. a frame window) is hidden/shown, no message is sent. I tried
WM_WINDOWPOSCHANGED as well, with the same result.
Are there any messages sent to child windows when a window is
...How can I reference a value from a previous record?
Greetings forum members:
I have a simple inspection data table that contains the following five fields:
1. Primary Key
2. Asset ID (Duplicates OK)
3. Inspection Date
4. Start Value
5. Finish value
The data in this table must abide by the following rule:
For each Asset ID; The start value of the current inspection record must be
greater than or equal to the finish value of the previous inspection record.
(The previous record would be determined using the Inspection Date field)
My question is:
How can I reference the value from the previous inspection record, in order
to verify that th...Moving Cell Reference after Sorting
hi, i'm trying to have a cell reference in an equation following a
moving data point ...
Assumption #1 - column A has data A1=1, A2=5, A3=3
Assumption #2 - column B has an equation referring to the "3" in A3
Goal - to have my equation follow the data point "3"
Interaction - I **sort** column A so that the data is ascending order,
i.e., A1=1, A2=3, A3=5
Problem - my equation refers only to cell A3 (i.e., value is now 5,
originally was 3). I want the equation to refer to wherever my data
point went (i.e., to refer to whatever cell the 3 is sorted to). How
:...How can I convert text to reference ? Ex: ISNUMBER(ADDRESS(6;10;2.
I'm trying to use above function to avoid doing it by coding. However ADDRESS
function returns text and I cannot convert it to a reference, in order to use
its result as input to other functions (like ISNUMBER or CELL).
If I understand you correctly, try
...How do I change the sheet a formula refers automatically?
Here's what I want to do.
I need to be able to create the initial formula, then have it retain
relative reference. So for example, the initial formula on sheet 2
looks like this "=sheet1!L16"
On sheet 3 I want it to pull data from L16 on sheet 2. While I could
simply write this as a single formula "=sheet2!L16" Writing this 30
times for one formula seems redundent to say the least. How can I get
this to change automatically?
Another way to put what I want to do. . . . How do I get it to pull a
value from the immediatly preceding worksheet within the same
workboo...match and index function
I am using the match and index function to pull in data from a
separate worksheet. I am using Column A as the reference cell,
whereas my formula lies in Column F.
The formula pulls in the data fine. The problem is, when I sort the
data set, the match index function maintains the original reference
cell and does NOT refer to the same row.
So, for example, if my formula references A3, the formula continues to
use A3 after I sort the cell (as if I had used an absolute
Does anyone know how to fix this? How can I pull in data using
matchindex (or vlookup), but also be able to sort ...indirect function
INDIRECT function is very useful function but sometimes can be
inadvertently used wrongly. I am not sure whether complete information on
this function is available at one spot, if not some MVP may consider
preparing a webpage. It will be of great help to beginners like me.
You could google for indirect using Ron DeBruin's add in
"R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
> INDIRECT function is very useful function but someti...Outlook VBA -- Insert Email reference into new Task
I've written a script that creates a task from a selected email. It
puts the body of the email into the body of the task, but I also
wanted it to include a "reference" to the original email (like that
message icon perhaps) so I can link back to it. Anyone know how to do
Here's the code:
Dim item As MailItem
Set item = Outlook.Application.ActiveExplorer.Selection.item(1)
Dim olApp As Outlook.Application
Dim olTsk As TaskItem
Dim userField As Outlook.UserProperty
Dim userName As String