Formula selecting first 1400 rows of column, need ALL of it. HELP

[Jennifer C.]

Formula selecting first 1400 rows of column, need ALL of it. HELP!!

Okay when I evaluate a formula in an Excel file, I find this within the
formula when I step-in to it: "PRO!$C$2:$C$1400"

Basically, PRO is the name of the sheet in the workbook and I want to select
Column C. Now Column C is MUCH MUCH longer than 1400 rows. I can't get in
to fix this to make it say like 10,400. What can I do??

 

[JP]

You could post the formula!

--JP

 

[Max]

.. "PRO!$C$2:$C$1400"

.. I can't get in to fix this to make it say like 10,400.

One guess is that it is a multi-cell array formula. Click on any formula
cell within the range, press CTRL + "/" key to select the full extent of the
range. Edit it to the new range within the formula bar, then press
CTRL+SHIFT+ENTER to re-confirm it.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik
---

 

[Jennifer C.]

Sorry... I didn't think I needed to.

Here's the formula that's in the formula bar: =SUMIF(cat_col,$A157,debit_col)

When I evaluate the formula and then Step in at "cat_col" it expands to
this: "PRO!$C$2:$C$1400"

 

[Jennifer C.]

Thanks Max, unless I did it wrong it didn't work. I'm trying to select ALL
of Column C but even when I try to do that or just select the column it
doesn't work.

 

[Dave]

Hi,
Not sure I understand, but here's a suggestion. "cat_col" seems to be a
named range, representing "PRO!$C$2:$C$1400"
Is it just a matter of redefining this named range to "PRO!$C$2:$C$10000" ?
Regards - Dave.

 

[Max]

My earlier guess on your scenario was incorrect

From your clarification in the other branch:

=SUMIF(cat_col,$A157,debit_col)

To edit the 2 defined ranges: cat_col & debit_col,
just click Insert > Name > Define (options are there)
Select say: cat_col in the dialog box,
then you can edit the "Refers to" part of it (eg extend the range)
Click Add, then repeat for debit_col
Click OK when done
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik
---

 

[JP]

Those are names that are pointing to hard coded ranges. Edit the names
and you can change the definition.

Press Ctrl-F3 and select each name. You'll see the definition in the
Refers To box. Just click inside and press F2 to move around. You can
adjust your range there.

--JP

 

[Jennifer C.]

THANKS JP, I didn't need to do Ctrl F3, I just needed to go into the Name
Manager (I believe that may be a new feature in 2007).

THANKS AGAIN FOR YOUR HELP!!!

Jennifer

 

[JP]

Glad to hear it and thanks for letting us know it worked!

--JP