Collecting Range Name values to VBA
I have a worksheet "Setup" where users type in a date in a cell named
"ChtDte" and a path and database name in a cell named "FLName". I am using
DOA to connect to a database and return a record set. The query used
"qryCOCostwRates" uses a date paramater. Because this sheet will be used by
several users all pointing to the database in different locations, I need to
know where they have the database.
I need to get the values in these two range names in the setup tab of the
spreadsheet so I can connect to the data, and provide a value for the
paramiter. This should be easy but I can't seem to find the solution. Any
ideas? Here's what I have tried:
Dim xlwsSetup As Excel.Worksheet
Dim xlrngFl As Excel.Range
Dim xlrngDte As Excel.Range
Dim xlWb As Excel.Workbook
Dim dbFln as String
Dim RptDte as Date
Set xlWb = ActiveWorkbook
Set xlwsSetup = xlWb.Worksheets("Sheet3")
Set xlwsSetup = ActiveSheet
Set xlrngFl = xlwsSetup.Range("FlName")
'The above is cell C3 but it may change so I named the range "FLName". The
user types "C:\Data\db\mydb.mdb" into the cell.
Set xlrngDte = xlwsSetup.Range("ChtDte")
'The above is cell C2 and it has a date in it like 2/28/09. If rows or
columns are inserted, I don't want to lose the reference so it's named ChtDte.
dbFln = xlrngFl.Value
RptDte = xlrngDte.Value
Is the file path and name (FLName) and the report date (ChtDte) now in my
variables?
--
Thanks in advance!
**John**
|
0 |
|
Utf
2/18/2010 10:39:01 PM
excel.programming
6508 articles. 
2 followers.
1 Replies
1757 Views
I got it worked out... Thanks for all the help...
--
Thanks in advance!
**John**
"John" wrote:
> I have a worksheet "Setup" where users type in a date in a cell named
> "ChtDte" and a path and database name in a cell named "FLName". I am using
> DOA to connect to a database and return a record set. The query used
> "qryCOCostwRates" uses a date paramater. Because this sheet will be used by
> several users all pointing to the database in different locations, I need to
> know where they have the database.
>
> I need to get the values in these two range names in the setup tab of the
> spreadsheet so I can connect to the data, and provide a value for the
> paramiter. This should be easy but I can't seem to find the solution. Any
> ideas? Here's what I have tried:
>
> Dim xlwsSetup As Excel.Worksheet
> Dim xlrngFl As Excel.Range
> Dim xlrngDte As Excel.Range
> Dim xlWb As Excel.Workbook
> Dim dbFln as String
> Dim RptDte as Date
> Set xlWb = ActiveWorkbook
> Set xlwsSetup = xlWb.Worksheets("Sheet3")
> Set xlwsSetup = ActiveSheet
> Set xlrngFl = xlwsSetup.Range("FlName")
>
> 'The above is cell C3 but it may change so I named the range "FLName". The
> user types "C:\Data\db\mydb.mdb" into the cell.
>
> Set xlrngDte = xlwsSetup.Range("ChtDte")
> 'The above is cell C2 and it has a date in it like 2/28/09. If rows or
> columns are inserted, I don't want to lose the reference so it's named ChtDte.
>
> dbFln = xlrngFl.Value
> RptDte = xlrngDte.Value
>
> Is the file path and name (FLName) and the report date (ChtDte) now in my
> variables?
>
>
> --
> Thanks in advance!
> **John**
|
|
0 |
|
Utf
2/19/2010 10:43:01 PM
Similar Artilces:
XmlSerializer Collection with Collections
Hello I got this working but it is not how I really want it, basically I have an xml file which has a root of <test> and can be filled with 3 different types of <question> elements with different attributes, all share a base set of 4, one of the question types can have children with <option> elements, this is how the xml looks after serialization.... If you notice there is an extra <SelectionList> around the <option>'s in the final <question>, how can I write my class so I don't have this extra <SelectionList> element....my class follows, you ...
Blank Cell if next cell value is 0
Hello: Please help me with this one: I like to have these cells: A1: Blue A2: 0 A3: Red A4: 1 A5: Green A6: 0 A7: Yellow A8: 7 convert to these: A1: A2: 0 A3: Red A4: 1 A5: A6: 0 A7: Yellow A8: 7 (if A2 value is 0 (zero), A1 should be blank and so on... Thanks in advance. Dervish Hi do you want a macro solution for this? or would be an output an an adjancent column be sufficient? -- Regards Frank Kabel Frankfurt, Germany "Dervish" <dervishme@comcast.net> schrieb im Newsbeitrag news:400aa860.0410281053.2aa1e388@posting.google.com... > Hello: > > Please help m...
How do I merge in a selected range of cells out of Excel?
copy / right click / insert copied cells ...
Evaluate a Range
I would like to create a formula that looks at a range with a specified criterion and when it finds the first entry in that range that meets that criterion returns the position of that data in the range. For example, given the following data beginning in A1 and extending to D1: 5,7 ,8,11 I would like to create a formula that looks for a value greater than 10 in that range and then returns the number 4 to signify that it is the fourth entry in that range. Thanks as always for your help. --- Message posted from http://www.ExcelForum.com/ One way: Array enter (CTRL-SHIFT-ENTER or CMD-RET...
Array range error question
When i try to redim my array i get an out of range error. can someone tel me why? Cells A1..A10 are filled with strings. thanks much Sub Get_names_array() Dim names_array() As Variant 'dynmanic Dim j As Long ReDim names_array(5, 1) j = 0 Range("A1").Select For i = 1 To 10 names_array(j, 0) = ActiveCell.Value names_array(j, 1) = ActiveCell.Address arraylimitFirst = UBound(names_array, 1) 'returns current dimension of 1st array limit, init = 5 If j >= arraylimitFirst Then ReDim Preserve names_array(10, 1) ' << RANGE ERROR ...
Formula to find last different value in a column?
I have a formula that is based on comparing the last value in a column. Basically of not null it performs an action. However I need the ones after that to be able to set up and find the last different value e.g. A B B B My formula so far is if Value<>"" then do X I even got it to say If Value<>"" AND Value not equal to prior record. Which would work for the second B But what I really need it to say (to get to A) is If Value <>"" and Value IS Equal to Last Record step backwards until you find the value of the first differe...
about Collections
Hi! The ArrayList support the ICollection and IEnumerable. Below is three ways to iterate through a collection of an instance of an ArrayList object. I just find it's easier to iterate though a collection by using the for loop or the foreach so my question is when is it better or more appropriate to use the Enumerator. static void Main(string[] args) { ArrayList list = new ArrayList(); list.Add(1); list.Add("hej"); //Using the foor loop for (int i = 0; i < list.Count; i++) { Console.WriteLine(li...
Summing Values that are True and Not Rounded
HELP! I have a problem at work where I have data in 3 columns that are derived using the ROUND function to 2 decimal places. In my total column, I want the values of the data added in it's true form. How do I do it? Hopefully I've explained it right. :confused: -- walkerdayle ------------------------------------------------------------------------ walkerdayle's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10021 View this thread: http://www.excelforum.com/showthread.php?threadid=477508 If you mean that you want to sum the un-rounded values, I th...
Easy way to delete names in a workbook?
I inherited a workbook. The guy defined about 50 names, referring to a bunch of other workbooks. Is there an easy macro to delete all defined names rather than doing it one by one? Try Jan Karel Pieterse's Name Manager utility. It's a free add-in that you can download here: http://www.bmsltd.co.uk/MVP/Default.htm Joe wrote: > I inherited a workbook. The guy defined about 50 names, > referring to a bunch of other workbooks. Is there an easy > macro to delete all defined names rather than doing it one > by one? -- Debra Dalgleish Excel FAQ, Tips & Book...
Pivot Tables
Is there a way to use pivot tables in Excel to accomplish the same result as an MS-Access process to "join" two tables on matching fields? For example, I have one range of data that contains employee time card information that includes a costing code for each time entry. I have a separate data range that contains, for each costing code, one or more records containing percentages that are used to allocate labor to specific business units. The desired result is a table or data range that contains all of the records from the time card range and all matching record...
server name
i just opened my account with windows mail and my email provider is windows live...but i did something wrong i know im no genius but can you please help, this is the error message that pops ups when i try to send a message *The host 'pop3' could not be found. Please verify that you have entered the server name correctly. Account: 'windows mail', Server: 'pop3', Protocol: POP3, Port: 110, Secure(SSL): No, Socket Error: 11001, Error Number: 0x800CCC0D Not even in the ballpark. Use the Configuration Settings on this page: http://mailcall.spaces.live.com/B...
How do you change a field name in 2002 Excel
I have copied and pasted a whole database from Works into the Excel program but can't seem to find a way to change the field names from A, B, C, to what I want as Last Names, First Names, etc. Aarrrrgh. It can't be THAT difficult! <G> Using the HELP did nothing for me thus I am here asking this silly Q. AnnE in MN You cannot change the Column letters from A, B, C etc. You can choose to have column and row headers not shown under Tools>Options>View Enter your titles(names) in row 1 then select A2 and Window>Freeze Panes to lock row 1 in view. Gord Di...
Summing & return highest value
Cell A1 may have a number entered. Cells A2 thru A13 may have numbers entered. I want cell A14 to display which ever has the higher value.... A1 or the sum of cell range A2:A13. What would be the formula? Please no VBA or macro's Try this: A14: =MAX(A1,SUM(A2:A13)) Does that help? *********** Regards, Ron XL2002, WinXP "PSU35" wrote: > Cell A1 may have a number entered. > Cells A2 thru A13 may have numbers entered. > I want cell A14 to display which ever has the higher value.... A1 or the sum > of cell range A2:A13. > > What would be the formula? Pl...
The pivottable field name is not valid
Hi i have 1 Excel file with a pivot table called PVT. It is reading data from another Excel file which i called Excel_DB. This Excel_DB file will get overwritten with data from Oracle DB using a data retrieval tool which can export data into excel file format. The strange think is, i do not know what indicators are stored in the original Excel_DB used to create the PVT. Reason : 1) if i export data into another excel file, copy and paste the data into Excel_DB, i can refresh data in PVT w/o having to open the file Excel_DB at all. 2) if i use the tool to overwrite Excel_DB, then when i try...
The name could not be resolved
Hello List After reinstalling Windows 2000 get msg: The name could not be resolved. The Microsoft Exchange Address Book was unable to log on to the Microsoft Exchange Server computer. Contact your system administrator if the problem persists. click ok. The name could not be resolved. The action could not be completed. user logs in OK to Windows 2000 server. client is Windows 2000 professional with service pack 4. Any ideas? Angus The typical fix for that error was to remove and re-install TCP/IP (from the Properties of the NIC). "Angus Comber" <angus@NOSPAMitelo...
Empty Range
Hi, I'm trying to do a function who delete all the row of a range I pass is argument and will copy them to an other sheet. Here is my function : Function RangeToDelete(ToBeDelete As Range, SheetsNameCopy As String) If WorksheetFunction.CountBlank(ToBeDelete) = 0 Then Selection.EntireRow.Copy Sheets(SheetsNameCopy).Paste Selection.EntireRow.Delete End If End Function I want to check if the range is empty first so I don't have an error cause I can't delete something that is empty. But this function doesn't work since the CountBlank is not workin...
3-D Address References and Names
It seems that 3-D cell addresses transfer to other workbooks (not worksheets) only as constants, such as "(specific cell value)". The record macro that results from that will have a paste information as "(specific cell value)", and not a variable. Does one have to go to Excel Visual Basic to express a variable, so the macro is reusable for other workbooks with different cell values? Thanks, Tom ...
Problem with a value in a variable
Hello, I'm trying to work with the events log : cmdlet "get-eventlog" I would like To test if I get some news events since two hours. This is my simple script : -------------------------------------------- $datedujour=$(get-date).AddHours(-2) $journal="Application" $listevents=get-eventlog $journal -Newest 1000 | where { ($_.timewritten -ge $datedujour) -and $_.entryType -match "warning|error"} $countevents=@($listevents).count IF ($countevents -gt 0) {$evtcheck="YES"} ------------------------------------------- But i ha...
Update Text Box Value
Maybe a simple question Can anyone tell me how I go about updating other text boxes from an input in one text box ?? I have an emloyee number that links to personnel information about an individual .. I would like to enter a pay number in one box ... click a button and their name etc is presented in the other boxes ... Can anyone enlighten me please Many thanks Hi Alan, '----------------------------------- > Maybe a simple question > > Can anyone tell me how I go about updating other text boxes from an input > in > one text box ?? > > I have an emloyee numb...
COUNTIF with multiple disjoint ranges, same criteria
Is there any way to specify multiple disjoint ranges in one COUNTIF? I.e. something like COUNTIF((A1:C3,D4:F6,G7:I9),"Y") I want to avoid COUNTIF(A1:C3,"Y") + COUNTIF(D4:F6,"Y") + ... -- � 2005 Kurt Swanson AB This formula works as long as it is not located within A1:I9 =SUMPRODUCT(--((A1:C3:D4:F6:G7:I9)="Y")) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Kurt Swanson" wrote: > Is there any way to specify multiple disjoint ranges in one COUNTIF? > > I.e. something like COUNTIF((A1:C3,D4:F6,G7:I9),"Y"...
Change name of Distribution List
How do I (can I?) change the NAME of a Distribution List? -- DavidC Not difficult and hardly undiscoverable. Open the DL. Change the Name field. I suspect there was some other question you meant to ask. This one was far too easy. -- Russ Valentine "DavidC" <DavidC@discussions.microsoft.com> wrote in message news:9E47744C-DCD2-435C-B703-F41E31C63569@microsoft.com... > How do I (can I?) change the NAME of a Distribution List? > -- > DavidC ...
Excel Combine Ranges
I have 4 different ranges in a worksheet and need to get the effective box/area of all four together including he areas in between the ranges so that there is on area to reference. What is the easiest way to do this. E.g. Range1: b2.c3 Range2: c20.d30 Range3: f8.g10 Range4: i30.k100 I need the resultant area reference to be b2:k100. Is there a quick way to do this? Thanks in advance. Hi Anne, One way: '=============>> Public Sub Tester002() Dim myRng As Range Dim ar As Range Set myRng = Range("B2:B3,C20:D30,F8:G10,I30:K100") For Each ar In myRn...
Dynamic Shart title = cell value
Have been using the dynamic title for years and it works GREAT.. Now problem is the chart title comes from another tab, formula references the other tab. Each chart has a tab with a dept. number and name.. Would like to take dept. number from chart and rename tab. Do have macro to do this if needed, but cannot get the dept. number into a cell on the graph page. And still thank you again for many years ago helping with the dynamic chart title - is great.. Hi John, Sounds like you need a concatinated title: Suppose your title is in cell A1 and reads "My Title" Then t...
How do I correct a #value! error
I have a somewhat complicated formula in that it refers to a separate spreadsheet. The formula works fine, but the problem seems to be that until I open the referred to spreadsheet, I get a #value! error. I know that I have used this type of formula before and didn't need to open the spreadsheet to get my answers. Why is it doing it now? It might help to let you know that the spreadsheet referred to was created by access as a query, but I don't see how that changes it as I save the query as an excel spreadsheet. Any help would be greatly appreciated, as the spreadsheet g...
Named range using conditional formatting #2
Hi, I have a named cell called Econvert which contains the conversion rate from Dollars to Euros. I am creating a template for others to use and I need to add a conditional format for when users forget to divide their inputs by the Econvert. For example, if they enter a salary number as $40,000 I want them to divide this number by Econvert so that the end result is =$40,000/Econvert. I would like the cell to become red if they forget to divide by Econvert which is why I think that conditional formatting would help. Any ideas? Thanks in advance. ...
Hello I got this working but it is not how I really want it, basically I have an xml file which has a root of <test> and can be filled with 3 different types of <question> elements with different attributes, all share a base set of 4, one of the question types can have children with <option> elements, this is how the xml looks after serialization.... If you notice there is an extra <SelectionList> around the <option>'s in the final <question>, how can I write my class so I don't have this extra <SelectionList> element....my class follows, you ...
Blank Cell if next cell value is 0
Hello: Please help me with this one: I like to have these cells: A1: Blue A2: 0 A3: Red A4: 1 A5: Green A6: 0 A7: Yellow A8: 7 convert to these: A1: A2: 0 A3: Red A4: 1 A5: A6: 0 A7: Yellow A8: 7 (if A2 value is 0 (zero), A1 should be blank and so on... Thanks in advance. Dervish Hi do you want a macro solution for this? or would be an output an an adjancent column be sufficient? -- Regards Frank Kabel Frankfurt, Germany "Dervish" <dervishme@comcast.net> schrieb im Newsbeitrag news:400aa860.0410281053.2aa1e388@posting.google.com... > Hello: > > Please help m...
How do I merge in a selected range of cells out of Excel?
copy / right click / insert copied cells ...
Evaluate a Range
I would like to create a formula that looks at a range with a specified criterion and when it finds the first entry in that range that meets that criterion returns the position of that data in the range. For example, given the following data beginning in A1 and extending to D1: 5,7 ,8,11 I would like to create a formula that looks for a value greater than 10 in that range and then returns the number 4 to signify that it is the fourth entry in that range. Thanks as always for your help. --- Message posted from http://www.ExcelForum.com/ One way: Array enter (CTRL-SHIFT-ENTER or CMD-RET...
Array range error question
When i try to redim my array i get an out of range error. can someone tel me why? Cells A1..A10 are filled with strings. thanks much Sub Get_names_array() Dim names_array() As Variant 'dynmanic Dim j As Long ReDim names_array(5, 1) j = 0 Range("A1").Select For i = 1 To 10 names_array(j, 0) = ActiveCell.Value names_array(j, 1) = ActiveCell.Address arraylimitFirst = UBound(names_array, 1) 'returns current dimension of 1st array limit, init = 5 If j >= arraylimitFirst Then ReDim Preserve names_array(10, 1) ' << RANGE ERROR ...
Formula to find last different value in a column?
I have a formula that is based on comparing the last value in a column. Basically of not null it performs an action. However I need the ones after that to be able to set up and find the last different value e.g. A B B B My formula so far is if Value<>"" then do X I even got it to say If Value<>"" AND Value not equal to prior record. Which would work for the second B But what I really need it to say (to get to A) is If Value <>"" and Value IS Equal to Last Record step backwards until you find the value of the first differe...
about Collections
Hi! The ArrayList support the ICollection and IEnumerable. Below is three ways to iterate through a collection of an instance of an ArrayList object. I just find it's easier to iterate though a collection by using the for loop or the foreach so my question is when is it better or more appropriate to use the Enumerator. static void Main(string[] args) { ArrayList list = new ArrayList(); list.Add(1); list.Add("hej"); //Using the foor loop for (int i = 0; i < list.Count; i++) { Console.WriteLine(li...
Summing Values that are True and Not Rounded
HELP! I have a problem at work where I have data in 3 columns that are derived using the ROUND function to 2 decimal places. In my total column, I want the values of the data added in it's true form. How do I do it? Hopefully I've explained it right. :confused: -- walkerdayle ------------------------------------------------------------------------ walkerdayle's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10021 View this thread: http://www.excelforum.com/showthread.php?threadid=477508 If you mean that you want to sum the un-rounded values, I th...
Easy way to delete names in a workbook?
I inherited a workbook. The guy defined about 50 names, referring to a bunch of other workbooks. Is there an easy macro to delete all defined names rather than doing it one by one? Try Jan Karel Pieterse's Name Manager utility. It's a free add-in that you can download here: http://www.bmsltd.co.uk/MVP/Default.htm Joe wrote: > I inherited a workbook. The guy defined about 50 names, > referring to a bunch of other workbooks. Is there an easy > macro to delete all defined names rather than doing it one > by one? -- Debra Dalgleish Excel FAQ, Tips & Book...
Pivot Tables
Is there a way to use pivot tables in Excel to accomplish the same result as an MS-Access process to "join" two tables on matching fields? For example, I have one range of data that contains employee time card information that includes a costing code for each time entry. I have a separate data range that contains, for each costing code, one or more records containing percentages that are used to allocate labor to specific business units. The desired result is a table or data range that contains all of the records from the time card range and all matching record...
server name
i just opened my account with windows mail and my email provider is windows live...but i did something wrong i know im no genius but can you please help, this is the error message that pops ups when i try to send a message *The host 'pop3' could not be found. Please verify that you have entered the server name correctly. Account: 'windows mail', Server: 'pop3', Protocol: POP3, Port: 110, Secure(SSL): No, Socket Error: 11001, Error Number: 0x800CCC0D Not even in the ballpark. Use the Configuration Settings on this page: http://mailcall.spaces.live.com/B...
How do you change a field name in 2002 Excel
I have copied and pasted a whole database from Works into the Excel program but can't seem to find a way to change the field names from A, B, C, to what I want as Last Names, First Names, etc. Aarrrrgh. It can't be THAT difficult! <G> Using the HELP did nothing for me thus I am here asking this silly Q. AnnE in MN You cannot change the Column letters from A, B, C etc. You can choose to have column and row headers not shown under Tools>Options>View Enter your titles(names) in row 1 then select A2 and Window>Freeze Panes to lock row 1 in view. Gord Di...
Summing & return highest value
Cell A1 may have a number entered. Cells A2 thru A13 may have numbers entered. I want cell A14 to display which ever has the higher value.... A1 or the sum of cell range A2:A13. What would be the formula? Please no VBA or macro's Try this: A14: =MAX(A1,SUM(A2:A13)) Does that help? *********** Regards, Ron XL2002, WinXP "PSU35" wrote: > Cell A1 may have a number entered. > Cells A2 thru A13 may have numbers entered. > I want cell A14 to display which ever has the higher value.... A1 or the sum > of cell range A2:A13. > > What would be the formula? Pl...
The pivottable field name is not valid
Hi i have 1 Excel file with a pivot table called PVT. It is reading data from another Excel file which i called Excel_DB. This Excel_DB file will get overwritten with data from Oracle DB using a data retrieval tool which can export data into excel file format. The strange think is, i do not know what indicators are stored in the original Excel_DB used to create the PVT. Reason : 1) if i export data into another excel file, copy and paste the data into Excel_DB, i can refresh data in PVT w/o having to open the file Excel_DB at all. 2) if i use the tool to overwrite Excel_DB, then when i try...
The name could not be resolved
Hello List After reinstalling Windows 2000 get msg: The name could not be resolved. The Microsoft Exchange Address Book was unable to log on to the Microsoft Exchange Server computer. Contact your system administrator if the problem persists. click ok. The name could not be resolved. The action could not be completed. user logs in OK to Windows 2000 server. client is Windows 2000 professional with service pack 4. Any ideas? Angus The typical fix for that error was to remove and re-install TCP/IP (from the Properties of the NIC). "Angus Comber" <angus@NOSPAMitelo...
Empty Range
Hi, I'm trying to do a function who delete all the row of a range I pass is argument and will copy them to an other sheet. Here is my function : Function RangeToDelete(ToBeDelete As Range, SheetsNameCopy As String) If WorksheetFunction.CountBlank(ToBeDelete) = 0 Then Selection.EntireRow.Copy Sheets(SheetsNameCopy).Paste Selection.EntireRow.Delete End If End Function I want to check if the range is empty first so I don't have an error cause I can't delete something that is empty. But this function doesn't work since the CountBlank is not workin...
3-D Address References and Names
It seems that 3-D cell addresses transfer to other workbooks (not worksheets) only as constants, such as "(specific cell value)". The record macro that results from that will have a paste information as "(specific cell value)", and not a variable. Does one have to go to Excel Visual Basic to express a variable, so the macro is reusable for other workbooks with different cell values? Thanks, Tom ...
Problem with a value in a variable
Hello, I'm trying to work with the events log : cmdlet "get-eventlog" I would like To test if I get some news events since two hours. This is my simple script : -------------------------------------------- $datedujour=$(get-date).AddHours(-2) $journal="Application" $listevents=get-eventlog $journal -Newest 1000 | where { ($_.timewritten -ge $datedujour) -and $_.entryType -match "warning|error"} $countevents=@($listevents).count IF ($countevents -gt 0) {$evtcheck="YES"} ------------------------------------------- But i ha...
Update Text Box Value
Maybe a simple question Can anyone tell me how I go about updating other text boxes from an input in one text box ?? I have an emloyee number that links to personnel information about an individual .. I would like to enter a pay number in one box ... click a button and their name etc is presented in the other boxes ... Can anyone enlighten me please Many thanks Hi Alan, '----------------------------------- > Maybe a simple question > > Can anyone tell me how I go about updating other text boxes from an input > in > one text box ?? > > I have an emloyee numb...
COUNTIF with multiple disjoint ranges, same criteria
Is there any way to specify multiple disjoint ranges in one COUNTIF? I.e. something like COUNTIF((A1:C3,D4:F6,G7:I9),"Y") I want to avoid COUNTIF(A1:C3,"Y") + COUNTIF(D4:F6,"Y") + ... -- � 2005 Kurt Swanson AB This formula works as long as it is not located within A1:I9 =SUMPRODUCT(--((A1:C3:D4:F6:G7:I9)="Y")) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Kurt Swanson" wrote: > Is there any way to specify multiple disjoint ranges in one COUNTIF? > > I.e. something like COUNTIF((A1:C3,D4:F6,G7:I9),"Y"...
Change name of Distribution List
How do I (can I?) change the NAME of a Distribution List? -- DavidC Not difficult and hardly undiscoverable. Open the DL. Change the Name field. I suspect there was some other question you meant to ask. This one was far too easy. -- Russ Valentine "DavidC" <DavidC@discussions.microsoft.com> wrote in message news:9E47744C-DCD2-435C-B703-F41E31C63569@microsoft.com... > How do I (can I?) change the NAME of a Distribution List? > -- > DavidC ...
Excel Combine Ranges
I have 4 different ranges in a worksheet and need to get the effective box/area of all four together including he areas in between the ranges so that there is on area to reference. What is the easiest way to do this. E.g. Range1: b2.c3 Range2: c20.d30 Range3: f8.g10 Range4: i30.k100 I need the resultant area reference to be b2:k100. Is there a quick way to do this? Thanks in advance. Hi Anne, One way: '=============>> Public Sub Tester002() Dim myRng As Range Dim ar As Range Set myRng = Range("B2:B3,C20:D30,F8:G10,I30:K100") For Each ar In myRn...
Dynamic Shart title = cell value
Have been using the dynamic title for years and it works GREAT.. Now problem is the chart title comes from another tab, formula references the other tab. Each chart has a tab with a dept. number and name.. Would like to take dept. number from chart and rename tab. Do have macro to do this if needed, but cannot get the dept. number into a cell on the graph page. And still thank you again for many years ago helping with the dynamic chart title - is great.. Hi John, Sounds like you need a concatinated title: Suppose your title is in cell A1 and reads "My Title" Then t...
How do I correct a #value! error
I have a somewhat complicated formula in that it refers to a separate spreadsheet. The formula works fine, but the problem seems to be that until I open the referred to spreadsheet, I get a #value! error. I know that I have used this type of formula before and didn't need to open the spreadsheet to get my answers. Why is it doing it now? It might help to let you know that the spreadsheet referred to was created by access as a query, but I don't see how that changes it as I save the query as an excel spreadsheet. Any help would be greatly appreciated, as the spreadsheet g...
Named range using conditional formatting #2
Hi, I have a named cell called Econvert which contains the conversion rate from Dollars to Euros. I am creating a template for others to use and I need to add a conditional format for when users forget to divide their inputs by the Econvert. For example, if they enter a salary number as $40,000 I want them to divide this number by Econvert so that the end result is =$40,000/Econvert. I would like the cell to become red if they forget to divide by Econvert which is why I think that conditional formatting would help. Any ideas? Thanks in advance. ...