 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2006A question about variable in a function
Good morning, I was looking to find out if i can insert in the range criteria of a function a variable. I will explain what i mean with an example. Assuming we have the following function =COUNTIF($C$6:$G$50;A2), and i want the number 50 to be a variable, because i have this function in many cells, i want to be able to change this value at once, for example taking the data of another cell and put it in the function. I am open to suggestion if there is another way doind this. =COUNTIF(INDIRECT("$C$6:$G$"&A1);A2)  SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Good morning, > I was looking to find out if i can insert in the range criteria of a [quoted text clipped - 5 lines] > of another cell and put it in the function. I am open to suggestion if there > is another way doind this. > =COUNTIF(INDIRECT("$C$6:$G$"&A1);A2) Hi Bob, I see what the above Indirect is doing (concatenating the number in A1 to the $G$ cell ref as the column number) but would you mind explaining the use of ""? As this appears to be something I could use and I'd like to understand the rules surrounding the use of inverted commas. Many thanks, Jason That is just quotes, because it is passing a string to the INDIRECT function. SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > > =COUNTIF(INDIRECT("$C$6:$G$"&A1);A2) > [quoted text clipped - 8 lines] > > Jason > Good morning, > I was looking to find out if i can insert in the range criteria of a [quoted text clipped - 5 lines] > of another cell and put it in the function. I am open to suggestion if there > is another way doind this. Nikos / Bob Something I don't understand is the semi-colon in the formula: =COUNTIF($C$6:$G$50;A2) Is it a typo? I've not come across a semi-colon being used in countif, only the default format :- COUNTIF(range,criteria). If it isn't a typo, how does it work? Because I can't get it to work? Jay Different 'national' versions of XL use different delimiters. As you've noticed, European versions use the semi-colon " ; " instead of the comma " , ". SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > > Good morning, > > I was looking to find out if i can insert in the range criteria of a [quoted text clipped - 18 lines] > > Jay > Different 'national' versions of XL use different delimiters. > As you've noticed, European versions use the semi-colon " ; " instead of the > comma " , ". Ah, that makes quite a few things clearer :-) Many thanks, -Jay- I realise I've missed the boat slightly on this one but an alternative to Bob's Indirect suggestion..... =COUNTIF($C$6:INDEX($C:$C;A1);A2) Signaturedaddylonglegs
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.