Remember Me?

#1
February 11th 05, 07:05 PM
 Jimboski Posts: n/a
Using a Text / Data output as a cell reference

I am trying to use the end of a column as a divisor and need to convert what
i guess is text into an actual cell reference if possible.
In column A, say there are 13 data points ending at cell A13.
I then try to turn that into a cell reference with the formula
="A"&TEXT(COUNTA(A1:A13,),"0") I put this formula in B1
the output of this is then A13
what i need this to do, is be able to divide any cell by A13.
The reason I am going through all this trouble is that there are many
columns and each has a different # of data points.
I hope this is somewhat clear. Thanks for any help

#2
February 11th 05, 07:31 PM
 Ken Wright Posts: n/a

For what you have asked you can just wrap it with INDIRECT(......) which
converts a textual representation of a reference to an actual reference.

Another way to find the last value:-

=LOOKUP(9.99999999999999E+307,A:A) for last numeric entry in a column

=LOOKUP(9.99999999999999E+307,1:1) for last numeric entry in a row

=LOOKUP(REPT("z",255),A:A) for last text entry in a column

=LOOKUP(REPT("z",255),1:1) for last text entry in a row

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Jimboski" wrote in message
...
I am trying to use the end of a column as a divisor and need to convert

what
i guess is text into an actual cell reference if possible.
In column A, say there are 13 data points ending at cell A13.
I then try to turn that into a cell reference with the formula
="A"&TEXT(COUNTA(A1:A13,),"0") I put this formula in B1
the output of this is then A13
what i need this to do, is be able to divide any cell by A13.
The reason I am going through all this trouble is that there are many
columns and each has a different # of data points.
I hope this is somewhat clear. Thanks for any help

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post lucky Excel Discussion (Misc queries) 0 February 8th 05 09:41 PM [email protected] New Users to Excel 2 January 6th 05 07:18 AM Rene Excel Discussion (Misc queries) 1 December 16th 04 04:34 PM [email protected] Excel Worksheet Functions 2 December 11th 04 12:05 AM Tom A Johnson Excel Worksheet Functions 2 November 11th 04 11:28 PM

All times are GMT +1. The time now is 06:53 AM.