A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Setting up and Configuration
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dynamic range based on another column



 
 
Thread Tools Display Modes
  #1  
Old May 10th, 2010, 07:04 PM posted to microsoft.public.excel.setup
Carrie_Loos via OfficeKB.com
external usenet poster
 
Posts: 58
Default Dynamic range based on another column

I have a worksheet with several columns that I intend to update daily from a
data dump. I am trying to set up a dynamic range based on a previous column
(Column H) that will have a value for every cell within that column. The
column that needs updates (Column O) has blanks and it is those blanks that
will possibly have information in the data dump. I was thinking if I could
give it a dynamic range name then I could look, find and enter the data for
the blank cells. But I am stuck on how to write the dynamic range to equate
to the same number of cells from Column H for Column O. There are several
columns that I will be applying this to but all being the same length as
column H.

Can anyone assist?

--
Message posted via http://www.officekb.com

  #2  
Old May 11th, 2010, 08:01 AM posted to microsoft.public.excel.setup
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default Dynamic range based on another column

Hi

InsertNameDefine
Name RngO
Refers to =$O$1:INDEX($O:$O,COUNTA($H:$H))

or
Name lrow
Refers to =COUNTA($H:$H)

Name RngO
Refers to $O$1:INDEX($O:$O,lrow)

For more information take a look at a tutorial I wrote at
http://www.contextures.com/xlNames03.html
--
Regards
Roger Govier

Carrie_Loos via OfficeKB.com wrote:
I have a worksheet with several columns that I intend to update daily from a
data dump. I am trying to set up a dynamic range based on a previous column
(Column H) that will have a value for every cell within that column. The
column that needs updates (Column O) has blanks and it is those blanks that
will possibly have information in the data dump. I was thinking if I could
give it a dynamic range name then I could look, find and enter the data for
the blank cells. But I am stuck on how to write the dynamic range to equate
to the same number of cells from Column H for Column O. There are several
columns that I will be applying this to but all being the same length as
column H.

Can anyone assist?

  #3  
Old May 11th, 2010, 06:49 PM posted to microsoft.public.excel.setup
Carrie_Loos via OfficeKB.com
external usenet poster
 
Posts: 58
Default Dynamic range based on another column

Thanks!

Roger Govier wrote:
Hi

InsertNameDefine
Name RngO
Refers to =$O$1:INDEX($O:$O,COUNTA($H:$H))

or
Name lrow
Refers to =COUNTA($H:$H)

Name RngO
Refers to $O$1:INDEX($O:$O,lrow)

For more information take a look at a tutorial I wrote at
http://www.contextures.com/xlNames03.html
--
Regards
Roger Govier

I have a worksheet with several columns that I intend to update daily from a
data dump. I am trying to set up a dynamic range based on a previous column

[quoted text clipped - 8 lines]

Can anyone assist?


--
Message posted via http://www.officekb.com

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:51 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.