Home 
Search 
Today's Posts 
#1




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




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




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. 
#4




Hi
=OFFSET($B$1,COLUMN()1,0,1,4 returns: B1:E1 for column A B2:E2 for column B B3:E3 for column C etc.  Arvi Laanemets (When sending email, use address arvil<Attarkon.ee) "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




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




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 
Display Modes  


Similar Threads  
Thread  Forum  
Naming column in Index Function  Excel Worksheet Functions  
Aligning Two Lists in Excel  Excel Discussion (Misc queries)  
which formula or function searches for a value in a range of cell.  Excel Worksheet Functions  
which formula or function searches for a value in a range of cell.  Excel Worksheet Functions  
Can you average data in 1 column based on a range of values in another?  Excel Worksheet Functions 