Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a cell reference from a value within a cell?
I need to use a value in a cell to create a cell reference. What I'd like to
do is almost accomplished by the address function, but address returns it as text rather than a cell reference. Example: I have numerous sample records in a spreadsheet that summarizes sampling results. The number of records varies will vary from workbook to workbook, and I'm trying to automate through use of a template without getting into visual basic stuff (which I know very little about). So,I use the count function in Cell A3 to count the number records on the sheet. The result comes back a whole number...say "20" in this case. Then, I want to refernce a range of cells that includes cell C4:C24 (i.e. the vertical range based on the 20 records)in order to count all the blank cells in the range. I looked through all the available functions, and couldn't find anything that I thought would help (except address). Anybody have suggestions? Thanks in advance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a cell reference from a value within a cell?
How about the offset function, as in =COUNTIF(OFFSET($C$4,0,0,20,1),"")
The 20,1 at the end indicates that the result should include 20 rows, 1 column. Since you've calculated the number of rows in A3, =countif(offset($C$4,0,0,A3,1),""). "Fishboy" wrote: I need to use a value in a cell to create a cell reference. What I'd like to do is almost accomplished by the address function, but address returns it as text rather than a cell reference. Example: I have numerous sample records in a spreadsheet that summarizes sampling results. The number of records varies will vary from workbook to workbook, and I'm trying to automate through use of a template without getting into visual basic stuff (which I know very little about). So,I use the count function in Cell A3 to count the number records on the sheet. The result comes back a whole number...say "20" in this case. Then, I want to refernce a range of cells that includes cell C4:C24 (i.e. the vertical range based on the 20 records)in order to count all the blank cells in the range. I looked through all the available functions, and couldn't find anything that I thought would help (except address). Anybody have suggestions? Thanks in advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a cell reference from a value within a cell?
Hi
P.e. you have "Sheet1" in A1 and "C2" in B1 =INDIRECT("'" & A1 & "'!" & B1) returns a value from cell Sheet1!C2 Arvi Laanemets "Fishboy" wrote in message ... I need to use a value in a cell to create a cell reference. What I'd like to do is almost accomplished by the address function, but address returns it as text rather than a cell reference. Example: I have numerous sample records in a spreadsheet that summarizes sampling results. The number of records varies will vary from workbook to workbook, and I'm trying to automate through use of a template without getting into visual basic stuff (which I know very little about). So,I use the count function in Cell A3 to count the number records on the sheet. The result comes back a whole number...say "20" in this case. Then, I want to refernce a range of cells that includes cell C4:C24 (i.e. the vertical range based on the 20 records)in order to count all the blank cells in the range. I looked through all the available functions, and couldn't find anything that I thought would help (except address). Anybody have suggestions? Thanks in advance! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a cell reference from a value within a cell?
Awesome! Your rock, man! That did the job. Thanks for your help!
"bpeltzer" wrote: How about the offset function, as in =COUNTIF(OFFSET($C$4,0,0,20,1),"") The 20,1 at the end indicates that the result should include 20 rows, 1 column. Since you've calculated the number of rows in A3, =countif(offset($C$4,0,0,A3,1),""). "Fishboy" wrote: I need to use a value in a cell to create a cell reference. What I'd like to do is almost accomplished by the address function, but address returns it as text rather than a cell reference. Example: I have numerous sample records in a spreadsheet that summarizes sampling results. The number of records varies will vary from workbook to workbook, and I'm trying to automate through use of a template without getting into visual basic stuff (which I know very little about). So,I use the count function in Cell A3 to count the number records on the sheet. The result comes back a whole number...say "20" in this case. Then, I want to refernce a range of cells that includes cell C4:C24 (i.e. the vertical range based on the 20 records)in order to count all the blank cells in the range. I looked through all the available functions, and couldn't find anything that I thought would help (except address). Anybody have suggestions? Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Cell Reference locking | Excel Discussion (Misc queries) | |||
Cell reference problem | Excel Worksheet Functions | |||
how to include a cell reference that is contained in a cell withi. | Excel Worksheet Functions | |||
Cell Reference Math | Excel Worksheet Functions |