Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I create a cell reference from ADDRESS function return?
I am using the RAND function to generate a row number from which I want to
extract the contents of the cell at a specific column at that row. I have used the ADDRESS function to create a text string that is the cell reference. I am trying to use the CELL function with the "contents" info type but I can't figure out how to use the cell reference I have created to direct the function to the cell to get the contents. =ROUND((RAND()*(COUNTIF(A:A, "<"))-1),0) (places an integer number in cell F2) =ADDRESS(F2,1) (Returns the text string for the Cell in column A, row defined as number generated and placed in F2) =CELL("contents", ???) When I try to use the reference to the cell that the text string is in from the ADDRESS function, I get the text string back. When I nest Cell functions, I get an error. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I create a cell reference from ADDRESS function return?
Try
=CELL("contents",INDIRECT(F3)) -- __________________________________ HTH Bob "Coachdenny" wrote in message ... I am using the RAND function to generate a row number from which I want to extract the contents of the cell at a specific column at that row. I have used the ADDRESS function to create a text string that is the cell reference. I am trying to use the CELL function with the "contents" info type but I can't figure out how to use the cell reference I have created to direct the function to the cell to get the contents. =ROUND((RAND()*(COUNTIF(A:A, "<"))-1),0) (places an integer number in cell F2) =ADDRESS(F2,1) (Returns the text string for the Cell in column A, row defined as number generated and placed in F2) =CELL("contents", ???) When I try to use the reference to the cell that the text string is in from the ADDRESS function, I get the text string back. When I nest Cell functions, I get an error. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I create a cell reference from ADDRESS function return?
Hi,
If you want a random whole number for a cell address you don't need to =ROUND((RAND()*(COUNTIF(A:A, "<"))-1),0) Why not =RAND()*COUNTIF(A:A, "<")+1 =RANDBETWEEN(1,COUNTA(A:A)) None of these formula guarentee unique results. If this helps, please click the Yes botton Cheers, Shane Devenshire "Coachdenny" wrote: I am using the RAND function to generate a row number from which I want to extract the contents of the cell at a specific column at that row. I have used the ADDRESS function to create a text string that is the cell reference. I am trying to use the CELL function with the "contents" info type but I can't figure out how to use the cell reference I have created to direct the function to the cell to get the contents. =ROUND((RAND()*(COUNTIF(A:A, "<"))-1),0) (places an integer number in cell F2) =ADDRESS(F2,1) (Returns the text string for the Cell in column A, row defined as number generated and placed in F2) =CELL("contents", ???) When I try to use the reference to the cell that the text string is in from the ADDRESS function, I get the text string back. When I nest Cell functions, I get an error. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I create a cell reference from ADDRESS function return?
First off, your first formula (the one with RAND in it) can result in the
values 0 and -1 which would make bad row numbers. That aside, what would be wrong with using this formula instead of the CELL one... =INDEX(A:A,F2) where you would use whatever column reference you wanted to look up the value in? -- Rick (MVP - Excel) "Coachdenny" wrote in message ... I am using the RAND function to generate a row number from which I want to extract the contents of the cell at a specific column at that row. I have used the ADDRESS function to create a text string that is the cell reference. I am trying to use the CELL function with the "contents" info type but I can't figure out how to use the cell reference I have created to direct the function to the cell to get the contents. =ROUND((RAND()*(COUNTIF(A:A, "<"))-1),0) (places an integer number in cell F2) =ADDRESS(F2,1) (Returns the text string for the Cell in column A, row defined as number generated and placed in F2) =CELL("contents", ???) When I try to use the reference to the cell that the text string is in from the ADDRESS function, I get the text string back. When I nest Cell functions, I get an error. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
function to find value and return cell reference | Excel Discussion (Misc queries) | |||
Create a database query that does not reference an IP address. | Excel Discussion (Misc queries) | |||
How do I create a cell reference to return name of the tab? | Excel Discussion (Misc queries) | |||
How do I use a function to return the address of a cell? | Excel Worksheet Functions | |||
Using result from ADDRESS function as a cell reference itself | Excel Worksheet Functions |