INDIRECT reference

Hi. I need to have in say cell A1 a drop down list (which I obtain with the
data validation tool) which shows a list like 0%, 5%, 10%, 15%, etc. so that
people looking at the spreasheet can select their % desired.  BUT, while I
would like the numbers to chosse from in the drop down list to be those
above, I really need them to be divided by 12 i.e. (0%/12), then (5%/12),
then (10%/12), etc.
I thought i cd try to do it with the indirect function but I ma not really
sure what the best way to do it. Is?
Any suggestions?
I made be going about it the wrong way as well....
Tx,
Sat


0
saturnin02 (56)
7/20/2003 10:37:42 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
336 Views

Similar Articles

[PageSpeed] 28

Sat,

Can't you simply use =A1/12

--

HTH

Bob Phillips

"saturnin02" <saturnin02@hotmail.com> wrote in message
news:Ovqur#wTDHA.3796@tk2msftngp13.phx.gbl...
> Hi. I need to have in say cell A1 a drop down list (which I obtain with
the
> data validation tool) which shows a list like 0%, 5%, 10%, 15%, etc. so
that
> people looking at the spreasheet can select their % desired.  BUT, while I
> would like the numbers to chosse from in the drop down list to be those
> above, I really need them to be divided by 12 i.e. (0%/12), then (5%/12),
> then (10%/12), etc.
> I thought i cd try to do it with the indirect function but I ma not really
> sure what the best way to do it. Is?
> Any suggestions?
> I made be going about it the wrong way as well....
> Tx,
> Sat
>
>


0
bob.phillips (411)
7/20/2003 11:04:49 PM
I cd but that is only part of the formula.
But for other reasons, I'd liek to know if there is a way of doing what I
posted below--something like ti I mean....

"Bob Phillips" <bob.phillips@tiscali.co.uk> wrote in message
news:#qvYSNxTDHA.2128@TK2MSFTNGP12.phx.gbl...
> Sat,
>
> Can't you simply use =A1/12
>
> --
>
> HTH
>
> Bob Phillips
>
> "saturnin02" <saturnin02@hotmail.com> wrote in message
> news:Ovqur#wTDHA.3796@tk2msftngp13.phx.gbl...
> > Hi. I need to have in say cell A1 a drop down list (which I obtain with
> the
> > data validation tool) which shows a list like 0%, 5%, 10%, 15%, etc. so
> that
> > people looking at the spreasheet can select their % desired.  BUT, while
I
> > would like the numbers to chosse from in the drop down list to be those
> > above, I really need them to be divided by 12 i.e. (0%/12), then
(5%/12),
> > then (10%/12), etc.
> > I thought i cd try to do it with the indirect function but I ma not
really
> > sure what the best way to do it. Is?
> > Any suggestions?
> > I made be going about it the wrong way as well....
> > Tx,
> > Sat
> >
> >
>
>


0
saturnin02 (56)
7/21/2003 1:51:53 AM
Hi saturnin02!

My very strong preference is to go the way of Bob and allow APRs to be
entered and to do the adjustment to the monthly effective rate in
another cell that is clearly labeled that way. But you've rejected
that approach.

Here's one way and although I haven't thoroughly test it, it does seem
to do the job. The principle is to include the set schedule of rates
plus those rate divided by 12 in your data validated list and then use
a worksheet_change event handler.

It is subject to a restriction that the lowest rate that you have in
your unadjusted list must not be greater than the highest unadjusted
rate / 12. I can allow as a special case a rate of 0%.

I have a range of cells

H1:H13 has rates 15% down to 3%
H14 has 0%

H15:
=ROUND(H1/12,6)
Copied down to H27

In A1 I have my data validated entry which relies on a list

=$H$1:$H$27

Then I have a Worksheet_Change event handling routine:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Intersect(Target, Range("A1")) Is Nothing Then
  Application.EnableEvents = True
  End
End If
If Range("A1").Value = 0 Then
  Application.EnableEvents = True
  End
End If
If Range("A1").Value < 0.02 Then
  Application.EnableEvents = True
  End
End If
Range("A1").Value = Round(Range("A1").Value / 12, 6)
Application.EnableEvents = True
End Sub


It's probably capable of being improved upon so hang around and watch
for other comments / replies.
-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Monday 21st July: Belgium (National Day),
Bolivia (Martyrs´┐Ż Day), Guadeloupe (Victor Schoelcher Day), Guam
(Liberation Day), Japan (Marine Day), Malta (St. George Festa), Peru
(Feria Local Fronteriza Peruano Ecuatoriana), St. Martin (Schoelcher
Day).
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"saturnin02" <saturnin02@hotmail.com> wrote in message
news:uCX%232qyTDHA.3640@tk2msftngp13.phx.gbl...
> I cd but that is only part of the formula.
> But for other reasons, I'd liek to know if there is a way of doing
what I
> posted below--something like ti I mean....
>
> "Bob Phillips" <bob.phillips@tiscali.co.uk> wrote in message
> news:#qvYSNxTDHA.2128@TK2MSFTNGP12.phx.gbl...
> > Sat,
> >
> > Can't you simply use =A1/12
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > "saturnin02" <saturnin02@hotmail.com> wrote in message
> > news:Ovqur#wTDHA.3796@tk2msftngp13.phx.gbl...
> > > Hi. I need to have in say cell A1 a drop down list (which I
obtain with
> > the
> > > data validation tool) which shows a list like 0%, 5%, 10%, 15%,
etc. so
> > that
> > > people looking at the spreasheet can select their % desired.
BUT, while
> I
> > > would like the numbers to chosse from in the drop down list to
be those
> > > above, I really need them to be divided by 12 i.e. (0%/12), then
> (5%/12),
> > > then (10%/12), etc.
> > > I thought i cd try to do it with the indirect function but I ma
not
> really
> > > sure what the best way to do it. Is?
> > > Any suggestions?
> > > I made be going about it the wrong way as well....
> > > Tx,
> > > Sat
> > >
> > >
> >
> >
>
>


0
njharker (1646)
7/21/2003 2:35:59 AM
Great Norman.
I appreciate your comments and will give it a whirl!
Tx,
Sat
"Norman Harker" <njharker@optusnet.com.au> wrote in message
news:#$pVUDzTDHA.2260@TK2MSFTNGP12.phx.gbl...
> Hi saturnin02!
>
> My very strong preference is to go the way of Bob and allow APRs to be
> entered and to do the adjustment to the monthly effective rate in
> another cell that is clearly labeled that way. But you've rejected
> that approach.
>
> Here's one way and although I haven't thoroughly test it, it does seem
> to do the job. The principle is to include the set schedule of rates
> plus those rate divided by 12 in your data validated list and then use
> a worksheet_change event handler.
>
> It is subject to a restriction that the lowest rate that you have in
> your unadjusted list must not be greater than the highest unadjusted
> rate / 12. I can allow as a special case a rate of 0%.
>
> I have a range of cells
>
> H1:H13 has rates 15% down to 3%
> H14 has 0%
>
> H15:
> =ROUND(H1/12,6)
> Copied down to H27
>
> In A1 I have my data validated entry which relies on a list
>
> =$H$1:$H$27
>
> Then I have a Worksheet_Change event handling routine:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Application.EnableEvents = False
> If Intersect(Target, Range("A1")) Is Nothing Then
>   Application.EnableEvents = True
>   End
> End If
> If Range("A1").Value = 0 Then
>   Application.EnableEvents = True
>   End
> End If
> If Range("A1").Value < 0.02 Then
>   Application.EnableEvents = True
>   End
> End If
> Range("A1").Value = Round(Range("A1").Value / 12, 6)
> Application.EnableEvents = True
> End Sub
>
>
> It's probably capable of being improved upon so hang around and watch
> for other comments / replies.
> --
> Regards
> Norman Harker MVP (Excel)
> Sydney, Australia
> Holidays and Observances Monday 21st July: Belgium (National Day),
> Bolivia (Martyrs' Day), Guadeloupe (Victor Schoelcher Day), Guam
> (Liberation Day), Japan (Marine Day), Malta (St. George Festa), Peru
> (Feria Local Fronteriza Peruano Ecuatoriana), St. Martin (Schoelcher
> Day).
> njharker@optusnet.com.au
> Excel and Word Function Lists (Classifications, Syntax and Arguments)
> available free to good homes.
> "saturnin02" <saturnin02@hotmail.com> wrote in message
> news:uCX%232qyTDHA.3640@tk2msftngp13.phx.gbl...
> > I cd but that is only part of the formula.
> > But for other reasons, I'd liek to know if there is a way of doing
> what I
> > posted below--something like ti I mean....
> >
> > "Bob Phillips" <bob.phillips@tiscali.co.uk> wrote in message
> > news:#qvYSNxTDHA.2128@TK2MSFTNGP12.phx.gbl...
> > > Sat,
> > >
> > > Can't you simply use =A1/12
> > >
> > > --
> > >
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "saturnin02" <saturnin02@hotmail.com> wrote in message
> > > news:Ovqur#wTDHA.3796@tk2msftngp13.phx.gbl...
> > > > Hi. I need to have in say cell A1 a drop down list (which I
> obtain with
> > > the
> > > > data validation tool) which shows a list like 0%, 5%, 10%, 15%,
> etc. so
> > > that
> > > > people looking at the spreasheet can select their % desired.
> BUT, while
> > I
> > > > would like the numbers to chosse from in the drop down list to
> be those
> > > > above, I really need them to be divided by 12 i.e. (0%/12), then
> > (5%/12),
> > > > then (10%/12), etc.
> > > > I thought i cd try to do it with the indirect function but I ma
> not
> > really
> > > > sure what the best way to do it. Is?
> > > > Any suggestions?
> > > > I made be going about it the wrong way as well....
> > > > Tx,
> > > > Sat
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
saturnin02 (56)
7/21/2003 2:41:12 AM
Possibly a shorter way would be to create a named formula

=sheet1!$A$1/12

Insert|Name|Define

Then just enter the name and the formula

Just refer to the name rather than a cell in your calculations.  (make it
short and easy to remember)

HTH

PC


"Norman Harker" <njharker@optusnet.com.au> wrote in message
news:#$pVUDzTDHA.2260@TK2MSFTNGP12.phx.gbl...
> Hi saturnin02!
>
> My very strong preference is to go the way of Bob and allow APRs to be
> entered and to do the adjustment to the monthly effective rate in
> another cell that is clearly labeled that way. But you've rejected
> that approach.
>
> Here's one way and although I haven't thoroughly test it, it does seem
> to do the job. The principle is to include the set schedule of rates
> plus those rate divided by 12 in your data validated list and then use
> a worksheet_change event handler.
>
> It is subject to a restriction that the lowest rate that you have in
> your unadjusted list must not be greater than the highest unadjusted
> rate / 12. I can allow as a special case a rate of 0%.
>
> I have a range of cells
>
> H1:H13 has rates 15% down to 3%
> H14 has 0%
>
> H15:
> =ROUND(H1/12,6)
> Copied down to H27
>
> In A1 I have my data validated entry which relies on a list
>
> =$H$1:$H$27
>
> Then I have a Worksheet_Change event handling routine:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Application.EnableEvents = False
> If Intersect(Target, Range("A1")) Is Nothing Then
>   Application.EnableEvents = True
>   End
> End If
> If Range("A1").Value = 0 Then
>   Application.EnableEvents = True
>   End
> End If
> If Range("A1").Value < 0.02 Then
>   Application.EnableEvents = True
>   End
> End If
> Range("A1").Value = Round(Range("A1").Value / 12, 6)
> Application.EnableEvents = True
> End Sub
>
>
> It's probably capable of being improved upon so hang around and watch
> for other comments / replies.
> --
> Regards
> Norman Harker MVP (Excel)
> Sydney, Australia
> Holidays and Observances Monday 21st July: Belgium (National Day),
> Bolivia (Martyrs' Day), Guadeloupe (Victor Schoelcher Day), Guam
> (Liberation Day), Japan (Marine Day), Malta (St. George Festa), Peru
> (Feria Local Fronteriza Peruano Ecuatoriana), St. Martin (Schoelcher
> Day).
> njharker@optusnet.com.au
> Excel and Word Function Lists (Classifications, Syntax and Arguments)
> available free to good homes.
> "saturnin02" <saturnin02@hotmail.com> wrote in message
> news:uCX%232qyTDHA.3640@tk2msftngp13.phx.gbl...
> > I cd but that is only part of the formula.
> > But for other reasons, I'd liek to know if there is a way of doing
> what I
> > posted below--something like ti I mean....
> >
> > "Bob Phillips" <bob.phillips@tiscali.co.uk> wrote in message
> > news:#qvYSNxTDHA.2128@TK2MSFTNGP12.phx.gbl...
> > > Sat,
> > >
> > > Can't you simply use =A1/12
> > >
> > > --
> > >
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "saturnin02" <saturnin02@hotmail.com> wrote in message
> > > news:Ovqur#wTDHA.3796@tk2msftngp13.phx.gbl...
> > > > Hi. I need to have in say cell A1 a drop down list (which I
> obtain with
> > > the
> > > > data validation tool) which shows a list like 0%, 5%, 10%, 15%,
> etc. so
> > > that
> > > > people looking at the spreasheet can select their % desired.
> BUT, while
> > I
> > > > would like the numbers to chosse from in the drop down list to
> be those
> > > > above, I really need them to be divided by 12 i.e. (0%/12), then
> > (5%/12),
> > > > then (10%/12), etc.
> > > > I thought i cd try to do it with the indirect function but I ma
> not
> > really
> > > > sure what the best way to do it. Is?
> > > > Any suggestions?
> > > > I made be going about it the wrong way as well....
> > > > Tx,
> > > > Sat
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
7/21/2003 2:58:19 AM
Right, Paul.
Makes sense!
Never thought of that (obviously)
Tx,
Sat

"Paul Corrado" <paulcorrado@optonline.net> wrote in message
news:ussn$OzTDHA.212@TK2MSFTNGP10.phx.gbl...
> Possibly a shorter way would be to create a named formula
>
> =sheet1!$A$1/12
>
> Insert|Name|Define
>
> Then just enter the name and the formula
>
> Just refer to the name rather than a cell in your calculations.  (make it
> short and easy to remember)
>
> HTH
>
> PC
>
>
> "Norman Harker" <njharker@optusnet.com.au> wrote in message
> news:#$pVUDzTDHA.2260@TK2MSFTNGP12.phx.gbl...
> > Hi saturnin02!
> >
> > My very strong preference is to go the way of Bob and allow APRs to be
> > entered and to do the adjustment to the monthly effective rate in
> > another cell that is clearly labeled that way. But you've rejected
> > that approach.
> >
> > Here's one way and although I haven't thoroughly test it, it does seem
> > to do the job. The principle is to include the set schedule of rates
> > plus those rate divided by 12 in your data validated list and then use
> > a worksheet_change event handler.
> >
> > It is subject to a restriction that the lowest rate that you have in
> > your unadjusted list must not be greater than the highest unadjusted
> > rate / 12. I can allow as a special case a rate of 0%.
> >
> > I have a range of cells
> >
> > H1:H13 has rates 15% down to 3%
> > H14 has 0%
> >
> > H15:
> > =ROUND(H1/12,6)
> > Copied down to H27
> >
> > In A1 I have my data validated entry which relies on a list
> >
> > =$H$1:$H$27
> >
> > Then I have a Worksheet_Change event handling routine:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Application.EnableEvents = False
> > If Intersect(Target, Range("A1")) Is Nothing Then
> >   Application.EnableEvents = True
> >   End
> > End If
> > If Range("A1").Value = 0 Then
> >   Application.EnableEvents = True
> >   End
> > End If
> > If Range("A1").Value < 0.02 Then
> >   Application.EnableEvents = True
> >   End
> > End If
> > Range("A1").Value = Round(Range("A1").Value / 12, 6)
> > Application.EnableEvents = True
> > End Sub
> >
> >
> > It's probably capable of being improved upon so hang around and watch
> > for other comments / replies.
> > --
> > Regards
> > Norman Harker MVP (Excel)
> > Sydney, Australia
> > Holidays and Observances Monday 21st July: Belgium (National Day),
> > Bolivia (Martyrs' Day), Guadeloupe (Victor Schoelcher Day), Guam
> > (Liberation Day), Japan (Marine Day), Malta (St. George Festa), Peru
> > (Feria Local Fronteriza Peruano Ecuatoriana), St. Martin (Schoelcher
> > Day).
> > njharker@optusnet.com.au
> > Excel and Word Function Lists (Classifications, Syntax and Arguments)
> > available free to good homes.
> > "saturnin02" <saturnin02@hotmail.com> wrote in message
> > news:uCX%232qyTDHA.3640@tk2msftngp13.phx.gbl...
> > > I cd but that is only part of the formula.
> > > But for other reasons, I'd liek to know if there is a way of doing
> > what I
> > > posted below--something like ti I mean....
> > >
> > > "Bob Phillips" <bob.phillips@tiscali.co.uk> wrote in message
> > > news:#qvYSNxTDHA.2128@TK2MSFTNGP12.phx.gbl...
> > > > Sat,
> > > >
> > > > Can't you simply use =A1/12
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "saturnin02" <saturnin02@hotmail.com> wrote in message
> > > > news:Ovqur#wTDHA.3796@tk2msftngp13.phx.gbl...
> > > > > Hi. I need to have in say cell A1 a drop down list (which I
> > obtain with
> > > > the
> > > > > data validation tool) which shows a list like 0%, 5%, 10%, 15%,
> > etc. so
> > > > that
> > > > > people looking at the spreasheet can select their % desired.
> > BUT, while
> > > I
> > > > > would like the numbers to chosse from in the drop down list to
> > be those
> > > > > above, I really need them to be divided by 12 i.e. (0%/12), then
> > > (5%/12),
> > > > > then (10%/12), etc.
> > > > > I thought i cd try to do it with the indirect function but I ma
> > not
> > > really
> > > > > sure what the best way to do it. Is?
> > > > > Any suggestions?
> > > > > I made be going about it the wrong way as well....
> > > > > Tx,
> > > > > Sat
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
saturnin02 (56)
7/21/2003 3:27:15 AM
Reply:

Similar Artilces:

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! Hi! Try this: =OFFSET(INDIRECT(A$2&"!Q1"),,(ROWS($1:1)-1)*1) When copied down will return references to: Sheet!???Q1 Sheet!???R1 Sheet!???S1 Sheet!???T1 etc Biff "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 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. -- Paul B 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" <ap@textguru.ph> wrote in message news:OZ99hQoKFHA.436@TK2MSFTNGP09.phx.gbl... > 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 http://www.officelabs.com/ribbonhero of download this xls workbook http://office.microsoft.com/download/afile.aspx?AssetID=AM101864291033 -- Gary Keramidas Office 2010 "Noella" <Noella@discussions.microsoft.com> wrote in message news:45DF2F13-504E-45CD-8FB7-39433847656F@microsoft.com... >I think I have seen one somewhere but can't fi...

Absolute Cell Reference Across A Range
Hi all, 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, Samuel -- Samuel ----------------------------------------------------------------------- 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 workbook? TIA, Ken 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 happy.: -- Message posted from http://www.ExcelForum.com =INDIRECT(A1 & "!J20") lsu-i-like <<lsu-i-like.1cjz7z@excelforum-nospam.com>> 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": =AND(L1>INDIRECT(ADDRESS(ROW($A4),COLUMN($AD4), 4,TRUE,"data")),L1<INDIRECT(ADDRESS(ROW($A4),COLUMN($AE4), 4,TRUE,"data"))) 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? Thanks. 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
Hi, 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. Thanks! Val One way: 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?
Hi, 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 =E9t=E9 r=E9f=E9renc=E9e On Error GoTo ppSendEmail_Error AccXP_Mail.modDivers.openFrmCourriel ' AccXP_Mail: the name of the project of the AccXP_Mail.mdb attached ' 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 Column O 9 Package ID Package Reporting Date Reporting Date + 30days 10 2.0 Offshore Pipelay 18 Jan 2012 17 Feb 2012 11 12 13 Phase (Cell Ref: C13) Due in 30 days (Cell Ref:M13) 14 15 Detailed Design Formula 16 Procurement 17 Fabrication 18 Installation 19 Pre-commissioning 20 Commissioning 21 Offshore Pipelay 22 Operations 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
hi 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 Order" 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: > Duncan > > How are you trying to do this as you are in the misc group? > > Programmatically... > > LastRowInA = Range("A65536").End(xlUp).Row > > -- > HTH > Nick Hodge > Microsoft MVP - Excel > Southampton, England > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS > > > "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? Hi only possible with VBA using an event procedure. e.g. put the following code in your workbook module for cell A1 Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In Me.Worksheets With wkSht.PageSetup .CenterFooter = wksht.range("A1").value End With Next wkSht 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 hidden/shown? Thanks. ...

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 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 =INDIRECT(ADDRESS(G7,H7)) for example HTH, Bernd ...

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 reference). 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 http://www.rondebruin.nl/ -- Don Guillett SalesAid Software donaldb@281.com "R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message news:u5Rleq06FHA.3388@TK2MSFTNGP11.phx.gbl... > INDIRECT function is very useful function but someti...

Outlook VBA -- Insert Email reference into new Task
Hi guys, 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 this? Here's the code: ----------- Sub TaskFromEmail() 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 Set olAp...