Excel Statistics 05: Cell References in Formulas

Hello All,

I need to use a number that was calculated from a formula in a cell
reference. Here is the situation: I have a large spreadsheet that
changes periodically. I need to count the number of items in that
spreadsheet(i will use COUNTA - already works) and then take that
number and use it as a row number in a cell reference like this:

In cell H1: =COUNTA(A2:A9999)

Then take that number in cell H1 and insert that into say $J$XXXX where
XXXX represents the number in cell H1.

I'm looking to increase the calculation speed of the workbook by only
calculating cells that I need to.

Thanks for the help,
Jason


--
jhockstr

I tried to do that but it will not work in the actual cell reference.
I'm stumped.



humejap Wrote:
> Have you tried the formula
>
> =Concatenate("$J$",H1)
>
>
> --
> humejap
> ------------------------------------------------------------------------
> humejap's Profile:
> http://www.excelforum.com/member.php...fo&userid=5506
> View this thread:
> http://www.excelforum.com/showthread...hreadid=395098


--
jhockstr

Goto is great for highlighting numerical constants and text. But for formulas I can only highlight the $B$24*(1-C7) kind, what if I'd like to just find numbers only formulas like "23566+45643+6767678"?

I am trying to generate the differences between numbers in two columns, though I occasionally have to add a row to one of the columns. When I do this the formula to generate the difference shifts to reflect the addition of the new row. I would like to have it keep the formula the same. For example, if I have:

A B Sum
5 4 9 (C2 = A2 + B2)
3 9 12
8 6 14
2 3 5

I want to be able to add a row to column a without the sum formula changing to accommodate the shift. I would like to have it look like the following:

A B Sum
5 4 9 (C2 = A2 + B2)
9 9 (C3 = A3 + B3)
3 6 9 etc.
8 3 11
2 2

However, when I shift down I end up with C2 = A3+B2, C3 = A4+B3, etc.

I would essentally like to be able to use an absolute cell reference and then copy the formula down column c. Thanks in advance for any advice.

Chris

i'm working on a spreadsheet that allows a user to select a state, and then does a vlookup on another worksheet to return the cell. for example, if you type in AK, it will return C3. C3 is the upper left cell of 6x7 table.

i want my chart to automatically update based on that cell reference C3, so i have attempted to create named ranges to reference in the chart source data. the chart also needs to be automatically updated when new columns are added to the report, which makes it a little trickier. Data is the name of the worksheet that contains the data tables.

here is the named range that seems to be working for the data labels, with fixed rows C & D):
=OFFSET(Data!$C$1,,,2,COUNTA(Data!$C$1:$IV$1)+1)

here is the named range for the values, which works when you start at a fixed value such as C3:
=OFFSET(Data!$C$3,,,6,COUNTA(Data!$C$1:$IV$1)+1)

in the formula immediately above, i need to somehow replace the "$C$3" with the cell that is referenced to begin with using the vlookup. so that any time the user changes the state, the range begins from the updated cell.

can you help me???

I'd love to find an elegant way to do something.

I've got weekly information appended below each other in one sheet, and I'm
pulling data from it in another by using VLOOKUP. Right now I get the
beginning and ending rows for the lookup range, but have to manually input
them into the formula -- is there a way to build the array automatically for
the formula? I tried building and assembling text to represent the wanted
rows, but got errors. The trick is that the number of rows is not constant
from one week to the next.

Here's what I'm using now (manually setting rows in the third calc based on
values returned from the first two calcs):

beginning row=+VLOOKUP(W$36,'0740'!$A4:$D750,4,FALSE)
(look for the start date of the week, say 372 in this case)

ending row=+VLOOKUP(W$36+7,'0740'!$A4:$D750,4,FALSE)
(look for the start date of the next week, say 396 in this case)

the value lookup=+VLOOKUP($A40,'0740'!$A$372:$D$396,3,0)
(look for the value within the row range)

Any help to not have to manually update rows for future weeks will be
appreciated!

Hi - I have a problem that I thought would be solved by using absolute references, but it's not. I have a spreadsheet that looks like this:
__A _______B_______C
1 Alberts Alberts =IF(NOT(A1=B1),"different","")
2 Alberts Alberts =IF(NOT(A2=B2),"different","")
3 Brooks Alberts =IF(NOT(A3=B3),"different","")
4 Brooks Brooks =IF(NOT(A4=B4),"different","")
As you would expect, C3 displays "different", all other cells in column C are blank. So far so good. However, what I want to do, now that I've identified the "different" row, is to move A3 down one row, so things look like this:
__A _______B_______C
1 Alberts Alberts =IF(NOT(A1=B1),"different","")
2 Alberts Alberts =IF(NOT(A2=B2),"different","")
3 ______ Alberts =IF(NOT(A3=B3),"different","")
4 Brooks Brooks =IF(NOT(A4=B4),"different","")
5 Brooks etc...........
Please note that I'm doing this because there is other information in each row, not because my OCD is out of control. Also, in my real formula I'm handling cells that are blank. Anyway, my problem is that I don't get the above. Rather, Excel adjust the values in my formula so I get:
__A _______B_______C
1 Alberts Alberts =IF(NOT(A1=B1),"different","")
2 Alberts Alberts =IF(NOT(A2=B2),"different","")
3 ______ Alberts =IF(NOT(A4=B3),"different","")
4 Brooks Brooks =IF(NOT(A5=B4),"different","")
5 Brooks etc...........
This is NOT what I want. I want the formula in C3 to continue to reference A3, not A4. As I understand it, absolute references are only for when you want to copy the cell that they are used in, which I'm not doing. Anyway, I tried saying $A$3, but it didn't help.
How do I stop this behavior?
Thanks!

I have a formula that references data on another spreadsheet with multiple worksheets. I have a new worksheet for each month (Jan, Feb, Mar, ...).

How can I copy formulas in series that keep the cell references the same, but change to the appropriate month. So for the forumula below, I want to fill a series down in a column to represent each month.

Right now I have to go in and edit it to change the month to the next month (i.e. change Jan to Feb.)

An example of the formula is below. In this case, I would want to fill the series down a column and have the months update in series but keep the cell reference the same.


=SUM('[New 2008 sales report linked.xls]Jan'!$M$19,'[New 2008 sales report linked.xls]Jan'!$N$19,'[New 2008 sales report linked.xls]Jan'!$Q$19,'[New 2008 sales report linked.xls]Jan'!$L$47,'[New 2008 sales report linked.xls]Jan'!$K$47,'[New 2008 sales report linked.xls]Jan'!$S$47)

Hi - I'm new here. I've looked over a few pages of posts but didn't find a topic I thought would address what I would like to do.

Suppose I have the numbers 1-10 in cells A1-A10 respectively

suppose in cell A12 I have a value x and in cell A13 I have a value y.

I would like to put a formula in cell A15 that sums up the values in cells Ax to Ay. For example, if the value in cell A12 is 4 and the value in cell A13 is 6, the result of the formula in cell A15 would be the sum of A4,A5,A6 = 15.

I have had no luck coming up with a formula that does this, or in searching for a formula that does this. A little help, please?

Thanks,
Peter