Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mike Peter
 
Posts: n/a
Default how to enter a formula using column() function for a range

I need to enter a formula using Data Validation. The formula is "=$B$4:$E$4".
How do I change this formula to use the Column() function instead of constant
'4'.

I want it to get the number '4' using current Column(). The formula has to
look like "=$B$(Column()):$E$(Column())". But the data validation dialog's
'Source' input box refuses to accept this as valid formula. Is there any work
around? thanks.
  #2   Report Post  
JulieD
 
Posts: n/a
Default

Hi Mike

when i try entering
=$B$4:$E$4
i get a error message from data/validation
"you can not use a direct reference to a worksheet range in a data
validation formula"

do you get the same error?
what are you trying to achieve with this formula? the cell with the
validation on it must be equal to the sum of the four values in the range
or???

Cheers
JulieD


"Mike Peter" <Mike wrote in message
...
I need to enter a formula using Data Validation. The formula is
"=$B$4:$E$4".
How do I change this formula to use the Column() function instead of
constant
'4'.

I want it to get the number '4' using current Column(). The formula has to
look like "=$B$(Column()):$E$(Column())". But the data validation dialog's
'Source' input box refuses to accept this as valid formula. Is there any
work
around? thanks.



  #3   Report Post  
Mike Peter
 
Posts: n/a
Default

In 'Data validation' dialog box, i selected 'list', before entering this
formula.
This will make the data entry easy for a target cell, because the cell will
display a list box of values from B4, C4, D4 and E4. No chance of wrong date
in the target cell.

I am trying to let excel select the number '4' using the column() formula.
This will make life easy for me, when I copy the cell to another cell. The
number will be decided based on the column of the cell.

Any idea how to do this? Just try to enter a formula of "=$c$4" in another
cell. This will copy the value from cell c4 in to the target cell. Now, how
do I change the formula to "=$c$(column())" ?

thanks.


"JulieD" wrote:

Hi Mike

when i try entering
=$B$4:$E$4
i get a error message from data/validation
"you can not use a direct reference to a worksheet range in a data
validation formula"

do you get the same error?
what are you trying to achieve with this formula? the cell with the
validation on it must be equal to the sum of the four values in the range
or???

Cheers
JulieD


"Mike Peter" <Mike wrote in message
...
I need to enter a formula using Data Validation. The formula is
"=$B$4:$E$4".
How do I change this formula to use the Column() function instead of
constant
'4'.

I want it to get the number '4' using current Column(). The formula has to
look like "=$B$(Column()):$E$(Column())". But the data validation dialog's
'Source' input box refuses to accept this as valid formula. Is there any
work
around? thanks.




  #5   Report Post  
JulieD
 
Posts: n/a
Default

Hi Mike

just a note - "4" in your example is a row not a column
so does this give you what you want?
using data / validation / list .... displays 10 entries from the current row
to current row plus 10

=INDIRECT("C"&ROW()&":C"&ROW()+10)

Cheers
JulieD


"Mike Peter" <Mike wrote in message
...
In 'Data validation' dialog box, i selected 'list', before entering this
formula.
This will make the data entry easy for a target cell, because the cell
will
display a list box of values from B4, C4, D4 and E4. No chance of wrong
date
in the target cell.

I am trying to let excel select the number '4' using the column() formula.
This will make life easy for me, when I copy the cell to another cell. The
number will be decided based on the column of the cell.

Any idea how to do this? Just try to enter a formula of "=$c$4" in another
cell. This will copy the value from cell c4 in to the target cell. Now,
how
do I change the formula to "=$c$(column())" ?

thanks.


"JulieD" wrote:

Hi Mike

when i try entering
=$B$4:$E$4
i get a error message from data/validation
"you can not use a direct reference to a worksheet range in a data
validation formula"

do you get the same error?
what are you trying to achieve with this formula? the cell with the
validation on it must be equal to the sum of the four values in the range
or???

Cheers
JulieD


"Mike Peter" <Mike
wrote in message
...
I need to enter a formula using Data Validation. The formula is
"=$B$4:$E$4".
How do I change this formula to use the Column() function instead of
constant
'4'.

I want it to get the number '4' using current Column(). The formula has
to
look like "=$B$(Column()):$E$(Column())". But the data validation
dialog's
'Source' input box refuses to accept this as valid formula. Is there
any
work
around? thanks.








  #6   Report Post  
Mike Peter
 
Posts: n/a
Default

thanks Julie. I got it. The formula I drove from your sample is
=INDIRECT("B"&COLUMN()&":E"&COLUMN()). This meets the current work.

"JulieD" wrote:

Hi Mike

just a note - "4" in your example is a row not a column
so does this give you what you want?
using data / validation / list .... displays 10 entries from the current row
to current row plus 10

=INDIRECT("C"&ROW()&":C"&ROW()+10)

Cheers
JulieD


"Mike Peter" <Mike wrote in message
...
In 'Data validation' dialog box, i selected 'list', before entering this
formula.
This will make the data entry easy for a target cell, because the cell
will
display a list box of values from B4, C4, D4 and E4. No chance of wrong
date
in the target cell.

I am trying to let excel select the number '4' using the column() formula.
This will make life easy for me, when I copy the cell to another cell. The
number will be decided based on the column of the cell.

Any idea how to do this? Just try to enter a formula of "=$c$4" in another
cell. This will copy the value from cell c4 in to the target cell. Now,
how
do I change the formula to "=$c$(column())" ?

thanks.


"JulieD" wrote:

Hi Mike

when i try entering
=$B$4:$E$4
i get a error message from data/validation
"you can not use a direct reference to a worksheet range in a data
validation formula"

do you get the same error?
what are you trying to achieve with this formula? the cell with the
validation on it must be equal to the sum of the four values in the range
or???

Cheers
JulieD


"Mike Peter" <Mike
wrote in message
...
I need to enter a formula using Data Validation. The formula is
"=$B$4:$E$4".
How do I change this formula to use the Column() function instead of
constant
'4'.

I want it to get the number '4' using current Column(). The formula has
to
look like "=$B$(Column()):$E$(Column())". But the data validation
dialog's
'Source' input box refuses to accept this as valid formula. Is there
any
work
around? thanks.






  #7   Report Post  
JulieD
 
Posts: n/a
Default

Hi Mike

glad its solved

Cheers
JulieD

"Mike Peter" <Mike wrote in message
...
thanks Julie. I got it. The formula I drove from your sample is
=INDIRECT("B"&COLUMN()&":E"&COLUMN()). This meets the current work.

"JulieD" wrote:

Hi Mike

just a note - "4" in your example is a row not a column
so does this give you what you want?
using data / validation / list .... displays 10 entries from the current
row
to current row plus 10

=INDIRECT("C"&ROW()&":C"&ROW()+10)

Cheers
JulieD


"Mike Peter" <Mike
wrote in message
...
In 'Data validation' dialog box, i selected 'list', before entering
this
formula.
This will make the data entry easy for a target cell, because the cell
will
display a list box of values from B4, C4, D4 and E4. No chance of wrong
date
in the target cell.

I am trying to let excel select the number '4' using the column()
formula.
This will make life easy for me, when I copy the cell to another cell.
The
number will be decided based on the column of the cell.

Any idea how to do this? Just try to enter a formula of "=$c$4" in
another
cell. This will copy the value from cell c4 in to the target cell. Now,
how
do I change the formula to "=$c$(column())" ?

thanks.


"JulieD" wrote:

Hi Mike

when i try entering
=$B$4:$E$4
i get a error message from data/validation
"you can not use a direct reference to a worksheet range in a data
validation formula"

do you get the same error?
what are you trying to achieve with this formula? the cell with the
validation on it must be equal to the sum of the four values in the
range
or???

Cheers
JulieD


"Mike Peter" <Mike
wrote in message
...
I need to enter a formula using Data Validation. The formula is
"=$B$4:$E$4".
How do I change this formula to use the Column() function instead of
constant
'4'.

I want it to get the number '4' using current Column(). The formula
has
to
look like "=$B$(Column()):$E$(Column())". But the data validation
dialog's
'Source' input box refuses to accept this as valid formula. Is there
any
work
around? thanks.








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Naming column in Index Function mlkpied Excel Worksheet Functions 3 December 7th 04 01:20 AM
Aligning Two Lists in Excel Rich Excel Discussion (Misc queries) 2 December 4th 04 06:44 PM
which formula or function searches for a value in a range of cell. Roccobarocco Excel Worksheet Functions 5 December 3rd 04 01:06 PM
which formula or function searches for a value in a range of cell. Roccobarocco Excel Worksheet Functions 7 December 3rd 04 10:06 AM
Can you average data in 1 column based on a range of values in another? kman24 Excel Worksheet Functions 2 November 17th 04 03:09 PM


All times are GMT +1. The time now is 05:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"