Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do you have a formula range be determined by the location ofanother cell?
Random data set below for example. Using 1 column and many rows:
A 1) 40 2) 32 3) 42 4) 35 5) 12 .... no data in between .... 25) 39 26) 31 27) 25 28) 14 29) 09 This data will be manipulated by formulas that work on a fixed ($) range (ie, $A$1:$A$5) at some column to the left. I will be coping down these formulas and just updating the data in column A. The issue is that with all the ranges being fixed when I copy down the rows the ranges are not shifted down. If I removed the $'s everything will work, but what I'm trying to do requires the fixed ranges (and for my learning as well). I can find the address of the new range with address(), but I can't apply it to the formula as it results as a text in double quotes. Example: in cell A24 i can type in the following code: =address(row(A25),column(A25),1) = $A$25 then the formulas doing stuff I would like to use this address: =dosomethingwithfixedrange(A24:XX) Reiteration: I want to find the cell address of a particular cell and use that address as part of a fixed range in a different cell. Like telling the formula where to start a range... Any help will be MOST useful. Thanks, Daniel |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do you have a formula range be determined by the location ofanother cell?
I don't fully understand your post, but have a look at the INDIRECT
function in XL Help. This allows you to build up a cell address or range as if it were a string. Hope this helps. Pete On Feb 7, 4:45*pm, Daniel wrote: Random data set below for example. Using 1 column and many rows: * * A 1) 40 2) 32 3) 42 4) 35 5) 12 ... no data in between ... 25) 39 26) 31 27) 25 28) 14 29) 09 This data will be manipulated by formulas that work on a fixed ($) range (ie, $A$1:$A$5) at some column to the left. *I will be coping down these formulas and just updating the data in column A. *The issue is that with all the ranges being fixed when I copy down the rows the ranges are not shifted down. *If I removed the $'s everything will work, but what I'm trying to do requires the fixed ranges (and for my learning as well). *I can find the address of the new range with address(), but I can't apply it to the formula as it results as a text in double quotes. Example: in cell A24 i can type in the following code: =address(row(A25),column(A25),1) = $A$25 then the formulas doing stuff I would like to use this address: =dosomethingwithfixedrange(A24:XX) Reiteration: I want to find the cell address of a particular cell and use that address as part of a fixed range in a different cell. *Like telling the formula where to start a range... Any help will be MOST useful. Thanks, Daniel |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do you have a formula range be determined by the location ofanother cell?
Let me try explaining differently..
I have formulas dependent on Row numbers (ie, fixed ranges). However, there are purposeful spaces being data sets and by spaces I mean blank rows. As I move down to lower rows (higher numbers) i want to copy my formulas down. However, because they use fixed ranges, they don't move down. I want to use the address() or some other technique to update the fixed ranges... Example: A1 B1 1) 3 COUNT($B$1:$B$5) 2) 5 3) 3 4) 5) 1 .... 10) 1 COUNT($B$1:$B$5) 11) 12) 13) 9 14) 8 When I copy down the formula in B1 the fixed range remains the same of course. However, I want to keep the copied formula (in B10) to be fixed, which means the ranges need to be updated. I want to do this automatically, instead of manually. Thanks, Daniel On Feb 7, 12:06 pm, Pete_UK wrote: I don't fully understand your post, but have a look at the INDIRECT function in XL Help. This allows you to build up a cell address or range as if it were a string. Hope this helps. Pete On Feb 7, 4:45 pm, Daniel wrote: Random data set below for example. Using 1 column and many rows: A 1) 40 2) 32 3) 42 4) 35 5) 12 ... no data in between ... 25) 39 26) 31 27) 25 28) 14 29) 09 This data will be manipulated by formulas that work on a fixed ($) range (ie, $A$1:$A$5) at some column to the left. I will be coping down these formulas and just updating the data in column A. The issue is that with all the ranges being fixed when I copy down the rows the ranges are not shifted down. If I removed the $'s everything will work, but what I'm trying to do requires the fixed ranges (and for my learning as well). I can find the address of the new range with address(), but I can't apply it to the formula as it results as a text in double quotes. Example: in cell A24 i can type in the following code: =address(row(A25),column(A25),1) = $A$25 then the formulas doing stuff I would like to use this address: =dosomethingwithfixedrange(A24:XX) Reiteration: I want to find the cell address of a particular cell and use that address as part of a fixed range in a different cell. Like telling the formula where to start a range... Any help will be MOST useful. Thanks, Daniel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert # of Rows determined by cell | Excel Discussion (Misc queries) | |||
Find DMIN in a column range determined by a number in another cell | Excel Worksheet Functions | |||
Saving a pre-determined range of cells as text? | Excel Discussion (Misc queries) | |||
Getting a value from a cell that is determined by a formula | Excel Discussion (Misc queries) | |||
How do I generate a sum, determined by cell color? | Excel Worksheet Functions |