Cell Selection
In Excel 2007 I am trying to select a number of adjacent columns. As cells in a number of rows crossing these columns are merged across more cloumns than I would like to select, Excel is selecting all the columns which are cut across by the merged cells. This did not happen in Excel 2003, I used to select whatever rows or columns I needed. How can I do so in 2007? Excel Help does not seem to be so helpful in this respect.
Paul297
excel.newusers
15348 articles. 
2 followers.
1 Replies
289 Views
I have found the solution: Double click the first column header Shift Double click the last column header "Paul" wrote: > In Excel 2007 I am trying to select a number of adjacent columns. As cells in > a number of rows crossing these columns are merged across more cloumns than I > would like to select, Excel is selecting all the columns which are cut across > by the merged cells. This did not happen in Excel 2003, I used to select > whatever rows or columns I needed. > > How can I do so in 2007? Excel Help does not seem to be so helpful in this > respect. > > > > > >
Similar Artilces:
Inserting Pictures into Excel cell
I would like to insert a Picture or a word file into an excel worksheet cell, but keep it hidden behind the cells below. Usually, when I insert a word file, etc. it appears on top in full and covers cells underneath. Is there a way of inserting a file in a cell, keep most of it hidden so that when you click on the cell, the whole thing appears. When you click on another cell, it hides again? Please let me know in simple clear English. Hi AFAIK not really, when you insert pictures into excel they don't actually go into a cell ... the "best" option, might be to insert the...
Lock down certain cells and prompt if used
Hi Group, How do I prompt users that some particular cells are not editable (are locked) if the user tries to overwrite them? Thanks Hi Thrava! If you have left the lock on the cells and if you have protected the sheet, you don't need to. Excel will give the message that you can't alter the cell. I tend to highlight unlocked cells at the time that I remove the lock. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Thrava" <anonymous@di...
Color cells x5
I would like to have when I put the number 1 in the cell for it to tur red in the cell for it to turn blue in the cell for it to turn green in the cell for it to turn yellow in the cell for it to turn orange please help me out if a micro is needed please make on for me so i can copie it and past thanks Jo -- jladik ----------------------------------------------------------------------- jladika's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=910 View this thread: http://www.excelforum.com/showthread.php?threadid=47981 Private Sub Worksheet_Change(By...
cell unprotected in excel 2003 but protected in excel 97 and 2000?
My boss sent out an excel sheet that had one unprotected column for people to enter data, the rest was write-protected so no one can change his formulas. He has 2003, as do I. He sent a copy to other users, some of whom have excel 2000, others excel 97. When they try to enter the amounts in the un-protected column it tells them that cell is protected. HOWEVER - he sends the same file to me, and I can enter data no problem. As far as I knew there were no compatability problems between excel 2003 and previous versions, but then why can I enter data in the unprotected cells but users with prev...
Increment a number based on four adjoing cells
I am using an xlsx spreadsheet for indexing metadata for file folders. Based on the metadata selected from dropdowns in columns H, I & J, the spreadsheet builds a file number in the format AA-AA-NN-nnnnn (A=alpha; N=predetermined number; nnnnn= sequential number). I'm using the following formulas: Col B =VLOOKUP(H577,FUNCTIONLU,2) Col C =VLOOKUP(I577,CATEGORYLU,2) Col D =VLOOKUP(J577,TYPELU,2) Col E =IF(D576="","",IF(COUNTIF($D$2:$D576,$D576)=1,TEXT(0,"00001")+0,IF(COUNTIF($D$2:$D576,D576)>1,INDEX($E$2:$E576,MATCH($D576,$D$2:$...
How to stop toolbar drop box of entire cell contents
I keep getting a drop box in the toolbar that shows my entire cell contents and covers 1/4 of my screen. Would like it not to do that. What should I do? That is the formula bar you see expanding. Don't select any cells with great amounts of text or just don't display the formula bar. View>Formula Bar...........uncheck it. That's one thing I like about Excel 2007. The option to expand or not. Gord Dibben MS Excel MVP On Fri, 30 Apr 2010 08:27:04 -0700, cubanmamma <cubanmamma@discussions.microsoft.com> wrote: >I keep getting a drop box in t...
Get previous selection in List Control
Hi All, I have a list control (CListCtrl containing port names) in my dialog box. I want to select a port in the list and corresponding configuration in the dialog box. When the selection is changed (i.e. user selects another port in the list), the configuration for previous port is strored in a data structure (or file). But when I change the selection (NM_CLICK event) the current selection index is changed to new selection (index of newly selected port), and I can not save the configuration for previously selected port. Is there any way to get the previous selection in the list control? Pl...
need to add/remove characters over thousands of cells
I have thousands of payroll records that are imported into excel on a daily basis. The cost codes appear like this: 7002/1045.824240305 The 7002/ needs to be removed and a . (dot) inserted five from the end like this: 1045.8242.40305 Please note there are hundreds of different cost codes so I cannot simply copy and paste but they must all be coverted so the format is the same. I am desperate for suggestions. cheers, Nadia Try this formula: =MID(A1,FIND("/",A1,1)+1,LEN(A1)) Then use a custom format to get the dot in there. ******************* ~Anne Troy www.OfficeArticles.com www....
How do I select an Option button using code?
I have an option group with two option button. I am trying to select one of them using code. I tried: Me.OptProject.Enabled=True and Me.OptProject.SetFocus Neither worked. Any ideas? Thanks Ayo Set the Value of the option group to the OptionValue of the option button. This kind of thing: Me.Frame0 = Me.OptProject.OptionValue -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ayo" <Ayo@discussions.microsoft.com> wrote in message news:263D3BA4-7...
Sorting not allowed when cells are protected
I want to protect cell formulas from being deleted yet allow others to sort the results. If I protect the cells' contents, sorting is greyed out & not available. How can I protect cells yet allow rows of cells to be sorted by others? Specifically, I have peoples names in column A, Total correct in column B, answers to questions in columns C thru H. The correct answers are at the bottom of columns C thru H. A countif formula is in each row of column B (total correct). Sorting the results is only available if I unprotect the worksheet thus allowing the formulas in column B to be...
Selective auto text inputting
I am building a workbook to track vendor transactions. I have several vendors that are "regulars" and want to track their transactions separately, but still record them on the "master" sheet. My workbook has the master sheet and a worsheet for each of the major vendors. I put a column for each of the major vendors The idea being if the next transaction is from Vendor A, I can put an x in their column and the data from that transaction will be copied to Vendor A's separate worksheet in the next available line. If none of the "designator" colums have an x t...
How can I type within a cell like it is a word document?
Hi, Is there a way to type text within a cell where I can choose the position of the text by clicking within the cell like you can within a word document? I'd like to be able to easily type in something like this: YOU: ME: No I can not do that! (happy) Yes you can! And be able to select the placement of my text WITHOUT having to hold down the cursor to push it forward. Thanks! N...
Select last data in column for a graph
Hello, Is there an excel function that I can use to select the last cell that contains data to display in a chart? Do I have to write a macro for this? Thanks Dan No macro, just formulas in dynamic defined names: http://peltiertech.com/WordPress/2008/05/14/dynamic-charts/ - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "ASTDan" <ASTDan@discussions.microsoft.com> wrote in message news:2ABB9526-574C-4589-B6D4-6A9EDBB75C9D@microsoft.com... > Hello, > > Is there an excel function that I ca...
text in a cell
i have a word in excel that i would like to populate to different cells what is the formula to copy a word into another cell One way: A1: word A2: =A1 In article <8217C379-40A3-4FA5-A1EF-1C6BA93AF6EF@microsoft.com>, Debbie <Debbie@discussions.microsoft.com> wrote: > i have a word in excel that i would like to populate to different cells what > is the formula to copy a word into another cell ...
auto-hyperlinking worksheet but also showing a cell value beside it
i got it to automatically hyperlink 100's of worksheets. thanks to the book 100 excel hacks. worth every penny. but now i'm trying to auto-hyperlink a cell value in each woksheet. for example worksheet hyperlink will be on column A and a cell value for each worksheet hyperlink in column B. thanks in advance. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** You're gonna have to explain that one slowly, with examples and in English. g "excel ent" <twistedseoul@ya...
Problem creating form that selects tables and fields
Hello I’ve been working on this problem for a long time and, so far, each solution runs into a roadblock that stops everything. I would appreciate any suggestions. I’m trying to build a form that allows the user to pick a table (in this mdb file) , and within that table, select several fields. It could look something like those Microsoft wizards where there is a list box on the left, and the user uses pointer controls identified with “>” and “<” to move variables from the box on the left to the box on the right. It could also be something very different. In my latest effo...
how to get the address of the ceected cell
Hi Everybody How can I get the address of a cell whenever I click on it thanks Hi yosi, Look at the top left of your screen, to the left of the formula bar. It's called the name box. HTH Martin Here's how in code............ Sub SelectedCellAddress() MsgBox ActiveCell.address End Sub Vaya con Dios, Chuck, CABGx3 "yosi_lb" <yosi_lb@bezeqint.net> wrote in message news:45bb36fe$1@news.bezeqint.net... > Hi Everybody > > How can I get the address of a cell whenever I click on it > > thanks > > 'This is worksheet event code, which m...
Formatting a cell for currency
What's the VB code for formatting a cell to show currency (with dolla sign and 2 decimals)? Worksheets("Sheet1").Cells(1,1).?????????=True What's the syntax? thank -- k48 ----------------------------------------------------------------------- k483's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1079 View this thread: http://www.excelforum.com/showthread.php?threadid=27333 Hi one easy way: Set your format manually while recording a macro :-) -- Regards Frank Kabel Frankfurt, Germany "k483" <k483.1eulpz@excelforum-nospam.com&g...
How to calculate % in summary sheet from cell A9 in multiple sheet 01-23-10
Dear experts, I have data on patients in excel file, with each patient's data placed on a separate sheet. For instance: PatientA has data on worksheet "PatientA" (Sheet2) where cell A9 is hemoglobin value.. PatientZ has data on worksheet "PatientZ" (Sheet50) where cell A9 is hemoglobin value. I have a summary worksheet called "Summary" (Sheet1). I need cell A9 in the "Summary" (Sheet1) sheet to give me the percentage of patients whose Hemoglobin value is between 10 and 12 (that is percentage of A9 values in Sheet1 to Sheet50 with value ...
Opening a URL in a cell without a mouse click
I am trying to automate a task with a macro recorder where I open a hyperlink in a cell in a spreadsheet, save the displayed page then move to the next cell and repeat. The problem I am experiencing is when I use the mouse to click the hyperlink, the recorder always goes back to the first cell I clicked when recording the session. I need to use keystrokes to open the hyperlink. I have not found a solution as of yet on how to open the web page with anything other than a mouse. Any suggestions would be most welcome. Terry -- trountree --------------------------------------------------...
Changing cell from formula to data.
Is there a way to change a cell from a formula (that is returning data) to the data it is returning. I realy hop it it becuse it would help out a ton. -- Thanks TimM If you select the cell and copy then edit - pastes special - values on the cell you will have the value and not the formula. HTH -- Sincerely, Michael Colvin "TimM" wrote: > Is there a way to change a cell from a formula (that is returning data) to > the data it is returning. I realy hop it it becuse it would help out a ton. > > > -- > Thanks > TimM Hi, Select the cell(s) contain...
Avoiding Cells
Hi all, I've got a report spreadsheet, that shows the status of around 30 projects as either "Red", "Amber" or "Green". In another worksheet of the report I want to display all the projects that are displayed as "Red". Now, I know that I could have a column that looks at a specific cell with the project status in and displays it if is says "Red": =IF(A2="Red",A2,"") However, this would also mean that where the projects aren't "Red", there would be gaps in the table. Is there a formula I can use that ...
Reference Cells with Sumif or Countif
I am trying in vain to use sumif and countif formulas with reference cells in the criteria. For example, in the formula =SUMIF(D5:DS5,"<A10",D8:DS8), I have to plug in the acutal value of A10 to get the calculation to work (=SUMIF(D5:DS5,"<9180",D8:DS8). I would like to use the reference cell (A10) all the time, instead of having to change the formula manually every time. Not sure where I am going wrong. Thank you, Gene SORRY - NEW TO THIS BOARD - ONCE I POST THIS QUESTION, I SAW SIMILAR QUESTIONS ALREADY ANSWERED. PLEASE DISREGARD THIS POST. THANK YOU &qu...
How do I change case from lower to proper in a range of cells?
I need to change case from Lower to UPPER for a range of cells. Any pointers on this will be appreciated. Ajayjeet, you could use the upper functon like this, with data in column A in B1 put =UPPER(A1) and copy down, then copy and paste special over your data, or with a macro, select your data then run this code Sub Make_CAPS() Dim cel As Range For Each cel In Intersect(Selection, _ ActiveSheet.UsedRange) cel.Formula = UCase$(cel.Formula) Next End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedb...
How to extract just the Column Name of the active cell
Hello, Does anyone know how to extract only the Column Name of an active cell in another cell. e.g. If active cell is D4 then I want the "D" to be extract and displayed in another cell say A1. Let me know. Regards, Fred Debug.Print ColumnLetter(activecell.Column) '----------------------------------------------------------------- Function ColumnLetter(Col As Long) '----------------------------------------------------------------- Dim sColumn As String On Error Resume Next sColumn = Split(Columns(Col).Address(, False), ":")(1) On Error GoTo 0...
I would like to insert a Picture or a word file into an excel worksheet cell, but keep it hidden behind the cells below. Usually, when I insert a word file, etc. it appears on top in full and covers cells underneath. Is there a way of inserting a file in a cell, keep most of it hidden so that when you click on the cell, the whole thing appears. When you click on another cell, it hides again? Please let me know in simple clear English. Hi AFAIK not really, when you insert pictures into excel they don't actually go into a cell ... the "best" option, might be to insert the...
Lock down certain cells and prompt if used
Hi Group, How do I prompt users that some particular cells are not editable (are locked) if the user tries to overwrite them? Thanks Hi Thrava! If you have left the lock on the cells and if you have protected the sheet, you don't need to. Excel will give the message that you can't alter the cell. I tend to highlight unlocked cells at the time that I remove the lock. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Thrava" <anonymous@di...
Color cells x5
I would like to have when I put the number 1 in the cell for it to tur red in the cell for it to turn blue in the cell for it to turn green in the cell for it to turn yellow in the cell for it to turn orange please help me out if a micro is needed please make on for me so i can copie it and past thanks Jo -- jladik ----------------------------------------------------------------------- jladika's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=910 View this thread: http://www.excelforum.com/showthread.php?threadid=47981 Private Sub Worksheet_Change(By...
cell unprotected in excel 2003 but protected in excel 97 and 2000?
My boss sent out an excel sheet that had one unprotected column for people to enter data, the rest was write-protected so no one can change his formulas. He has 2003, as do I. He sent a copy to other users, some of whom have excel 2000, others excel 97. When they try to enter the amounts in the un-protected column it tells them that cell is protected. HOWEVER - he sends the same file to me, and I can enter data no problem. As far as I knew there were no compatability problems between excel 2003 and previous versions, but then why can I enter data in the unprotected cells but users with prev...
Increment a number based on four adjoing cells
I am using an xlsx spreadsheet for indexing metadata for file folders. Based on the metadata selected from dropdowns in columns H, I & J, the spreadsheet builds a file number in the format AA-AA-NN-nnnnn (A=alpha; N=predetermined number; nnnnn= sequential number). I'm using the following formulas: Col B =VLOOKUP(H577,FUNCTIONLU,2) Col C =VLOOKUP(I577,CATEGORYLU,2) Col D =VLOOKUP(J577,TYPELU,2) Col E =IF(D576="","",IF(COUNTIF($D$2:$D576,$D576)=1,TEXT(0,"00001")+0,IF(COUNTIF($D$2:$D576,D576)>1,INDEX($E$2:$E576,MATCH($D576,$D$2:$...
How to stop toolbar drop box of entire cell contents
I keep getting a drop box in the toolbar that shows my entire cell contents and covers 1/4 of my screen. Would like it not to do that. What should I do? That is the formula bar you see expanding. Don't select any cells with great amounts of text or just don't display the formula bar. View>Formula Bar...........uncheck it. That's one thing I like about Excel 2007. The option to expand or not. Gord Dibben MS Excel MVP On Fri, 30 Apr 2010 08:27:04 -0700, cubanmamma <cubanmamma@discussions.microsoft.com> wrote: >I keep getting a drop box in t...
Get previous selection in List Control
Hi All, I have a list control (CListCtrl containing port names) in my dialog box. I want to select a port in the list and corresponding configuration in the dialog box. When the selection is changed (i.e. user selects another port in the list), the configuration for previous port is strored in a data structure (or file). But when I change the selection (NM_CLICK event) the current selection index is changed to new selection (index of newly selected port), and I can not save the configuration for previously selected port. Is there any way to get the previous selection in the list control? Pl...
need to add/remove characters over thousands of cells
I have thousands of payroll records that are imported into excel on a daily basis. The cost codes appear like this: 7002/1045.824240305 The 7002/ needs to be removed and a . (dot) inserted five from the end like this: 1045.8242.40305 Please note there are hundreds of different cost codes so I cannot simply copy and paste but they must all be coverted so the format is the same. I am desperate for suggestions. cheers, Nadia Try this formula: =MID(A1,FIND("/",A1,1)+1,LEN(A1)) Then use a custom format to get the dot in there. ******************* ~Anne Troy www.OfficeArticles.com www....
How do I select an Option button using code?
I have an option group with two option button. I am trying to select one of them using code. I tried: Me.OptProject.Enabled=True and Me.OptProject.SetFocus Neither worked. Any ideas? Thanks Ayo Set the Value of the option group to the OptionValue of the option button. This kind of thing: Me.Frame0 = Me.OptProject.OptionValue -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ayo" <Ayo@discussions.microsoft.com> wrote in message news:263D3BA4-7...
Sorting not allowed when cells are protected
I want to protect cell formulas from being deleted yet allow others to sort the results. If I protect the cells' contents, sorting is greyed out & not available. How can I protect cells yet allow rows of cells to be sorted by others? Specifically, I have peoples names in column A, Total correct in column B, answers to questions in columns C thru H. The correct answers are at the bottom of columns C thru H. A countif formula is in each row of column B (total correct). Sorting the results is only available if I unprotect the worksheet thus allowing the formulas in column B to be...
Selective auto text inputting
I am building a workbook to track vendor transactions. I have several vendors that are "regulars" and want to track their transactions separately, but still record them on the "master" sheet. My workbook has the master sheet and a worsheet for each of the major vendors. I put a column for each of the major vendors The idea being if the next transaction is from Vendor A, I can put an x in their column and the data from that transaction will be copied to Vendor A's separate worksheet in the next available line. If none of the "designator" colums have an x t...
How can I type within a cell like it is a word document?
Hi, Is there a way to type text within a cell where I can choose the position of the text by clicking within the cell like you can within a word document? I'd like to be able to easily type in something like this: YOU: ME: No I can not do that! (happy) Yes you can! And be able to select the placement of my text WITHOUT having to hold down the cursor to push it forward. Thanks! N...
Select last data in column for a graph
Hello, Is there an excel function that I can use to select the last cell that contains data to display in a chart? Do I have to write a macro for this? Thanks Dan No macro, just formulas in dynamic defined names: http://peltiertech.com/WordPress/2008/05/14/dynamic-charts/ - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "ASTDan" <ASTDan@discussions.microsoft.com> wrote in message news:2ABB9526-574C-4589-B6D4-6A9EDBB75C9D@microsoft.com... > Hello, > > Is there an excel function that I ca...
text in a cell
i have a word in excel that i would like to populate to different cells what is the formula to copy a word into another cell One way: A1: word A2: =A1 In article <8217C379-40A3-4FA5-A1EF-1C6BA93AF6EF@microsoft.com>, Debbie <Debbie@discussions.microsoft.com> wrote: > i have a word in excel that i would like to populate to different cells what > is the formula to copy a word into another cell ...
auto-hyperlinking worksheet but also showing a cell value beside it
i got it to automatically hyperlink 100's of worksheets. thanks to the book 100 excel hacks. worth every penny. but now i'm trying to auto-hyperlink a cell value in each woksheet. for example worksheet hyperlink will be on column A and a cell value for each worksheet hyperlink in column B. thanks in advance. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** You're gonna have to explain that one slowly, with examples and in English. g "excel ent" <twistedseoul@ya...
Problem creating form that selects tables and fields
Hello I’ve been working on this problem for a long time and, so far, each solution runs into a roadblock that stops everything. I would appreciate any suggestions. I’m trying to build a form that allows the user to pick a table (in this mdb file) , and within that table, select several fields. It could look something like those Microsoft wizards where there is a list box on the left, and the user uses pointer controls identified with “>” and “<” to move variables from the box on the left to the box on the right. It could also be something very different. In my latest effo...
how to get the address of the ceected cell
Hi Everybody How can I get the address of a cell whenever I click on it thanks Hi yosi, Look at the top left of your screen, to the left of the formula bar. It's called the name box. HTH Martin Here's how in code............ Sub SelectedCellAddress() MsgBox ActiveCell.address End Sub Vaya con Dios, Chuck, CABGx3 "yosi_lb" <yosi_lb@bezeqint.net> wrote in message news:45bb36fe$1@news.bezeqint.net... > Hi Everybody > > How can I get the address of a cell whenever I click on it > > thanks > > 'This is worksheet event code, which m...
Formatting a cell for currency
What's the VB code for formatting a cell to show currency (with dolla sign and 2 decimals)? Worksheets("Sheet1").Cells(1,1).?????????=True What's the syntax? thank -- k48 ----------------------------------------------------------------------- k483's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1079 View this thread: http://www.excelforum.com/showthread.php?threadid=27333 Hi one easy way: Set your format manually while recording a macro :-) -- Regards Frank Kabel Frankfurt, Germany "k483" <k483.1eulpz@excelforum-nospam.com&g...
How to calculate % in summary sheet from cell A9 in multiple sheet 01-23-10
Dear experts, I have data on patients in excel file, with each patient's data placed on a separate sheet. For instance: PatientA has data on worksheet "PatientA" (Sheet2) where cell A9 is hemoglobin value.. PatientZ has data on worksheet "PatientZ" (Sheet50) where cell A9 is hemoglobin value. I have a summary worksheet called "Summary" (Sheet1). I need cell A9 in the "Summary" (Sheet1) sheet to give me the percentage of patients whose Hemoglobin value is between 10 and 12 (that is percentage of A9 values in Sheet1 to Sheet50 with value ...
Opening a URL in a cell without a mouse click
I am trying to automate a task with a macro recorder where I open a hyperlink in a cell in a spreadsheet, save the displayed page then move to the next cell and repeat. The problem I am experiencing is when I use the mouse to click the hyperlink, the recorder always goes back to the first cell I clicked when recording the session. I need to use keystrokes to open the hyperlink. I have not found a solution as of yet on how to open the web page with anything other than a mouse. Any suggestions would be most welcome. Terry -- trountree --------------------------------------------------...
Changing cell from formula to data.
Is there a way to change a cell from a formula (that is returning data) to the data it is returning. I realy hop it it becuse it would help out a ton. -- Thanks TimM If you select the cell and copy then edit - pastes special - values on the cell you will have the value and not the formula. HTH -- Sincerely, Michael Colvin "TimM" wrote: > Is there a way to change a cell from a formula (that is returning data) to > the data it is returning. I realy hop it it becuse it would help out a ton. > > > -- > Thanks > TimM Hi, Select the cell(s) contain...
Avoiding Cells
Hi all, I've got a report spreadsheet, that shows the status of around 30 projects as either "Red", "Amber" or "Green". In another worksheet of the report I want to display all the projects that are displayed as "Red". Now, I know that I could have a column that looks at a specific cell with the project status in and displays it if is says "Red": =IF(A2="Red",A2,"") However, this would also mean that where the projects aren't "Red", there would be gaps in the table. Is there a formula I can use that ...
Reference Cells with Sumif or Countif
I am trying in vain to use sumif and countif formulas with reference cells in the criteria. For example, in the formula =SUMIF(D5:DS5,"<A10",D8:DS8), I have to plug in the acutal value of A10 to get the calculation to work (=SUMIF(D5:DS5,"<9180",D8:DS8). I would like to use the reference cell (A10) all the time, instead of having to change the formula manually every time. Not sure where I am going wrong. Thank you, Gene SORRY - NEW TO THIS BOARD - ONCE I POST THIS QUESTION, I SAW SIMILAR QUESTIONS ALREADY ANSWERED. PLEASE DISREGARD THIS POST. THANK YOU &qu...
How do I change case from lower to proper in a range of cells?
I need to change case from Lower to UPPER for a range of cells. Any pointers on this will be appreciated. Ajayjeet, you could use the upper functon like this, with data in column A in B1 put =UPPER(A1) and copy down, then copy and paste special over your data, or with a macro, select your data then run this code Sub Make_CAPS() Dim cel As Range For Each cel In Intersect(Selection, _ ActiveSheet.UsedRange) cel.Formula = UCase$(cel.Formula) Next End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedb...
How to extract just the Column Name of the active cell
Hello, Does anyone know how to extract only the Column Name of an active cell in another cell. e.g. If active cell is D4 then I want the "D" to be extract and displayed in another cell say A1. Let me know. Regards, Fred Debug.Print ColumnLetter(activecell.Column) '----------------------------------------------------------------- Function ColumnLetter(Col As Long) '----------------------------------------------------------------- Dim sColumn As String On Error Resume Next sColumn = Split(Columns(Col).Address(, False), ":")(1) On Error GoTo 0...