Prev: Copy sheets to new workbook - subscript error
Next: Excel 2007 dynamic chart range
From: monden2 on 14 Apr 2010 10:11
Hey Bob,

I think that is just what I need. But I have no idea how to but it in there.
Can I just type it in the controlsource box in the properties window for the
textbox, or should I write a VBA code for it (in which case, i need more
help. Haha)

Thanks a lot already!

"Bob Phillips" wrote:

> I would put something in the worksheet to get that cell reference, and then
> use say INDIRECT(B2) in the ControlSource for the textbox.
>
> --
>
> HTH
>
> Bob
>
> "monden2" <monden2(a)discussions.microsoft.com> wrote in message
> news:CEC4D1A7-DD67-4471-9B05-49D1B4D9C984(a)microsoft.com...
> > Hey everyone,
> >
> > If anyone was busy making a code for me on this, I am sorry to say that
> > management wanted an additional table. Thus, the ADDRESS formula had to be
> > updated. This is the new one:
> >
> > =IF($A$11="Short
> > Term",ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Sheet2!$A$19&"'!$B:$B"),0),MATCH(Sheet2!$A$14,INDIRECT("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0)),IF($A$11="Long
> > Term",ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Sheet2!$A$19&"'!$B:$B"),0)+15,MATCH(Sheet2!$A$14,INDIRECT("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0)),""))
> >
> > Awaiting your response(s)
> >
> > Thanks! :D
> >
> > "monden2" wrote:
> >
> >> Hey Bob,
> >>
> >> Because the ControlSource depens on the outcome of the formula. The
> >> formula
> >> is a part of a data validation sheet. In this case, the data in cell A13,
> >> A14
> >> and A15 will refer to a specific cell. This formula encorporate will show
> >> the
> >> address of this cell, which of course varies when the data in cell A1 or
> >> A2
> >> or A3 changes.
> >>
> >> For example.
> >>
> >> 13 14 15
> >> A 'Account' 'Internal' returns value in Sheet3!B20
> >>
> >> -or-
> >>
> >> 13 14 15
> >> A 'Account' 'External' returns value in Sheet6!B22
> >>
> >> -or-
> >>
> >> 13 14 15
> >> A 'Account' 'Other' returns value in Sheet2!B21
> >>
> >> This ADDRESS formula will then have a value 'Sheet3'!$B$20,
> >> 'Sheet6'!$B$22
> >> and 'Sheet2'!$B$21 respectively (the ' are for formating purposes
> >> because, as
> >> far as I read, thats how it should be typed in the ControlSource box).
> >>
> >> What I want is for my userform Textbox's Controlsource to pick up this
> >> value
> >> and use it as its reference, so that when the users puts data in the
> >> textbox,
> >> it will automatically put this data in that cell, to which the ADDRESS
> >> formula is referring.
> >>
> >> Thanks for the help! :D
> >>
> >>
> >>
> >> "Bob Phillips" wrote:
> >>
> >> > Why not just bind the textbox to B2?
> >> >
> >> > --
> >> >
> >> > HTH
> >> >
> >> > Bob
> >> >
> >> > "monden2" <monden2(a)discussions.microsoft.com> wrote in message
> >> > news:97A17B9B-A050-4B5D-A537-921767BA7D2C(a)microsoft.com...
> >> > > Hey Everyone,
> >> > >
> >> > > I got a quickie here. I have made this spreadsheet code using a lot
> >> > > of
> >> > > info
> >> > > on here. Now I wan to use it in VB.
> >> > >
> >> > > =ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Sheet2!$A$19&"'!$B:$B"),0),MATCH(Sheet2!$A$14,INDIRECT("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0))
> >> > >
> >> > > What I want to do, is have the outcome of this formula (i.e. $B$2) to
> >> > > be
> >> > > the
> >> > > ControlSource cell for my TextBox. It has to be flexible, meaning
> >> > > that if
> >> > > any
> >> > > variable change, and because of that the ADDRESS changes, it will
> >> > > automatically update.
> >> > >
> >> > > Thanks in advance!
> >> >
> >> >
> >> > .
> >> >
>
>
> .
>
From: Bob Phillips on 14 Apr 2010 16:53
Yes, just type that into the ControlSource property field.

--

HTH

Bob

"monden2" <monden2(a)discussions.microsoft.com> wrote in message
news:99E625EC-824A-4EED-A837-04E6A0FB06D2(a)microsoft.com...
> Hey Bob,
>
> I think that is just what I need. But I have no idea how to but it in
> there.
> Can I just type it in the controlsource box in the properties window for
> the
> textbox, or should I write a VBA code for it (in which case, i need more
> help. Haha)
>
> Thanks a lot already!
>
> "Bob Phillips" wrote:
>
>> I would put something in the worksheet to get that cell reference, and
>> then
>> use say INDIRECT(B2) in the ControlSource for the textbox.
>>
>> --
>>
>> HTH
>>
>> Bob
>>
>> "monden2" <monden2(a)discussions.microsoft.com> wrote in message
>> news:CEC4D1A7-DD67-4471-9B05-49D1B4D9C984(a)microsoft.com...
>> > Hey everyone,
>> >
>> > If anyone was busy making a code for me on this, I am sorry to say that
>> > management wanted an additional table. Thus, the ADDRESS formula had to
>> > be
>> > updated. This is the new one:
>> >
>> > =IF($A$11="Short
>> > Term",ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Sheet2!$A$19&"'!$B:$B"),0),MATCH(Sheet2!$A$14,INDIRECT("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0)),IF($A$11="Long
>> > Term",ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Sheet2!$A$19&"'!$B:$B"),0)+15,MATCH(Sheet2!$A$14,INDIRECT("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0)),""))
>> >
>> > Awaiting your response(s)
>> >
>> > Thanks! :D
>> >
>> > "monden2" wrote:
>> >
>> >> Hey Bob,
>> >>
>> >> Because the ControlSource depens on the outcome of the formula. The
>> >> formula
>> >> is a part of a data validation sheet. In this case, the data in cell
>> >> A13,
>> >> A14
>> >> and A15 will refer to a specific cell. This formula encorporate will
>> >> show
>> >> the
>> >> address of this cell, which of course varies when the data in cell A1
>> >> or
>> >> A2
>> >> or A3 changes.
>> >>
>> >> For example.
>> >>
>> >> 13 14 15
>> >> A 'Account' 'Internal' returns value in Sheet3!B20
>> >>
>> >> -or-
>> >>
>> >> 13 14 15
>> >> A 'Account' 'External' returns value in Sheet6!B22
>> >>
>> >> -or-
>> >>
>> >> 13 14 15
>> >> A 'Account' 'Other' returns value in Sheet2!B21
>> >>
>> >> This ADDRESS formula will then have a value 'Sheet3'!$B$20,
>> >> 'Sheet6'!$B$22
>> >> and 'Sheet2'!$B$21 respectively (the ' are for formating purposes
>> >> because, as
>> >> far as I read, thats how it should be typed in the ControlSource box).
>> >>
>> >> What I want is for my userform Textbox's Controlsource to pick up this
>> >> value
>> >> and use it as its reference, so that when the users puts data in the
>> >> textbox,
>> >> it will automatically put this data in that cell, to which the ADDRESS
>> >> formula is referring.
>> >>
>> >> Thanks for the help! :D
>> >>
>> >>
>> >>
>> >> "Bob Phillips" wrote:
>> >>
>> >> > Why not just bind the textbox to B2?
>> >> >
>> >> > --
>> >> >
>> >> > HTH
>> >> >
>> >> > Bob
>> >> >
>> >> > "monden2" <monden2(a)discussions.microsoft.com> wrote in message
>> >> > news:97A17B9B-A050-4B5D-A537-921767BA7D2C(a)microsoft.com...
>> >> > > Hey Everyone,
>> >> > >
>> >> > > I got a quickie here. I have made this spreadsheet code using a
>> >> > > lot
>> >> > > of
>> >> > > info
>> >> > > on here. Now I wan to use it in VB.
>> >> > >
>> >> > > =ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Sheet2!$A$19&"'!$B:$B"),0),MATCH(Sheet2!$A$14,INDIRECT("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0))
>> >> > >
>> >> > > What I want to do, is have the outcome of this formula (i.e. $B$2)
>> >> > > to
>> >> > > be
>> >> > > the
>> >> > > ControlSource cell for my TextBox. It has to be flexible, meaning
>> >> > > that if
>> >> > > any
>> >> > > variable change, and because of that the ADDRESS changes, it will
>> >> > > automatically update.
>> >> > >
>> >> > > Thanks in advance!
>> >> >
>> >> >
>> >> > .
>> >> >
>>
>>
>> .
>>


First  |  Prev  | 
Pages: 1 2
Prev: Copy sheets to new workbook - subscript error
Next: Excel 2007 dynamic chart range