Easy one..? #4

Hi,

What I was trying to do was to come up with a way of changing th
reference range for a MIN function in any column given a variabl
number of rows given by a named range "NoProjects".

I slept on it and came up with a much cleaner solution that didn't nee
the column ref.

MIN(A1:OFFSET(A1,NoProjects-1,0))

Thanks for the ideas,

Gromi

--
Gromi
-----------------------------------------------------------------------
Gromit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=92
View this thread: http://www.excelforum.com/showthread.php?threadid=27791

0
11/14/2004 10:49:06 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
574 Views

Similar Articles

[PageSpeed] 58

On Sun, 14 Nov 2004 16:49:06 -0600, Gromit
<Gromit.1fqghb@excelforum-nospam.com> wrote:

>
>Hi,
>
>What I was trying to do was to come up with a way of changing the
>reference range for a MIN function in any column given a variable
>number of rows given by a named range "NoProjects".
>
>I slept on it and came up with a much cleaner solution that didn't need
>the column ref.
>
>MIN(A1:OFFSET(A1,NoProjects-1,0))
>
>Thanks for the ideas,
>
>Gromit

Sometimes it pays to take a step back and look at the problem again.  Glad you
found a simpler solution.


--ron
0
ronrosenfeld (3122)
11/14/2004 11:16:27 PM
Reply:

Similar Artilces:

SUM Question #4
In a programming language, if the variable "x" has a current value of 10, the expression x = x + 2 will increment the value of x to 12 and x = x + 3 will increment the value of x to 15 and so forth. Can that same logic be done in Excel? e.g. If cell A1 currently has a value of 10 and I type in a 2 in cell B1, can I increment the value in A1 to 12 and if I tab over to C1 and then tab back to B1 and type in a 3, can the value in A1 now reflect 15 and so forth. The goal is to not need to use a new cell each time I want to type in a new value (loaves of bread in my real life example)....

one note opens all folders
For a couple of weeks now onenote opens all the folders on my computer as the default. I just want it to do the 'explore' option as normal. It only opens folders in onenote, not the documents or other file types contained within them, they open as normal. I've tried to reset the defaults for folders through control panel with no success. I've read you can delete a key in the registry to fix this but I'm not sure about doing this. My computer runs vista and onenote 2007. I'm not sure what's caused the problem, my computer's aniti-virus Kaspe...

Cells #4
I have a person that wants to know if there is a way to automatically adjust the cell to autofit. i.e. Let's say the column width is 10 and each cell contains variable length words. Is there a way to "fill" the cell with only that one word? Stretch it? Thanks in advance, Darryl Hi AFAIK no chance to do this. You could calculate the cell width, the font size, etc. and insert some spaces but this would not be accurate# >-----Original Message----- >I have a person that wants to know if there is a way to >automatically adjust the cell to autofit. i.e. Let's sa...

Easy money, you will not become rich, but it works.
Earn money on the net This is a way for earning some money online, you will not become a rich person, but it works!!! You will earn money by reading ads, and also by referring friends. You simply click a link and view a website for 30 seconds to earn money. You can earn even more by referring friends. You'll get paid $0.0150 for each website you personally view and $0.01 for each website your referrals view. There are several pages you can use. My favourites are: http://www.foxcash.net/?r=epasf http://www.neobux.com/?rh=6570617366 If you know another good page, please tell me. ...

Count Formula #4
I have a spreadsheet with three tabs. They all have people requesting time off...first sheet is for people requesting more than a week off - 2nd for 1 week at a time - 3rd for people just wanting 1 day off. The sheets have their name then 2 columns one for days off starting and the other for days off finishing (With the exception of people just wanting one day off) I would like to have another sheet counting the number of people off on a daily basis. It is easy enough using "counif" for the people asking for 1 day off...but I can't seem to easily break out the weekly...

Vlookup problem #4
Hi I have a set of data where colum A has a lerner Name Column B has the different units that learner is enrolled for Example English, Maths, Physics, Biology (Max 4 units for each learner). Column C has marks for each unit. So one learner can have 4 Rows and 3 columns, now when i want to lookup function, I want it to lookup for a learner Garry and find his marks in each unit. How can i do it? Please help, it will be much appriciated.! Thanka Does sorting the data by learning bring up the results you're looking for? "Garry" wrote: > Hi > I have a set of data where c...

How to copy one worksheet vith command button???
Does anybody know? Thank you for reading this especially if you are able to help me out on this. In xl2003 menus: Edit|move or copy sheet. Choose copy on the next dialog. You can see the same option if you rightclick on the worksheet tab. On 10/28/2010 17:39, DVAL wrote: > Does anybody know? > > Thank you for reading this especially if you are able to help me out on this. -- Dave Peterson With VBA!!! anyone??? "DVAL" <davaco@net.hr> wrote in message news:iacu31$gkn$1@news2.carnet.hr... > Does anybody know? > > Thank you for reading this especially...

More than one BackOrder in the same PO
Hi, Could we have more than one back order associated with the same PO in POP using Purchasing Order Preview? Thank you -- Gilberto Villegas GP Business Solutions partner Gilberto, Are you asking about linking multiple Sales Orders to one PO? If it is the case, Yes, you can link it. -- Thanks Janakiram M.P. MCP-GP http://janakirammp.blogspot.com "Gilberto" wrote: > Hi, > > Could we have more than one back order associated with the same PO in POP > using Purchasing Order Preview? > > Thank you > > -- > Gilberto Villegas > GP Business S...

CDHtmlDialog #4
How do i disable Drag & Drop in my CDHtmlDialog derived class Regards Manoj Jangid Hi, There is an answer: http://www.codeproject.com/dotnet/HtmlSkinCDHtmlDialog.asp by put_RegisterAsDropTarget(VARIANT_FALSE); Marcin Domaslawski U�ytkownik "Manoj Jangid" <manoj.jangid@systweak.net> napisa� w wiadomo�ci news:u9pt32IBHHA.2316@TK2MSFTNGP04.phx.gbl... > How do i disable Drag & Drop in my CDHtmlDialog derived class > > Regards > > Manoj Jangid > > ...

external email delayed (Status 4.4.7)
(We're running Exchange2000. Internal IP is NAT-ed to static external IP.) We're trying to send to a recipient (over in Germany) and the messages are not getting through. First we get the following type of notification Action: delayed Status: 4.4.7 And then another "Undeliverable" notification indicating a timeout: "Could not deliver the message in the time limit specified. Please retry or contact your administrator." What's different about our setup is that we're using a third-party spam filtering company, which may be creating some issues with Reve...

CRM 4.0 can not go online
Whenever we click on the "Go online" button in Outlook, we receive this message "An unknown error has occurred. Retry the action. If the problem persists, contact your system administrator". We've tried to reconfigure the CRM through the "Configuration Wizard", it would force CRM to online status, but if we go offline and go online again, we have same error. We have thought it is something to do with the VPN setting as all of our sales people work remotelly but now it happens to people in the office too. We just have had this issue lately, CRM 4.0 worke...

Multiple Outlook users on one machine
I have a workstation (98SE)that off-site employees use when they come into the office. I currently have them using OWA from the workstation, but it is starting to be a hassle. Is there a way I can configure Outlook to prompt for a username and password when opening, and have it access the appropriate mailbox on the Exhange 2000 server? Thanks! Slim <anonymous@discussions.microsoft.com> wrote: > I have a workstation (98SE)that off-site employees use > when they come into the office. I currently have them > using OWA from the workstation, but it is starting to be > a ...

how to copy formula from one workbook to another
Hi! I have a blank workbook containing many formulas and many worksheets. I use this to create a new workbook everymonth and enter data. After a while, I found a way to improve in a subset of formulas. How can I go back and reapply these formula to the entire workbook (including all worksheets, some of them are identical but others are different) without doing lot of manual work? thank you, prashant -- How about the paste special? It didn't work or not likely what you wer looking for -- hidek ----------------------------------------------------------------------- hideki's P...

Entering Multiple References in One Cell
Scenario: ######I am working on two different Tabs in an Excel Worksheet ###### Let's call them Tab A and Tab B ###### I have two dates (3/1/2008 & 4/1/2008) in two seperate cells in Tab A ######I would to reference them and have both values show up in a single cell Example: Tab A Cell 1 - 3/1/2008 Tab A Cell 2- 4/1/2008 Goal: Tab B Cell 1 - 3/1/2008 , 4/1/2008 Thanks On Sep 26, 2:37=A0pm, marc <m...@discussions.microsoft.com> wrote: > =A0Scenario: > ######I am working on two different Tabs in an Excel Worksheet > ###### Let's call them Ta...

CRM 4 Printing Workflows
We are using CRM4 and have created a number of workflows. I was just wondering if there would be any way to print these workflow, or create a document showing the steps of the workflow? This would just be very useful to save me alot of documentation time. Apart from a screen shot no printing option is available. Technically an application could be build to do this but I am pretty sure that there is nothing out there at the moment. -- Patrick Verbeeten (MCPD) Lead Developer Aviva IT Extended Entity and Plug-in browser: http://www.patrickverbeeten.com/maps/CrmTool.aspx "Rob...

DoModal throws an access violation only for one language
Hi, I've posted the first part of my issue here and it got resolved. The second part is what I am still struggling with. It consists of 2 issues. Perhaps someone would be able to help. Please excuse the repeated post. I have a few dialogs, icons, bitmaps, strings that I've localized in the following way: I've created a separate resource only dll which has a separate RC file for each language. This project also has a different build configuration for each language. So that in that configuration only that dll will build and the resources pane in the project properties will be se...

how do I change picture resolution of all pictures at one time?
I have a large catalogue with over 150 pictures. I have the high res pictures in the file for commercial printing, however would like an e-mailable catalogue and in order to do this I need to change the resolution of all the pictures. Is there a way of doing this all at once? Thanks, Stephen print the cat. to a pdf and email the pdf to your clients. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Stephen" <Stephen @discussions.microsoft.com> wrote in message news:344EE8B6-8748-4271-B8C6-C77663F7AB80@microsoft.com... > I have a large catalogue...

Error Message #4
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel I receive an error message for ALL Microsoft products when opening the application-"An unexpected error ocurred while trying to load the Microsoft framework library" The applications work for Word and Xcel but I get an error message for PowerPoint that shuts down the application when it trys to open a file. Can anyone provide insight? Thanks, Jim Framework errors are almost always the result of the software not being updated. Since you don't indicate your current update levels of Office or OS X I'd ...

More than one Table
I have an Old table TABLE 1 in an application which has a check fields CHK1, CHK2 in it. I have recently upgraded the application so that the check field are now a series of records in a sub-form TABLE 2. QUESTION I want to increment through TABLE 1 one record at a time, then run a conditional statement to see if CHK1, CHK2 is yes in each respective check field and if it is I want to open TABLE 2 and add record to TABLE 2 so that reflects the respective CHKn. My question is can 2 recordsets be open simultaneously and if so how do I swap between the 2 so that my VBA applic...

Emergency: Move Outlook from one computer to another
I have a computer that has a fried motherboard that needs to be sent in for repairs. I need to move my Outlook 2K from the broken machine's harddrive to a backup machine. I can't find the default Outlook .pst or whatever file to the new machine. Does anyone know how to do this? Take a look at these pages for info on Outlook data backup: http://www.slipstick.com/config/backup.htm -- Russ Valentine [MVP-Outlook] "Martin" <Martin@TWCourts.com> wrote in message news:058801c3c441$039b17c0$a301280a@phx.gbl... > I have a computer that has a fried motherboard that nee...

two companies one Exchange 2003 server
Can I set up two storage groups one for companyA and one for companyB and have an administrator at companyB administer the mailboxes in companyB while I can administer the mailboxes in companyA and companyB. Both our domains are in the same forest. We only have one exchange server and my IT director wants to separate them as much as possible with out buying another exchange 2003 server. What are some examples of what other people have done in this situation? Thank You, Chris Hi, Have a look at the following webcast, it will provide ideas how to delegate control. http://support.microsoft....

CRM 4.0 suddenly unusable
I only installed CRM 4.0 5 weeks ago, and only began loading it with real data 2 weeks ago. It seemed to be working fine on Friday, but this (Monday) morning CRM is no longer accessible through the Outlook Client or the web interface. The web interface gives the error message below. I have logged onto the SQL server and it appears to be fine. I can't understand what happened when not a single person was in the office over the weekend, and neither machine appears to have been reset due to a MS Update. Any ideas? Server Error in '/' Application --------------------------...

Reading text files one line at a time
Hello everyone, I'm using Visual Studio .Net and I would like to know how can I read a text file, line by line? Knowing that at the end of each line there is a new line character. Thanks, Geo Use the CStdioFile MFC class. -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "Geo" <Geo@discussions.microsoft.com> wrote in message news:60119E6F-2BB2-4E4B-B689-F7BB1FE0706D@microsoft.com... > Hello everyone, > > I'm using Visual Studio .Net and I would like to know how can I read a text > file, line by line? Knowing that at the end o...

vcards export just one email address
Hello! When I export a contact with more than one email address to a vcard file and import it later again - there is just one email address in the vcard. when I open the vcard file in a text editor I see always just one email address. Does anyone know about this problem and how the export and import vcards properly in outlook 2003? thanks! Bjorn ...

Receive one message to multiple user mailboxes
Hello all, Does anyone know if there is a way to have a single message routed to multiple users? When they had an ISP handling the mail, they just set Outlook to save the messages on the server, and had multiple users pull mail from that box. Now with Exchange Server, they want a message sent to admin@domain.com to drop into 2 or 3 different user mailboxes. Is this possible? or do they have to setup a common mailbox and give the users access to it? or other suggestions?? Thank you in advance. you need only to configure a distribution group with the smtp address admin@domain.com, ...