result given from content of selected cells.
is there any way to give a result in one cell from the content of other selected cells. scenario: i am trying to create a spreadsheet to work out my current grades for college. what i am wanting to do is create a function that takes the value from a selection of cells (ex. B1:B7) and give a result if the content match a given criteria. i am looking for something along along the lines of; cells B1:B7 = x then show result value 1. cells B1:B11 = x then show result value 2. cells B1:B13 = x then show result 3. (best i can say it as) my initial through was the IF statement but that failed to give the results. much appreciated if guidance can be given.
|
0 |
|
Utf
4/29/2010 1:46:01 PM
excel.worksheet.functions
4936 articles. 
2 followers.
6 Replies
753 Views
Hi, I'm not sure I fully understand gut how about this =COUNTIF(B1:B7,"XXX") Where XXX is the string your looking for. Or if your looking for numbers try =COUNTIF(B1:B7,1) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Rik_A" wrote: > is there any way to give a result in one cell from the content of other > selected cells. > > scenario: > i am trying to create a spreadsheet to work out my current grades for > college. what i am wanting to do is create a function that takes the value > from a selection of cells (ex. B1:B7) and give a result if the content match > a given criteria. > i am looking for something along along the lines of; > > cells B1:B7 = x then show result value 1. cells B1:B11 = x then show result > value 2. cells B1:B13 = x then show result 3. (best i can say it as) > > my initial through was the IF statement but that failed to give the results. > > much appreciated if guidance can be given.
|
|
0 |
|
Utf
4/29/2010 2:02:01 PM
Taking your requirements literally, something like: =(COUNTIF(B1:B7,"x")=ROWS(B1:B7))+(COUNTIF(B8:B11,"x")=ROWS(B8:B11))+(COUNTIF(B12:B13,"x")=ROWS(B12:B13)) should do the trick. "Rik_A" <Rik_A@discussions.microsoft.com> wrote in message news:6C5EC104-EAD4-4EF4-9EEF-ED11EA6830BD@microsoft.com... > is there any way to give a result in one cell from the content of other > selected cells. > > scenario: > i am trying to create a spreadsheet to work out my current grades for > college. what i am wanting to do is create a function that takes the value > from a selection of cells (ex. B1:B7) and give a result if the content > match > a given criteria. > i am looking for something along along the lines of; > > cells B1:B7 = x then show result value 1. cells B1:B11 = x then show > result > value 2. cells B1:B13 = x then show result 3. (best i can say it as) > > my initial through was the IF statement but that failed to give the > results. > > much appreciated if guidance can be given.
|
|
0 |
|
Steve
4/29/2010 2:11:39 PM
the COUNTIF function only adds up the cells contents and give a number result on the number of the matching criteria. (B1:B7 with the contents of x, the function =COUNTIF(B1:B7,"x") give the result of "7") what i am looking for is, should cell B1, B2, B3, B4, B5, B6 and B7 contents equal "x" the show result "Pass" (the cells B1:B7 being the pass criteria for my work) but if 1 of the 7 cells show no content then that result is nullified. *apologies for not creates a clear image" i originally thought of "=IF(B1:B7=x,pass,null)" but realise this wouldn't work. i hope this helps in the understanding. "Mike H" wrote: > Hi, > > I'm not sure I fully understand gut how about this > > =COUNTIF(B1:B7,"XXX") > > Where XXX is the string your looking for. Or if your looking for numbers try > > =COUNTIF(B1:B7,1) > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question.
|
|
0 |
|
Utf
4/29/2010 2:25:01 PM
Do you mean... =IF(COUNTIF(B1:B7,"X"),1,IF(COUNTIF(B1:B11,"X"),2, IF(COUNTIF(B1:B13,"X"),3,""))) -- Jacob (MVP - Excel) "Rik_A" wrote: > is there any way to give a result in one cell from the content of other > selected cells. > > scenario: > i am trying to create a spreadsheet to work out my current grades for > college. what i am wanting to do is create a function that takes the value > from a selection of cells (ex. B1:B7) and give a result if the content match > a given criteria. > i am looking for something along along the lines of; > > cells B1:B7 = x then show result value 1. cells B1:B11 = x then show result > value 2. cells B1:B13 = x then show result 3. (best i can say it as) > > my initial through was the IF statement but that failed to give the results. > > much appreciated if guidance can be given.
|
|
0 |
|
Utf
4/29/2010 2:45:01 PM
Hi, Is this it =IF(COUNTIF(B1:B7,"xxx")=7,"Pass","Maybe fail") -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Rik_A" wrote: > the COUNTIF function only adds up the cells contents and give a number result > on the number of the matching criteria. > (B1:B7 with the contents of x, the function =COUNTIF(B1:B7,"x") give the > result of "7") > what i am looking for is, > should cell B1, B2, B3, B4, B5, B6 and B7 contents equal "x" the show result > "Pass" (the cells B1:B7 being the pass criteria for my work) but if 1 of the > 7 cells show no content then that result is nullified. > *apologies for not creates a clear image" > > i originally thought of "=IF(B1:B7=x,pass,null)" but realise this wouldn't > work. i hope this helps in the understanding. > > "Mike H" wrote: > > > Hi, > > > > I'm not sure I fully understand gut how about this > > > > =COUNTIF(B1:B7,"XXX") > > > > Where XXX is the string your looking for. Or if your looking for numbers try > > > > =COUNTIF(B1:B7,1) > > -- > > Mike > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > introduces the fewest assumptions while still sufficiently answering the > > question.
|
|
0 |
|
Utf
4/29/2010 3:04:05 PM
Mike, might even make it more "generic" and foolproof? ... =IF(COUNTIF(B1:B7,"xxx")=ROWS(B1:B7),"Pass","Maybe fail") Especially if the problem course happens to be Math-101 <g>. By using Rows() and referencing the same range, the count is always correct without having to take off your shoes for really longish range of entries. "Mike H" wrote: > Hi, > > Is this it > > =IF(COUNTIF(B1:B7,"xxx")=7,"Pass","Maybe fail") > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "Rik_A" wrote: > > > the COUNTIF function only adds up the cells contents and give a number result > > on the number of the matching criteria. > > (B1:B7 with the contents of x, the function =COUNTIF(B1:B7,"x") give the > > result of "7") > > what i am looking for is, > > should cell B1, B2, B3, B4, B5, B6 and B7 contents equal "x" the show result > > "Pass" (the cells B1:B7 being the pass criteria for my work) but if 1 of the > > 7 cells show no content then that result is nullified. > > *apologies for not creates a clear image" > > > > i originally thought of "=IF(B1:B7=x,pass,null)" but realise this wouldn't > > work. i hope this helps in the understanding. > > > > "Mike H" wrote: > > > > > Hi, > > > > > > I'm not sure I fully understand gut how about this > > > > > > =COUNTIF(B1:B7,"XXX") > > > > > > Where XXX is the string your looking for. Or if your looking for numbers try > > > > > > =COUNTIF(B1:B7,1) > > > -- > > > Mike > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > introduces the fewest assumptions while still sufficiently answering the > > > question.
|
|
0 |
|
Utf
4/29/2010 6:52:01 PM
Similar Artilces:
Matching cells and files
Is it possible? I have a folder, which contains files named "1", "2" & "3" and then I have cells including names of those files, ie "1", "2" & "3". Is there a wonderful formula that can fast and easily match and hyperlink the cell with the appropriate file in the folder? Check your other post. suddengunfire wrote: > > Is it possible? > > I have a folder, which contains files named "1", "2" & "3" > > and then I have cells including names of those files, ie "1"...
show name when a cell has specific word
Hi How do we add into VBA a code to insert a name into a cell when another cell has a specific word selected from a validation list. Any help would be appreciated. Thanks Noemi Would a formula such as =IF(B1="Fred","Barney","") work for you? (assuming your cell to be checked to be B1) "Noemi" wrote: > Hi > How do we add into VBA a code to insert a name into a cell when another cell > has a specific word selected from a validation list. > > Any help would be appreciated. > > Thanks Noemi ...
Can't find cells to change content?
I have been given an Excel document I'd like to use as template. However, there is one item of text that I cannot change. In fact I can't seem to find which cell it is entered in. It appears as an overlay over the cells and does not relate to them. I can click on any other item and see to which cell it relates. Inserted Images highlight, and can be moved and placed anywhere. This text is not an image either as it does not give the image handles. If I select the whole document and delete the contents the text goes away. Any suggestions? NS <calypson@bell_kill_this_south.net&...
Writing many lines on a same Excel 2000 cell
Hello dear members of the microsoft.public.excel.misc newsgroup. I would need Your kindly help again on this one please. I would like to write some sentences on a single Excel 2000 cell. Those sentences should be in differents "lines" of the same cell. Exemple An Excel 2000 cell ------------------------------------- |Yesterday was Saturday | |Today is Sunday | |Tomorrow Monday | |--------------------------------------| Every time I hit on the "Enter" key the next line come down in the following column cell. How coul...
blank cells v. zeros
Hi- I have a database full of different tables on each worksheet. On eac table, I input test scores and then run my macro. The test scores go t the last worksheet labeled 'data'. My problem is when I leave a tes blank on the table (when there is no score for that specific test), zero appears under that test in 'data'. I tried turning off zero value but that got rid of every zero, even the ones that actually represent test score. To make a long story short, I need blank cells to sta blank and cells with zeros to stay zero. If anyone can help me, i would be much appreciated! -...
Conditional Formatting of empty cells?
i m makin a form with alot of stuff goin on and i want to make it ver easy for ppl to fill it in... ssssoooo.... is there a way to apply conditional formatting to empty/blank/nul cells i m looking ideally for something that will highlight the cell wit like a yellow background color so that it stands out but it has to g back to no fill wen they enter the info if anyone has any solutions or advice on this that would rock -thanks : ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelFo...
View Null Results in Query
i have an access 2003 query SELECT [AlarmData Table Filtered By 60 Min Trip].[Event Type], [AlarmData Table Filtered By 60 Min Trip].State, DateSerial(Year([Date/Time*]),Month([Date/Time*]),Day([Date/Time*])) AS [Date], TimeSerial(Hour([Date/Time*]),Minute([Date/Time*]),0) AS TimePeriod, Count(*) AS Total FROM [AlarmData Table Filtered By 60 Min Trip] GROUP BY [AlarmData Table Filtered By 60 Min Trip].[Event Type], [AlarmData Table Filtered By 60 Min Trip].State, DateSerial(Year([Date/Time*]),Month([Date/Time*]),Day([Date/Time*])), TimeSerial(Hour([Date/Time*]),Minute([Date/...
Setting the default properties for cells in all new spreadsheets
How can I set the default properties of spreadsheet cells (e.g.- always vertically centred, word wrap, Aerial 10point) so that the cells in all new spreadsheets are created with these attributes? The basic answer is to save the workbook as Sheet.xlt and Book.xlt (or Sheet.xltx and Book.xltx in Excel 2007) in the XLSTART folder. More details at http://office.microsoft.com/en-us/excel/HA010548151033.aspx This is for Excel2000 but the ideas are the same fro all versions best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Bon Bon" <Bo...
Excel 2000, cannot edit macro, nothing happens when I select "edit"
I have a worksheet built by someone else with many macros built in and they all are operational - no error messages. I want to edit one macro in the worksheet. I follow the steps: Tools, Macro, Macros (here I get the list of macros in the spreadsheet). I select the name of the one in the list that I want to edit then select "Edit" and nothing happens. The macro window closes and I'm back in the worksheet. Microsoft Visual Basic does not open and there is no error message. As a test, I recorded my own macro in the worksheet then followed the same steps above but selected my ...
Problem Applying Formula to cell
Hi I have a problem a with macro I have recorded/written. The macro applies a formula to a range of cells which references values in a column in a separate worksheet and returns cell contents from this worksheet then deletes blank rows. The macro runs as expected when the formula has been entered manually in the starting cell but as soon as i try to apply the formula as part of the macro (commented out second line) it returns error code 1004. Range("A3").Select ' ActiveCell.FormulaR1C1 = "=IF('Raw data'!$I1-25 =TODAY(),'Raw data&#...
combo box selection 11-17-09
I have a combo box (cbo2) and a list box (lbo1) that are filtered depending on the selection in a third combo box (cbo1). The boxes are filtering correctly but there is a problem and a question: Problem - cbo2 does not allow me to make a selection even though I can see the options. Question - I want the user to be able to select either something in cbo2 or lbo1. How do I accomplish this? thank you, -- javablood For your cbo2 problem, is the bound field enabled and not locked? Is the field it is bound to updateable (e.g. not a 'one' field on a one-to-many j...
easy deletion of table contents
i've been searching for an easy way to delete most of the content of my table but preserve the structure. you know other than highlighting each cell and then the delete key?? i would like to preserve the first two columns, i'll be using the same info this time around, but the 4 other columns will be new information. i thought there was a 'delete' content choice, but that may be some other software in another cubicle hell i've lived! anyway, if i have to delete all the content, so be it. that i think i can do very easily and still keep the structure and links. okay, thanks...
Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...
why cant i format my cells date with english canada
when i try to "format cells" date, choosing English canada, the format will not apply, it changes to chinese or cyrllic. I see no such choice of language within Format, cells, Date with my US version of Excel 2000, no can I imagine such an option.. What are your date formats (short and long) in you Region Options (in control settings).? What language are you setup for in your control settings. If you take the cell that has been formatted what do you see if you look at formatting with Format, cells, customi -- --- HTH, David McRitchie, Microsoft MVP - Excel [site ch...
How do I Multiply different cells by one number as a formula?
I have to two clients and need to show that I'm multiplying their total charge by half. I am using a template for the first time and need to know how to do that so my clients can see the formula? Help I haven't done this since college and i'm a little rusty. If this is what you would like to show, you could just put the charge in the Column B, the phrase 'x 0.5 =' in the Column C and the formula =B1*0.5 in Column D. A B C D Client 1 $100.00 x 0.5 = $50.00 Client 2 $100.00 x 0.5 = $50.00 Hope this helps! Mark "secar...
Mark cell when changed
Hello, I have an Excel sheet which is filled with formulas linked to a data warehouse. To be able to easily detect changes in the the past I was wondering if you can format a cell so that it (for example) turns red as soon as the value in it changes due to an update of the sheet. Hope that someone can advice me. Thanx very much in advance. Regards, Robert Robert, A worksheet_change event macro could mark the cells with red, but since you're sheet is links to the original data, that won't fire the macro. You need to think about when you'll reset the colors. Perhaps a bette...
Putting data into cells with formulas
Is it possible to put data into a cell that already has a formula? I want to know the % that A2 is from A1, however I want to put the number 23 in A2 manually and then have A2 have the answer which is .92. Impossible? A1-2 A2-2 Hi not possible with formulas. This would require VBA / using an event procedure >-----Original Message----- >Is it possible to put data into a cell that already has a formula? I want to know the % that A2 is from A1, however I want to put the number 23 in A2 manually and then have A2 have the answer which is .92. Impossible? > >A1-25 >A2-23 ...
Is there a forumula that uses colors instead of cell #'s?
I am creating a worksheet where different items are highlighted several different colors. Is there a way to create a formula that changes when I change the highlighted color of the row instead of having to remove a cell or cells from one formula and add it to another? Thanks for any help!! No, as a colour change does not force a sheet recalculation. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "JohnAO" <JohnAO@discussions.microsoft.com> wrote in message news:A5666EF9-D664-44C9-826D-60C9BDF039AC@microsoft.com... > I am creating a works...
fastest way to combine amounts from two diff cells
I've just inherited a spreadsheet which has three columns o information: Name, Employee ID and Amount. Since the employees have two pay periods there are duplicates. I'v been asked to filter the duplicate names and ID, but the problem is t total the two pay periods into one sum. There are 2000+ ppl and I' prefer not to retype and recalculate. I can Advance filter Name and ID for duplicates but it's this sum tota for pay periods that's got me. For example A1 and A2 are the same name. B1 and B2 are the same ID, bu C1 and C2 are two different amounts. Any suggestions on ...
Couting rows based on given data range
Hi All, I have a sheet which contains supplier details for whom invoices are still open and are ageing: Eg. Supplier Valid Invoice Invoice Ageing (Days) ABC Valid 1 ABC Valid 2 ABC Invalid 3 ABC Invalid 5 ABC Valid 6 ABC Valid 16 ABC Valid 19 ABC Valid 20 ABC Valid 7 ABC Valid 10 ABC Valid 11 I ha...
V10 w sp1 PM Build Batch for payables results in missing sproc
Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'TWO.dbo.pmPopulateSelectChecksVendorTemp'. And for sure it is missing. GP Utilities indicate all companies at correct build. How do I recreate SQL stored procedures in V10? Its found from the menus under Microsoft Dynamics GP->Maintenance->SQL Are we going to see you at Convergence in Orlando? We are throwing a big party aboard an ocean going cruise ship on Wednesday. Visit our web site at http://www.AccoladePublications.com for details and to reserve your boarding pass. -- Richard L. Wha...
Count # of characters in cell
Is there a formula for counting the number of characters in a cell Something like =countchar??? E.g. to return the following: 0863855556 - 10 (Characters) 0856524 - (7 Characters) 087666555 - 9 086666666 - 9 etc -- loscherlan ----------------------------------------------------------------------- loscherland's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=670 View this thread: http://www.excelforum.com/showthread.php?threadid=31939 loscherland Wrote: > Is there a formula for counting the number of characters in a cell > Something like =countchar???...
How...get a cell to offer options
How do I get a cell to offer options in Excel 97. For example I want t be able to chose by clicking on the cell between these options: $92.40 $67.30 Thank yo -- Message posted from http://www.ExcelForum.com Hi Ram1000! You can use: Data > Validation > List Type in your options with the separator used in functions (comma or continental Europe is ;) Or probably better, use an IF function if you can determine the criteria for determining the option. Example: =IF(A1<1000,92.4,67.3) In both cases it is better to use cell references than to hard code the amounts. -- Regards ...
selectively deleting cells
I would like to selectively delete alternate rows on an excel worksheet containing a long list of data. i.e. deleting rows/cells A3,A5,A7,A9,A11....A30573 etc. How do i do this quickly without having to use CTRL + select for each specific cell/row? Del, It isn't clear what you mean by "selectively" delete the rows. If you just want to delete every other row, use code like Sub DeleteEveryOtherRow() Dim RowNdx As Long Application.ScreenUpdating = False For RowNdx = 30573 To 3 Step -2 Rows(RowNdx).Delete Next RowNdx Application.ScreenUpdating = True E...
How do i get excel to format a cell for a negative result.
Hi, Can someone please tell me how i can get excel to format the cell when the result is negative. eg 2 - 3 = -1 therefore the cell would turn red. in menu format-cells-number- choose whichever format for negative n;umber Cisco <Cisco@discussions.microsoft.com> wrote in message news:E36E8CE1-5FDC-42EF-9BCC-C8E3A9768EB6@microsoft.com... > Hi, > > Can someone please tell me how i can get excel to format the cell when the > result is negative. > > eg 2 - 3 = -1 therefore the cell would turn red. Hi Cisco Select the range where you want this t apply, click on Fo...
Is it possible? I have a folder, which contains files named "1", "2" & "3" and then I have cells including names of those files, ie "1", "2" & "3". Is there a wonderful formula that can fast and easily match and hyperlink the cell with the appropriate file in the folder? Check your other post. suddengunfire wrote: > > Is it possible? > > I have a folder, which contains files named "1", "2" & "3" > > and then I have cells including names of those files, ie "1"...
show name when a cell has specific word
Hi How do we add into VBA a code to insert a name into a cell when another cell has a specific word selected from a validation list. Any help would be appreciated. Thanks Noemi Would a formula such as =IF(B1="Fred","Barney","") work for you? (assuming your cell to be checked to be B1) "Noemi" wrote: > Hi > How do we add into VBA a code to insert a name into a cell when another cell > has a specific word selected from a validation list. > > Any help would be appreciated. > > Thanks Noemi ...
Can't find cells to change content?
I have been given an Excel document I'd like to use as template. However, there is one item of text that I cannot change. In fact I can't seem to find which cell it is entered in. It appears as an overlay over the cells and does not relate to them. I can click on any other item and see to which cell it relates. Inserted Images highlight, and can be moved and placed anywhere. This text is not an image either as it does not give the image handles. If I select the whole document and delete the contents the text goes away. Any suggestions? NS <calypson@bell_kill_this_south.net&...
Writing many lines on a same Excel 2000 cell
Hello dear members of the microsoft.public.excel.misc newsgroup. I would need Your kindly help again on this one please. I would like to write some sentences on a single Excel 2000 cell. Those sentences should be in differents "lines" of the same cell. Exemple An Excel 2000 cell ------------------------------------- |Yesterday was Saturday | |Today is Sunday | |Tomorrow Monday | |--------------------------------------| Every time I hit on the "Enter" key the next line come down in the following column cell. How coul...
blank cells v. zeros
Hi- I have a database full of different tables on each worksheet. On eac table, I input test scores and then run my macro. The test scores go t the last worksheet labeled 'data'. My problem is when I leave a tes blank on the table (when there is no score for that specific test), zero appears under that test in 'data'. I tried turning off zero value but that got rid of every zero, even the ones that actually represent test score. To make a long story short, I need blank cells to sta blank and cells with zeros to stay zero. If anyone can help me, i would be much appreciated! -...
Conditional Formatting of empty cells?
i m makin a form with alot of stuff goin on and i want to make it ver easy for ppl to fill it in... ssssoooo.... is there a way to apply conditional formatting to empty/blank/nul cells i m looking ideally for something that will highlight the cell wit like a yellow background color so that it stands out but it has to g back to no fill wen they enter the info if anyone has any solutions or advice on this that would rock -thanks : ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelFo...
View Null Results in Query
i have an access 2003 query SELECT [AlarmData Table Filtered By 60 Min Trip].[Event Type], [AlarmData Table Filtered By 60 Min Trip].State, DateSerial(Year([Date/Time*]),Month([Date/Time*]),Day([Date/Time*])) AS [Date], TimeSerial(Hour([Date/Time*]),Minute([Date/Time*]),0) AS TimePeriod, Count(*) AS Total FROM [AlarmData Table Filtered By 60 Min Trip] GROUP BY [AlarmData Table Filtered By 60 Min Trip].[Event Type], [AlarmData Table Filtered By 60 Min Trip].State, DateSerial(Year([Date/Time*]),Month([Date/Time*]),Day([Date/Time*])), TimeSerial(Hour([Date/Time*]),Minute([Date/...
Setting the default properties for cells in all new spreadsheets
How can I set the default properties of spreadsheet cells (e.g.- always vertically centred, word wrap, Aerial 10point) so that the cells in all new spreadsheets are created with these attributes? The basic answer is to save the workbook as Sheet.xlt and Book.xlt (or Sheet.xltx and Book.xltx in Excel 2007) in the XLSTART folder. More details at http://office.microsoft.com/en-us/excel/HA010548151033.aspx This is for Excel2000 but the ideas are the same fro all versions best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Bon Bon" <Bo...
Excel 2000, cannot edit macro, nothing happens when I select "edit"
I have a worksheet built by someone else with many macros built in and they all are operational - no error messages. I want to edit one macro in the worksheet. I follow the steps: Tools, Macro, Macros (here I get the list of macros in the spreadsheet). I select the name of the one in the list that I want to edit then select "Edit" and nothing happens. The macro window closes and I'm back in the worksheet. Microsoft Visual Basic does not open and there is no error message. As a test, I recorded my own macro in the worksheet then followed the same steps above but selected my ...
Problem Applying Formula to cell
Hi I have a problem a with macro I have recorded/written. The macro applies a formula to a range of cells which references values in a column in a separate worksheet and returns cell contents from this worksheet then deletes blank rows. The macro runs as expected when the formula has been entered manually in the starting cell but as soon as i try to apply the formula as part of the macro (commented out second line) it returns error code 1004. Range("A3").Select ' ActiveCell.FormulaR1C1 = "=IF('Raw data'!$I1-25 =TODAY(),'Raw data&#...
combo box selection 11-17-09
I have a combo box (cbo2) and a list box (lbo1) that are filtered depending on the selection in a third combo box (cbo1). The boxes are filtering correctly but there is a problem and a question: Problem - cbo2 does not allow me to make a selection even though I can see the options. Question - I want the user to be able to select either something in cbo2 or lbo1. How do I accomplish this? thank you, -- javablood For your cbo2 problem, is the bound field enabled and not locked? Is the field it is bound to updateable (e.g. not a 'one' field on a one-to-many j...
easy deletion of table contents
i've been searching for an easy way to delete most of the content of my table but preserve the structure. you know other than highlighting each cell and then the delete key?? i would like to preserve the first two columns, i'll be using the same info this time around, but the 4 other columns will be new information. i thought there was a 'delete' content choice, but that may be some other software in another cubicle hell i've lived! anyway, if i have to delete all the content, so be it. that i think i can do very easily and still keep the structure and links. okay, thanks...
Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...
why cant i format my cells date with english canada
when i try to "format cells" date, choosing English canada, the format will not apply, it changes to chinese or cyrllic. I see no such choice of language within Format, cells, Date with my US version of Excel 2000, no can I imagine such an option.. What are your date formats (short and long) in you Region Options (in control settings).? What language are you setup for in your control settings. If you take the cell that has been formatted what do you see if you look at formatting with Format, cells, customi -- --- HTH, David McRitchie, Microsoft MVP - Excel [site ch...
How do I Multiply different cells by one number as a formula?
I have to two clients and need to show that I'm multiplying their total charge by half. I am using a template for the first time and need to know how to do that so my clients can see the formula? Help I haven't done this since college and i'm a little rusty. If this is what you would like to show, you could just put the charge in the Column B, the phrase 'x 0.5 =' in the Column C and the formula =B1*0.5 in Column D. A B C D Client 1 $100.00 x 0.5 = $50.00 Client 2 $100.00 x 0.5 = $50.00 Hope this helps! Mark "secar...
Mark cell when changed
Hello, I have an Excel sheet which is filled with formulas linked to a data warehouse. To be able to easily detect changes in the the past I was wondering if you can format a cell so that it (for example) turns red as soon as the value in it changes due to an update of the sheet. Hope that someone can advice me. Thanx very much in advance. Regards, Robert Robert, A worksheet_change event macro could mark the cells with red, but since you're sheet is links to the original data, that won't fire the macro. You need to think about when you'll reset the colors. Perhaps a bette...
Putting data into cells with formulas
Is it possible to put data into a cell that already has a formula? I want to know the % that A2 is from A1, however I want to put the number 23 in A2 manually and then have A2 have the answer which is .92. Impossible? A1-2 A2-2 Hi not possible with formulas. This would require VBA / using an event procedure >-----Original Message----- >Is it possible to put data into a cell that already has a formula? I want to know the % that A2 is from A1, however I want to put the number 23 in A2 manually and then have A2 have the answer which is .92. Impossible? > >A1-25 >A2-23 ...
Is there a forumula that uses colors instead of cell #'s?
I am creating a worksheet where different items are highlighted several different colors. Is there a way to create a formula that changes when I change the highlighted color of the row instead of having to remove a cell or cells from one formula and add it to another? Thanks for any help!! No, as a colour change does not force a sheet recalculation. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "JohnAO" <JohnAO@discussions.microsoft.com> wrote in message news:A5666EF9-D664-44C9-826D-60C9BDF039AC@microsoft.com... > I am creating a works...
fastest way to combine amounts from two diff cells
I've just inherited a spreadsheet which has three columns o information: Name, Employee ID and Amount. Since the employees have two pay periods there are duplicates. I'v been asked to filter the duplicate names and ID, but the problem is t total the two pay periods into one sum. There are 2000+ ppl and I' prefer not to retype and recalculate. I can Advance filter Name and ID for duplicates but it's this sum tota for pay periods that's got me. For example A1 and A2 are the same name. B1 and B2 are the same ID, bu C1 and C2 are two different amounts. Any suggestions on ...
Couting rows based on given data range
Hi All, I have a sheet which contains supplier details for whom invoices are still open and are ageing: Eg. Supplier Valid Invoice Invoice Ageing (Days) ABC Valid 1 ABC Valid 2 ABC Invalid 3 ABC Invalid 5 ABC Valid 6 ABC Valid 16 ABC Valid 19 ABC Valid 20 ABC Valid 7 ABC Valid 10 ABC Valid 11 I ha...
V10 w sp1 PM Build Batch for payables results in missing sproc
Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'TWO.dbo.pmPopulateSelectChecksVendorTemp'. And for sure it is missing. GP Utilities indicate all companies at correct build. How do I recreate SQL stored procedures in V10? Its found from the menus under Microsoft Dynamics GP->Maintenance->SQL Are we going to see you at Convergence in Orlando? We are throwing a big party aboard an ocean going cruise ship on Wednesday. Visit our web site at http://www.AccoladePublications.com for details and to reserve your boarding pass. -- Richard L. Wha...
Count # of characters in cell
Is there a formula for counting the number of characters in a cell Something like =countchar??? E.g. to return the following: 0863855556 - 10 (Characters) 0856524 - (7 Characters) 087666555 - 9 086666666 - 9 etc -- loscherlan ----------------------------------------------------------------------- loscherland's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=670 View this thread: http://www.excelforum.com/showthread.php?threadid=31939 loscherland Wrote: > Is there a formula for counting the number of characters in a cell > Something like =countchar???...
How...get a cell to offer options
How do I get a cell to offer options in Excel 97. For example I want t be able to chose by clicking on the cell between these options: $92.40 $67.30 Thank yo -- Message posted from http://www.ExcelForum.com Hi Ram1000! You can use: Data > Validation > List Type in your options with the separator used in functions (comma or continental Europe is ;) Or probably better, use an IF function if you can determine the criteria for determining the option. Example: =IF(A1<1000,92.4,67.3) In both cases it is better to use cell references than to hard code the amounts. -- Regards ...
selectively deleting cells
I would like to selectively delete alternate rows on an excel worksheet containing a long list of data. i.e. deleting rows/cells A3,A5,A7,A9,A11....A30573 etc. How do i do this quickly without having to use CTRL + select for each specific cell/row? Del, It isn't clear what you mean by "selectively" delete the rows. If you just want to delete every other row, use code like Sub DeleteEveryOtherRow() Dim RowNdx As Long Application.ScreenUpdating = False For RowNdx = 30573 To 3 Step -2 Rows(RowNdx).Delete Next RowNdx Application.ScreenUpdating = True E...
How do i get excel to format a cell for a negative result.
Hi, Can someone please tell me how i can get excel to format the cell when the result is negative. eg 2 - 3 = -1 therefore the cell would turn red. in menu format-cells-number- choose whichever format for negative n;umber Cisco <Cisco@discussions.microsoft.com> wrote in message news:E36E8CE1-5FDC-42EF-9BCC-C8E3A9768EB6@microsoft.com... > Hi, > > Can someone please tell me how i can get excel to format the cell when the > result is negative. > > eg 2 - 3 = -1 therefore the cell would turn red. Hi Cisco Select the range where you want this t apply, click on Fo...