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!

Why not just bind the textbox to B2?

--

HTH

Bob

"monden2" wrote in message
...
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!

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" wrote in message
...
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!


.

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("'"&Shee t2!$A$19&"'!$B:$B"),0),MATCH(Sheet2!$A$14,INDIRECT ("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0)),IF($A$11="Lon g
Term",ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Shee t2!$A$19&"'!$B:$B"),0)+15,MATCH(Sheet2!$A$14,INDIR ECT("'"&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" wrote in message
...
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!


.

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" wrote in message
...
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("'"&Shee t2!$A$19&"'!$B:$B"),0),MATCH(Sheet2!$A$14,INDIRECT ("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0)),IF($A$11="Lon g
Term",ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Shee t2!$A$19&"'!$B:$B"),0)+15,MATCH(Sheet2!$A$14,INDIR ECT("'"&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" wrote in message
...
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!


.

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" wrote in message
...
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("'"&Shee t2!$A$19&"'!$B:$B"),0),MATCH(Sheet2!$A$14,INDIRECT ("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0)),IF($A$11="Lon g
Term",ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Shee t2!$A$19&"'!$B:$B"),0)+15,MATCH(Sheet2!$A$14,INDIR ECT("'"&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" wrote in message
...
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!


.



.

Yes, just type that into the ControlSource property field.

--

HTH

Bob

"monden2" wrote in message
...
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" wrote in message
...
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("'"&Shee t2!$A$19&"'!$B:$B"),0),MATCH(Sheet2!$A$14,INDIRECT ("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0)),IF($A$11="Lon g
Term",ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Shee t2!$A$19&"'!$B:$B"),0)+15,MATCH(Sheet2!$A$14,INDIR ECT("'"&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" wrote in message
...
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!


.



.