Tech Problem Aggregator

cell counting in excel

Q: cell counting in excel

I have a few cells i need counting in excel but i need them to perform a certain job. I need to count 2 cells that have data in them to total 1. If there are blanks in the cells i need it not to count them. Below is an example.

Cell L2 Blank Cell M2 blank Cell O2 29 Cell P2 25.

I need this total to total 1 for the 2 full cells of O2 and P2.

Then after that any 2 cells together ie R2 and S2 adds 1 to the total providing that both cells are full.

to explain beter I'm doing a darts spredsheet keeping totals so i need 2 full cells to total a game win of 1. Then every 2 cells full after will be game win 2,3,4 etc.

I hope someone can help.

Many thanks

A: cell counting in excel

It's better if you post a sample spreadsheet, showing where the totals (1s) need to go.
If you click on "Go Advanced" below the Quick Reply window you can upload a file.

1 more replies
Answer Match 70.14%

In cell j, I have formula =IF(SUMPRODUCT(ISNUMBER(SEARCH("VLXP",K2:AB2))+0)>=1,"Yes","No") that returns yes or no if VLXP is contained in any cell K2 through AB2 and it works correctly. What I would really like to do is then put into cell j the entire matching cell content or if not found return n/a. Is there a way to accomplish this maybe with VBA?
 

A:Solved: Excel if cell contains vlxp then put matching cell data in current cell

6 more replies
Answer Match 68.04%

hi, i have 2-excel cells in the same sheet, both contain manually entered numbers; cell-2 changes frequently; if the existing entry in cell-1 is < than the new entry in cell-2, cell-1 should immediately reflect this new value. how do you create this formula?
 

A:Solved: excel-replace content of cell-1 if cell-2 is > cell-1

8 more replies
Answer Match 67.62%

1) worksheet A

sheet1 has a number in cell a1

on sheet2, you want to reference the a1 cell in sheet1. you want to do this in sheet2 on cell a1. you want "100-{sheet1a1}"

2) worksheet B

on sheet100, you want to count how many cells has something in it from cell a2 to a200

cell can be empty

can you do this with text in cells? or numbers? or a mix?

you want to know how to do numbers though.

~ end ~

sorry don't know what to google, don't use this function in excel much.. googling is very messy, confusing, and scattered.

please explain clearly. please do not use excel terms. won't understand.

excel 2013

~ extra ~

an excel cheatsheet for this would be helpful since i'll forget pretty soon from not using that function or whatever much.

quality > speed

good links are good (sharing is caring)

*** ~ 1) Basic Referencing Cell From Another Sheet & 2) Counting Instances Of Anything ~ ***

A:*** ~ 1) Basic Referencing Cell From Another Sheet & 2) Counting Insta

Not sure I follow you, but here's how I handle cell referencing.

Suppose I have an Excel file. In it are several worksheets, each with a name as shown on the tabs for each worksheet shown across the bottom of the file.

Suppose I have a worksheet named cat and another named dog.

Suppose cell A1 of cat contains 44 and I want that value to appear in cell A1 of dog.

I'd use this formula in A1 of dog:

=cat!a1

The exclamation point forces dog A1 to look in cat A1 for the value. If you change the 44 in cat A1 to 99.32, the value shown in dog A1 will change to 99.32 as well.

Can't help with the other as I've never needed to do such a count.

2 more replies
Answer Match 63.42%

I have a sheet with 2 simple columns: Date and Price. I have imported the dates (##/##/####) and the prices ($###,###) by copy/pasting from the search results given to me by a niche database program I use. When the cells paste in, they all have the format "General".

When I try to format the "date" column into dates, it _does_ change the format as far as the cell is concerned, but the content of the cell doesn't adapt to the new format. For example, I have the date as 3/05/2001 and when I change it to a date format of MMM D, YYYY the content should change to March 5, 2001 but it doesn't. It is as if all the cells are forced to stay as text regardless of what the formatting is that I'm applying.

Same problem with the price column: if I change the format to include 2 decimal points, that format does apply to the cells, but the content of each cell remains without a decimal or anything following, as if the content is just text.

I have like 1000 rows in each column, and plan to do this analysis of the database's results frequently, so I'm hoping the answer isn't just to retype the data. There's got to be a way to copy/paste or export or something. Maybe I could copy/paste into notepad first to scrub out any formatting or locking from the niche database program?
 

A:Excel 2007 Cell Values Won't Take On Characteristics of Newly Applied Cell Format

Good news: Made some progress. In thinking that maybe each value had the textual single-quote forcing it to act like text, or maybe if I find/repaced all the dollar signs and commas that had been imported, I accidentally discovered that each and every value in my imported columns has a following space!

Bad news: Seems like Excel has a bug that thinks that if I say "Find=[singleSpace]" "Replace=[null]", then I should be given an error saying "Excel cannot find any data to replace". I think I'm doing the find/replace correctly because it worked on the dollar signs and commas.

Anybody know a workaround for the bug?
 

1 more replies
Answer Match 63.42%

I'm working on a spreadsheet at the moment which displays a range of cells all containing values referenced from another spreadsheet (within the same workbook). This system works fine.

Every day, the original worksheet is updated. So, it has fields already arranged up until the end of the year. A row for every date. Now, needless to say, rows for dates in the future contain no values, and so when the spreadsheet I am working on now references those cells, it displays "$0.00" (which is correct, given I am dealing with financial figures).

Now, all of that works as expected, however, on the spreadsheet I am working on, all of those figures are displayed in a line graph. This line graph, at todays date, shows an enormous drop given that the fields for the rest of the year all show a zero balance.

What I need to do, is to get the remainder of those fields (every field that says "$0.00") to not display anything at all. So, if the value is $0.00, it would not display a value at all, and therefore not show anything on the graph.

Can someone tell me how I can achieve this? I'm sure it can be done with an "if" statement, but I'm not sure how to structure it.

Any help would be greatly appreciated.
 

A:Solved: Remove Cell Value If Cell Value Is Zero (Microsoft Office Excel 2007)

=If(a1="","",Sheet1!a1) and drag it down.

Where a1 is the first cell in spreadsheet you are working on, and sheet1!a1 is the sheet within workbook containing figure.

Not sure if the graph will recognize the "blank' cell as blank or "0"
You could try that

Pedro
 

3 more replies
Answer Match 63.42%

I'm attempting to write my first macro for an Excel 2003 workbook. I'm not completely code illiterate (I've got moderate skills with AutoLISP), but I'm new to VBA and am not yet an Excel power user, so please be gentle.

The macro I want to write will:
check that the selected cell's content is underlined before proceeding
copy the content of the currently selected cell into an external plain text .log file
.log file lines should be: year/month/day - time - username - cell contents
.log file names will probably need to be generated
clear the cell's content and formatting (particularly underline and text/background color)
Here's what I have so far:
Code:
Sub Unpost()
If Selection.Font.Underline = True
Then Selection.ClearFormats And Selection.Clearcontents
Else
If MsgBox("The selected cell is not underlined...are you sure?", vbOkCancel) = vbOk
Then Selection.ClearFormats And Selection.Clearcontents
Else Exit Sub
End If
End If
End Sub
If I've written it correctly, it should currently do everything except log the cell contents. This, from what I've seen, is going to be the trickier part. I intend to use this macro 50+ times per weekday, so at some point the .log files will get too long to be useful, so I assume it will need to automatically create new logs (perhaps "year-month.log"). I've seen some useful info about appending to an external log here and here, ... Read more

A:Excel 2003 macro: log contents of selected cell, clear cell

You need to use the "File Scripting Object" to create and/or append text to a file. I've included a link below to get you started. If you are unable to figure it out on your own let me know and I'll write the code for you.

http://www.virtualsplat.com/tips/visual-basic-fso.asp

Rollin
 

1 more replies
Answer Match 63%

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

More replies
Answer Match 63%

Hello,

I cant seem work out a solution for what I'm trying to do. I have an Excel workbook that has multiple sheets. On sheet 1 i want the data from cell "G3" to be copied onto sheet 2. But i want the location on sheet 2 to be based on whatever was entered into cell "D3" on sheet 1.

For example: Sheet 1, cell D3 I have the name John, in cell G3 i have 68. I want "68" to be pasted in sheet 2 in cell B26.

But if the name in Sheet 1 cell D3 is Suzie, then I want G3 to be pasted in Sheet 2 in cell D26. So I would need to identify the paste location for each person.

I want the data to paste to the next cell so that the next entry can be pasted below the last entry for that person (for John the first entry would go into cell B26, then the next entry would go into cell B27 and so on).

But i want it to be a specific range, i dont want data to be pasted past 20 cells (cell B45). If possible a message box could be created to let the user know that the max is reached.

I would appreciate anyone's help with this as i have been struggling for awhile to try to get this. Thank you
 

A:Excel - Copy paste cell into range based on another cell

12 more replies
Answer Match 63%

Using EXCEL, I have a need to copy the cell contents from upper cells in col. A down a few rows in col A. There are various changes in data in col A as you will see below. The periods in the following info are used as placeholders only. B1, A2, A3, A4, etc. are blank. I need a formula because I have 60,000 records in the spreadsheet. Thanks in advance.

Here is how the data looks now.

....A.....B
Apple.........
..........Fire
..........Ice
..........Snow
Peach
..........Sleet
..........Rain
..........Fog

Here is how I want the data to look

...A ...........B
Apple
Apple.......Fire
Apple.......Ice
Apple.......Snow
Peach
Peach.......Sleet
Peach.......Rain
Peach.......Fog
 

A:[Excel] Copy And Paste Upper Cell To Lower Cell

With the workbook open press ALT + F11 to bring up the Visual Basic Editor. Once the VB editor opens, click INSERT --> MODULE and paste the code below into the blank module. Close the VB editor and select the first cell in column A containing your data you want to copy down. Click TOOLS --> MACRO --> MACROS and select the macro from the list and run it. This macro will copy all your data except for the last value in column A because without actually seeing your workbook, I have no way knowing which line to stop at. Therefore, the code will end when it reaches the last value in column A.

Code:

Public Sub CopyData()

Do Until ActiveCell.Row = Cells(Rows.Count, "A").End(xlUp).Row

ActiveCell.Copy
ActiveCell.Offset(1, 0).Select

Do Until ActiveCell.Value <> ""
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Loop

Loop

End Sub


Rollin
 

2 more replies
Answer Match 62.58%

Using Excel 2003 in Windows XP

I would like to use the contents of one cell as the destination location for copying data.
For example
I have 2 worksheets 1) Results and 2) info
in info
A1 = 'ABC'
C1 = 'Results!O54' < this is calculated based on other data in sheet.

Using a macro, I'd like to copy contents of A1 to cell location 'Results!O54' more specifically to where ever C1 points... C1 will change based on other data in info sheet.

The macro record for action looks like this (but I would like the 'O54' to be based on contents of C1 which changes)
Range("A1").Select
Selection.Copy
Sheets("Results").Select
Range("O54").Select
ActiveSheet.Paste
Sheets("info").Select

There is more to it then that but I think this is where I am stumped.
 

A:Solved: Excel: Uses contents of Cell to select a cell

Sheets("info").Range("A1").Copy Destination:=Sheets("Results").Range(Sheets("info").Range("C1").Value)
 

3 more replies
Answer Match 61.32%

What excel formula would I use to copy just the number after "CallID=" which in Cell A1 is 172155416 over to Cell B1?zttp://www.abcdefghijklm.com/webreports/audio.jsp?callID=172155416&mailboxID=280332&authentication=B4B15093Thanks Totriomessage edited by Totrio

A:Excel - copying part of a cell to a new cell

It depends.The main function you need is the MID function which has this syntax:MID(text, start_num, num_chars)If every cell has the same numbers of characters before the string you want to extract (in this case 58) and the string is always the same number of characters (in this case 9), then a basic MID function can be used:=MID(A1,58,9)If some cells might have a different number of characters before the string you want to extract but the string is always the same number of characters (in this case 9), then a FIND function can be added to find the "equal sign" and use that as the start_num argument for the MID function:=MID(A1,FIND("=",A1)+1,9)If some cells might have a different number of characters before the string you want to extract and the string might have a varying numbers of characters also, then a couple of more FIND functions can be added to determine the number of characters between the "equal sign" and the "ampersand" and use that as num_chars argument for the MID function:=MID(A1,FIND("=",A1)+1,FIND("&",A1)-FIND("=",A1)-1)If there is nothing consistent for the formula to find, such as an equal sign before the string and an ampersand afterwards, then things get considerably more difficult and we'll need some more examples.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

7 more replies
Answer Match 61.32%

I have an Excel 2010 workbook comprising a simple organization membership list, consisting of names, addresses, etc., for nearly 2,000 members, grouped into 5 chapters' sheets, in a single Excel 2010 file. Every time I sit down to enter updates for any member, or add new members, etc., I always start by saving my most recent version of the workbook with the date I last updated it in the filename, and then when I finish my updates for the day, I save the current day's updated workbook with the current day's date in the filename, etc. So after a while, I've got quite a stack of versions of the same workbook, albeit with slightly different data in some of the cells, from version to version.

Near the end of the year, it'd be neat to be able to compare the beginning and end of year versions, cell by cell, to see what changed.

Ideally, I'd like to sort of "subtract" one workbook from the other, cell by cell, and display something in the results that shows cells that differ btwn workbooks and cells that don't.

Obviously, I could just print the two versions, and plow through them both, side by side. But they're nearly 2,000 rows deep, all things considered, and with all the data we compile for each member, each row goes up to column AT! That's a lotta data to compare visually cell by cell.

Is anything like what I'd like to do possible?

Thx.

A:Can I compare 2 Excel spreadsheets cell by cell?

I think this might help if I read this correctly. I used this before to compare three lists I made a long time ago, then when I needed to update them I didn't want to sift through hundreds of entries.

Compare Two Lists in Excel - Easy Excel Tutorial

From MS https://support.microsoft.com/kb/213367

Also 4 Ways to Compare Data in Excel - wikiHow

1 more replies
Answer Match 61.32%

I have two cells, Cell "A" and cell "B", that have a formula in each. Cell "A" has a value that is correct and Cell "B" has a value that is correct. I now have a third cell (cell "C") with a formula that takes the values of cell "A" and cell "B" and multiplies them. The value of the product is wrong in cell "C" as compared to a value performed by a calculator. Cell "C" reports 51,550.64 whereas the calculator reports 51,540. What is the problem.

Thanks
 

A:Excel cell to cell multiply problem

I'm willing to bet that the number you are entering into the calculator are rounded off while the number that Excel is using is not truly rounded off. Even though Excel may display a certain number in a cell due to its format, it is probably using the true value of the number which probably includes several decimal places. What numbers are showing in cells A and B? How are cells A and B formatted? What happens if you increase the number of decimal points in these cells...do the cell number become larger? If so, then Excel is likely using the true values of the cells instead of the display values in its calculations. Provide details of how you are obtaining your cell values so we can confirm that this is happening.

Try the following

TOOLS --> OPTIONS and choose the Calculation Tab. Put a check in the box marked "Precision as Displayed."
NOTE: This will affect all other calculations on the workbook causing changes to other values on the sheet!

Rollin
 

3 more replies
Answer Match 61.32%

Is there a way to copy a col of formula's/math cells to a new col without excel modifying the coordinates?

Scenario: 150 cells of diff equations "located from a1 thru a150" you wish to move to d1 thru d150.

Problem: Excel modifies the coordinates of each pasted formula rendering garbage.

Individual f2, highlite,copy and then paste takes too much time if there are thousands of formulas to move.
thanks, nt
 

A:Copy cell to cell without excel modifying

Use absolute instead of relative references, ie:

$A$1:$A$150

Rgds,
Andy

EDIT: better explanation. You have 5 in A1, and the formula =A1 in B1. If you copy B1 to C1, C1 will read =B1. But if you make B1 =$A$1 and then copy it to C1, it'll hold.
 

1 more replies
Answer Match 60.48%

This is multi-part question on how to create different formulas on the attached worksheet.

#1 - I would like to count the number of Start Dates IF the Start Date is blank or greater than today, but only IF there is a date in the Requisition Date column. This would be to indicate the total number of open positions as of today.

#2 - I would like to count the number of cells in column A that are greater than or equal to 17 IF the Start Date is blank or greater than today, but only IF there is a date in the Requisition Date column. This would indicate total number of open positions that have a grade lever of 17 or above.
 

A:Excel-Counting with dates

7 more replies
Answer Match 60.48%

does anyone know of a formula to count how many numbers there are in a single cell?? For instance, Cell A1 has "32, 567, 47, 3563" I need a formula to count that there are 4 numbers there. Is this possible???
 

A:Excel Formula::Counting

8 more replies
Answer Match 60.48%

I run a (Count) formula in an Excel 2003 worksheet (SP2 Teachers and Students Edition) which I update every week. (It counts a series of dates i.e.

1/2/07
8/2/07

Count = 2

etc. )

My problem is that when I update each week, the count formula doesn't include the extra cells, but includes a green triangle on the top left of the cell where the formula lies. I have tried the F9 command to update calculation but this doesn't work. If I click on the cell where the count formula is I get the following screenshot.

Is there a way where each week that the formula is updated automatically?
 

A:Excel Counting formula

6 more replies
Answer Match 60.48%

Hi again

Once again, I would like to thank all of you for your help in Excel programming. You guys are GREAT!!

I have another slight problem I need help with. I have a column, say column A, with text. Most of this text is repetitive in nature. I want to keep only the first 10 rows of the text that is repetitive and cut and paste the other repetitive text to sheet 2. Here is an example: Let us say that Column A has this text. I just used any text. The columns could have repetitive numbers as well. This is just an example.

qwe
qwe
qwe
qwe
qwe
qwe
qwe
qwe
qwe
qwe
qwe
qwe
rty
rty
rty
rty
uio
uio
uio
uio
uio
uio
uio
uio
uio
uio
uio
uio
uio
uio

Etc down the column. This list is extremely long. As you can see form my example, there are 12 qwe's, 4 rty's and 13 uio's. I would like to keep only ten or less of each groupings and cut and paste those groupings in excess of 10 into sheet 2. In other words, when the macro is run, I would like to have 10 qwe's, 4 rty's and 10 uio's in sheet 1. Sheet 2 would then have 2 qwe's and 3 uio's.

I have tried to create a macro by myself but this is beyond my prgramming expertise. Any help woud be appreciated.

Mario
 

A:Counting rows in Excel

16 more replies
Answer Match 60.48%

Hi again

This time I've encountered another problem with dates. Below are a series of dates in one column formatted mmm-yy. I have many of these dates ranging from Jan-05 to Dec-05 and a few in Jan-06. I want to have a count of how many Jan-05 I have, how may Feb-05 I have etc. I want a number count. for example I have 2 May-05 and 8 Sep-05. The countif formula does not work since it requires the full date, i.e. May 12, 2005 etc. But then it only gives me 1 count only, since the other May-05 has a different date. All I want is to count the months only. Any idea how to do this?? Do you have to write a macro to accomplish this task??

Mario
May-05
May-05
Jun-05
Jun-05
Jul-05
Jul-05
Aug-05
Aug-05
Sep-05
Sep-05
Sep-05
Sep-05
Sep-05
Sep-05
Sep-05
Sep-05
 

A:Counting Months only in Excel

11 more replies
Answer Match 60.06%

I have an excel spreadsheet--in which I cannot use VBA (for security reasons)--and I am trying to count cells. Each day that an occurance does not happen, an accident for example, I add 1 to the current day (for weekdays) and one for each weekend. However, if an occurance does happen, I need to reset the count to 0. Across the top of the sheet, I have the dates. I have a formula that looks like such:=IF(I$1="E",COUNTIF($C$1:I$1,"E")+IF($C3="X",0,$C3),IF(AND(I$1="D",H$1="D",H3="X"),1,COUNTIF($B$1:I$1,"D")+IF($B3="X",0,$B3))). The problem is that the formula only counts from the begining, and I need it to change to where it counts from the last "X" (for weekday or weekend as needed). Basically I have two individual sheets on top of one another--!
I am sure that the answer is stupidly simple, I just can't see it. Any help is greatly appreciated.
 

A:Solved: advanced excel counting

12 more replies
Answer Match 60.06%

I have a database that I would like to use to capture numbers of events per month based on certain conditions. For simplicity sake assume I have three columns. Column 1 is the date. Column 2 is the division code (assume for a forumula that one of the codes is NI). Column 3 is the type of event (assume for the formula that one of the event codes is PER). I've been able to count the number of events where NI and PER occur using the COUNTIFS formula. However, when I try to narrow the count to a specific month I cannot seem to get the formula to work. Suggestions? Thanks.
 

A:Solved: MS Excel counting for three conditions

7 more replies
Answer Match 60.06%

Hi,

I have a data set on the semiconductor industry with 4 main columns that looks like this:

Alliance Description Product&Service Transistor_Technology Feature_Size

Alliance Description contains paragraphical company descriptions in each cell. For example, "Multi-year agreement for technology licensing and development. The three-phase agreement includes the transfer of Sharp's 0.25 process technology to 1Si, joint research and development of 0.18 technology, and a wafer supply agreement for Sharp. Sharp agreed to buy 20% of the fab's output (more than $300 million worth of wafer shipments) through 2005. "

Each Alliance can be described as under 3 broad categories of Product & Service, Transistor Technology and Feature Size.

Under each broad category of Product & Service, Transistor Technology and Feature Size, I have sub categories in columns as below:

Product&Service Transistor_Technology Feature_Size
DigBipolar StdAnalog ........ Gallium Silicon ....... >1micron 1 micron....

Total no. of sub categories for Product and Service: 15 sub columns (DigBipolar, StdAnalog etc)
Total no. of sub categories for Transistor_Technology : 10 sub columns (Gallium, Silicon etc)
Total no. of sub categories for Feature_Size : 10 sub columns (>1 micron, 1 micron etc)

The sub categories as stated above (eg., DigBipolar, StdAnalog etc) in each of the 3 broad categories contain either 0s or 1s (this is something like a flag or an indicator va... Read more

A:Counting rows with 0s in excel: Urgent

7 more replies
Answer Match 60.06%

Hi guys,

In a previous thread I have asked how to conditional format fields within a certain time frame and have been given the answer =TODAY()-[No of days].

This works sucessfully, so that if I want to mark every date more than a year old in red the command is =TODAY()-365.

Is there a way that I can then count these fields?

Basically.. after they have been marked red I want to know how many reds i have in each column..

Sorry... im an excel newbie .

Thanks
 

A:Solved: Excel, Counting certain fields

Use the CountIf function.

Select the range of cells that you want to have counted and then enter a condition like >365

That should do what you want to do.
 

1 more replies
Answer Match 60.06%

Hi, I have another counting formula required. I currently have 4 statuses New, Problem, Open and Closed. I have a count of days old for open items, the other statuses have -41128 etc.

I have built a formula to distinguish between anything other than "Open" as a "0" as below:

=IF(A:A<>"OPEN", 0,1)

What I require is a formula that will count up the amount of days in total of any open items and not count the closed, problem and new statuses. Please see attached example. Total of open days old should be 167

Cheers
 

A:Solved: Excel - Another Counting Formula

Try this:

Code:
=SUMIF(A2:A13,"OPEN",C2:C13)
Jimmy
 

2 more replies
Answer Match 60.06%

I have a list of incidents catagorised by Date, type, and Cause. I want to show (using formulae) totals as follows

All incidents in a given year - I've done this by using

=COUNTIFS($C$4:$C$123,">=01/01/2006", $C$4:$C$123,"<=31/12/2006")

for each year which works very well. Now I want to total how many of a specfic type of incident occur in a given year - I tried this

=COUNTIFS($C$4:$C$123,">=01/01/2006", $C$4:$C$123,"<=31/12/2006", $D$4:$D$124, "=contact")

which returns #VALUE! error, I'm guessing its because I've introduced a text value but am not sure.

There must be a simpler and more elgant way to get Excel to do what I want, can anybody help ?
 

A:Excel 2007 - conditional counting

From the help file:

"Important: Each additional range must have the same number of rows (and columns) as the criteria_range1 argument. The ranges do not have to be adjacent to each other."

There must be a simpler and more elgant way to get Excel to do what I wantClick to expand...

Using Pivot Tables in Microsoft Excel
 

1 more replies
Answer Match 60.06%

Hello. I'm on excel 2007. I have two lists of names and other information. I need a formula that will tell me how many names appear on both lists. I can't see anything that seems to do this or I'm just filling it out wrong. Please help!
 

More replies
Answer Match 59.22%

I am using Excel 2007

I have a .csv file that can be dropped into Excel (see attached - "January Example for Email"). Look at column "F - Study Date Time". I need to auto populate that column of information, separated by total number of entries for a particular hour, into a second spreadsheet (see attached - "CT Exam Hourly Matrix").

Looking at "January Example for Email", column "F", the first entry is "1/1/2012 1:24". On that date, from the 1:00 - 2:00 hour (using 24 hr military time), there is one (1) entry to autopopulate into "CT Exam Hourly Matrix" B:3. The second entry in "January Example for Email", column "F" is "1/1/2012 10:43". So there is one (1) entry to autopopulate into "CT Exam Hourly Matrix" B:12.

Column/Row F:19 & 20 in "January Example for Email" has two (2) entries for that hour to be autopopulated into "CT Exam Hourly Matrix" C:18.

Therefore, the total number of entries for a specific hour will be tallied (sum) and transferred to the second spreadsheet in the appropriate column & row.

Is there a means to automate this process using only Excel or VBA or ??? I apologize for being completely Excel "challenged" and would be most grateful for your assistance. I have no idea what I am doing or how to do it. This is strictly a job related / personal need project to make my daily life sane again.
 

A:Solved: Excel Vlookup and Counting Entries

Why the duplicate post?
 

2 more replies
Answer Match 59.22%

Hey:

I know this may seem a bit... unnecessary but maybe you don't mind helping me out anyway.

Say that ROW2 is the date 8/1/10, ROW3 is the date 8/2/10, etc., etc.

COLUMN A is the date, COLUMN B is salesperson #1, COLUMN D is salesperson #2, etc., etc.

Say that for B2, salesperson #1 sold all the units numbered 100356-100389. I want C2 to calculate the total number he sold, so basically all the numbers in that range, which is 34.

I could do the math for them all myself, but hopefully there's a function that'll do it in excel!

Thanks!
Nick
 

A:Solved: Excel Simple Counting Problem

I think you should try the countif function.
If you search in help or else just google for Excel COUNTIF and you'll get tips and hints
 

3 more replies
Answer Match 59.22%

Hi

I've got a spreadsheet with lots of names and email addresses in grids. These are spread across several workbooks, each being a different department. What I want to be able to do is, if a name or email address occurs in the worksheets more than a set amout of times for this to be either flagged up or highlighted. Does anyone know if this is possible?

Any help appreciated

Thanks
 

A:Counting names and email addresses in Excel

11 more replies
Answer Match 59.22%

Hello there!

I'm working with data on Excel and I need to count values from 3 columns lets say I have this:

Provider / Item 1 / Item 2 / Item 3
Will / pencil / pen / eraser
Jhon / eraser / pen /pencil
Luisa / pen
Charly / pencil / eraser
Joe / eraser
Luisa / pen / pencil
Jhon / pencil / eraser / pen
Will / eraser / pen
Charly / pen

I need to count how many of each items (pencil, pen, eraser) in all rows, does each provider requested i.e. Will: 1 pencil, 2 erasers, 2 pens. The items come from a fixed catalog, but not the providers, I guess I need first to sort the columns, one for each catalog item. but then how to count within all rows? The main condition should be provider's name.

Thanks!
 

A:Solved: Excel Column Sorting and Counting

If you're using Excel 2007, you could try something like the following:

=COUNTIFS($A$2:$A$10, "Will", $B$2:$B$10, "Pencil") + COUNTIFS($A$2:$A$10, "Will", $C$2:$C$10, "Pencil") + COUNTIFS($A$2:$A$10,"Will", $D$2:$D$10, "Pencil")

And just replace the values each time if you're only counting a small number of items. Or a better way,

Put the providers down the left hand side (remove all duplicates)
Put the items across the top (like a header column - again remove any duplicates)

Then use the forumla:
=COUNTIFS($A$2:$A$10, $A13, $B$2:$B$10, C$12) + COUNTIFS($A$2:$A$10, $A13, $C$2:$C$10, C$12) + COUNTIFS($A$2:$A$10, $A13, $D$2:$D$10, C$12)

Where $A$2:$A$10 = The range of providers names,
$A13 = The provider to be counted (from the list you created),
$B$2:$B$10 = The range of the first item column,
C$12 = The item to be counted (from the header range).
As you can see you just need to add on the COUNTIFS formula for every column that you have in your table data. Not ideal but possibly a solution?

Hope this helps!
Adam
 

2 more replies
Answer Match 59.22%

I am trying to figure out a formula for Microsoft Excel. I want to be able to count up the number of cells in a column that are being used (or have text in them).

Is there a formula that would count this? Many thanks in advance.
 

A:(Excel) Formula for counting cells in a column

10 more replies
Answer Match 58.38%

I am looking for a way to count the number of consecutive months for a given name in excel 2007. I have names in one column and dates in another column. I want to put the number of consecutive months (using the date field) for each name in a new column. I have attached an example of some data. I would like to automatically populate the number in the count column.
Thanks in advance
 

A:Solved: Help with counting consecutive months Excel 2007

9 more replies
Answer Match 57.96%

So I have a huge amount of data that are all projected visit dates that range over a course of future years. I want to pivot the data out so that I can relay back counts of Month/Year as needed to an individual text label. Everything I find online tells you how to pivot if you have data in a cell but not how to manipulate and work with dates in a cell instead.
Attached is my data.

I want to be able to use a pivot to tell me (for example) 'There are 68 visits occurring in January 2013 and those visits belong to site 1 (20 visits), site 21 (40 visits), and site 42 (8 visits)'. Or that it can tell me, for site 1, 20 visits occurring in January 2013 are for V2 (3), V5 (10), and V20 (7).

Any help is appreciated.

Using Excel 2007.
 

A:Excel 2007 pivot tables counting dates help needed

It looks like you don't have your table set up properly to get the pivot you're looking for. In order to do what you want, you have to have a column for Visit# and you would list your visit number there. Get rid of all of the other Visit columns. Then you have another column for date. Keep your columns for patient number and site number. Then you can create the pivot you want.

So basically, you want to convert all the visit columns (V2, V3, etc) to one column by patient and site, with the same patient listed many times in the same column.

The problem is, you want a pivot table that compares three values (three columns), Site, Visit#, and Date. But you have your dates separated across 52 different columns and that just won't work.
 

3 more replies
Answer Match 57.96%

Hello all,

There's a ghost in the machine! I'm running Excel '97 on Windows XP. In using the COUNTIF funcition, It will count certain rows of data, but not others.

Check this out...
In example below, the "count" column has formulas where I drag them down so

C2 =COUNTIF($A$1:$A$2000,B2)
C3 =COUNTIF($A$1:$A$2000,B3)
C4 =COUNTIF($A$1:$A$2000,B4)
etc.

In column "B" I manually typed "8:00", "8:01" and "8:02" and used auto-fill for the rest, as it goes on and on up to 16:00.

A B C
1 data cond count
2 8:00 8:00 1
3 8:02 8:01 0
4 8:03 8:02 1
5 8:04 8:03 0
6 8:07 8:04 1

Notice in cell C5, that nothing shows up! Even though there is a value in column A of "8:03". 8:02 shows up, and so does 8:04. So why not 8:03?

Here's the kicker - if I go into cell B5 and MANUALLY TYPE IN "8:03", then it gets counted in C5!!!

Problem is, the data column A is 1100 cells long and column B is 720 columns. I can't manually type in each time condition 720 times in column B.

I have checked the format of all cells, and they are all set to the same format of Time (13:30).

What am I missing?!? This is such a powerful function, but why is it not working for me? Any help or suggestions are severely appreciated!

Much respect,
Kiwi Berg

 

A:Solved: Excel Problem: COUNTIF not working - counting some data but not all!

15 more replies
Answer Match 57.96%

Hi there

I would like to count the visible rows of a table (tblProbRep) that has been filterred and then to select the last cell in column H to copy to another sheet.
I know the code for all except how to count only visible rows in vba .
Please can someone assist.

Thanks you
TMeerkat
 

A:Solved: Excel 07 VBA - Counting all Visible rows after a filter was applied.

6 more replies
Answer Match 52.5%

I need some help. I am trying to come up with a formula that counts the times that a "string" occurs based on a value in another column.....
Example:
Everytime column a = "Jones", check if column e = "tom".....if so count it.
 

A:Counting strings in one column based on Condition in another column in Excel

The EASY way to do this is to concatenate columns A and E using (for instance) this formula:

=A1&E1

or

=A1&" "&E1 (puts a space between two words)

Then count the individual field. Make sense? Will it work for you?

------------------
~dreamboat~
Brainbench MVP for Microsoft Word
Brainbench
 

3 more replies
Answer Match 52.08%

I want to use the value typed into cell D5 in later code. It is a value from a list and I want the code to find the value of D5 so that it can locate where to copy the information to.

Can anyone help?
 

A:Cell Value VBA Excel

Please post your existing code and/or sample workbook.

Rollin
 

1 more replies
Answer Match 52.08%

Office 2010/Excel I accidentally entered an email address into a cell and now I can't get rid of that format in that cell. No matter what I do, whenever I try to enter text into that cell it is always illuminated in blue and is underlined, just like email addresses are.
How can I make this cell into a 'normal' one again?
Thanks.

A:Excel cell

I have Office 2007. In the Home tab is there a "Styling" option like this?

Highlight the cell and click Normal.

3 more replies
Answer Match 51.66%

Trying to validate a cell as Decimal between 0 and 99999999999999.99. What I get is a rounded value like 12,345,678,901,234.1 instead of 12,345,678,901,234.12. Changing the edit to (13, 2) saves the decimal place but I loose the 10 trillion. Is this an Excel limitation or is there something else I can try. Using built in cell validation (Data --> Validation). What is the largest number Excel can handle? Thanks........
 

A:Excel Cell Validation

Excel's numbers are precise up to 15 digits, any other digits become 0's. This includes the decimal places.
 

1 more replies
Answer Match 51.66%

Hi there,

Can I run two function formulae at once in one cell in Excel? For example - I need to trim and change to proper text in a certain cell, so I want to run the =trim(A2) and =Proper(A2) at once - instead of running one - then copying and pasting special, then running the other.

Is this possible?
 

A:Two Formulae in one cell - MS Excel.

Hi gabyggod,

use this:

=TRIM(PROPER(A2))

should do the trick!

Arowana
 

3 more replies
Answer Match 51.66%

I would like to know if it is possible to set up tabs so that I can hit the tab button and go from (eg) A3 to G7 to B18 with out setting up buttons
 

A:cell movement in excel

12 more replies
Answer Match 51.66%

I'm creating a database on excel to work out my incomings/outgoings etc.

I have a row with titles on. How do I keep those visible while a scroll through everything else? Someone who knows how to do this should know what I mean.

-Jack
 

A:Excel cell problems

7 more replies
Answer Match 51.66%

hi all, can anyone help please

basically i have a worksheet that is protected except for the ranges F5:F15,G5:G15,H5:H15 and I5:I15 for data entry, i also have 4 optionbuttons 1 - 4, what i am trying to do is, if optionbutton 1 is selected then ranges G5:G15:I5:I15 become protected so only entries can be made in F5:F15, if optionbutton2 is selected ranges F5:F15 and H5:H15:I5:I15 are protected so only entries can be made in G5:G15 and so on through the 4 buttons i've checked various forums but can seem to find the answer, can this be done or is there a different way to go about doing this, any help with the coding would be appreciated.

scouse13
 

A:excel cell protection

8 more replies
Answer Match 51.66%

Quick question,

Can I fill a cell on Excel with something other than just solid color? Maybe crosshatching or some other pattern? I'm using Excel 2000.

Thanx!
 

A:excel cell fill

Originally posted by T-Bone:
Quick question,

Can I fill a cell on Excel with something other than just solid color? Maybe crosshatching or some other pattern? I'm using Excel 2000.

Thanx! Click to expand...

Have you tried Format/Cells/Pattern, click on the Pattern button at the bottom of the window?
 

2 more replies
Answer Match 51.66%

Hi,
In Excel, I have a macro which, when ran, resets the last used cell, so that when you press CTRL + End, the last cell (bottom-right) that contains data is selected.

Here's the code:

Sub Reset_Range()
ActiveSheet.UsedRange
End Sub

However, sometimes this doesn't work. I run the macro and the 'active cell' is an empty one way off away from the data. Presumably this is because something's been in those now-empty cells previously. Maybe the have cell formats or something, and it stops the macro from working correctly.

E.g. I have a file with data in cells A1:K6154. Before, and after, running the macro, pressing CTRL + End takes you to cell K8621.
If I delete the rows 6155 to 8621, re-run the macro and press CRL + End, it still takes me to K8621.
If I delete the cells A6155 to K8621, re-run the macro and press CRL + End, it works correctly and takes me to K6154.

Can the code be modified so that for cells to be within the "used range", they must have an actual value in them?

Cheers.
 

A:Excel VBA - setting last used cell

If your range is in a block, and always set to the leftmost column (ie. Column A) then perhaps you would consider using this to select the rightmost in the last row of your range...

Cells(Application.Rows.Count, 1).End(xlUp).End(xlToRight).Select
 

3 more replies
Answer Match 51.66%

I have HTML in a cell. There's something like this in the cell:

Code:
<a href="http://www.google.com">Google</a>
I want to get the web address from that cell and put it into another cell (I assume I use a formula). Any idea?

It is also possible the cell may contain multiple URL lines like that, and I would like for each extra URL for it to be put in a new cell.
 

A:Excel: Get certain text from cell?

6 more replies
Answer Match 51.66%

Is there a formula to make a cell change color when that cell has a specific numeric value? IE:

I have a small table. The total value column (say D5 - D10) is the cells I want to be color coded. D5 has the value 5. D6 has 6, D7 - 7, D8 - 8, D9 - 9, D10 - 10. Any cell that has a value of 5 and under is colored red, 6 - 8 is yellow and 9 and over is green so therefore D5 should be red, D6 to D8 is yellow and D9 - D10 is green.

Attached is an example.
 

A:Excel: value changes cell color

Under Format - Conditional Format

You should be able to accomplish what you need. It has 3 conditions to test for to apply your choice of cell format.
 

3 more replies
Answer Match 51.66%

Range("A2").end(xldown)

If I want to move to next cell, what statement i have to write.
want the next cell in downwards directions as using Range("A2").end(xldown).next.select takes me towards right direction
 

A:Excel-Moving to next cell using VBA

Hi and welcome. Let me move you to business applications for better assistance.
 

3 more replies
Answer Match 51.66%

Hi,

I've this strange requirement. I've 10 digit numbers in a column. These 10 digits can be any number from 0 to 9 and at any position. 0 can be the starting digit too.

I customized the cells using Format option and entered 0000000000 so that all numbers are displayed even with preciding 0's. All this comes fine. But when I pick up the number from another downstream applicaiton, it takes obnly the part of the number ignoring the leading 0's.
Can someone polease help me how to set this right!!!!!

Thanks in anticipation.

-Kashyap
 

A:Excel cell formats

Hi,

To what is the purpose of retaining the leading zero's for dependent calculations/manipulation? If you make the leading zero's permanent, this would effectively make the numbers text. Is that what you want?

If you want as text, here are some things you can do:

Preceed the cell entry with a single apostrophe (denotes textual values and will be treated as such)
Use a secondary cell to calculate your leading zero's (e.g. ..

=REPT("0",10-LEN(A1))&A1

..where A1 is your number entry)
Use a routine like this in your worksheet module (right click Worksheet tab --> View Code)

Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'** Change A1:A10 to desired range of change
If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
'** Add desired amount of leading zero values
Target.Value = "'" & WorksheetFunction.Rept("0", 10 - _
Len(Target)) & Target.Value
End Sub

HTH
 

1 more replies
Answer Match 51.66%

Hi, I have a document that I wish to protect specific cells in. I have done this and whenever I double click or attempt to type in a protected field an error message appears. This is a good thing.

What I want to know is whether or not it is possible to prevent single clicking on protected cells as well. Basically, for the few fields I want data to be allowed to be entered in, I want them to be the only accessible fields. I don't even want to be able to do as little as click on cells other than the few left unprotected.

Is it possible to prevent any form of access, highlighting, clicking, typing on protected cells?
 

A:Cell Protection (MS Excel 97)

7 more replies
Answer Match 51.66%

Anyone know if / how to make a cell in an Excel worksheet "blink"? I want to catch the attention to the cell I want others to fill in.

Thanks for your help.
 

A:Excel - blinking cell

I don't think this ism possible, you could however add some code so that when the sheet is activated a small routine runs two or three loops and changing the background color making it look like blinking adn if necessary have the cell keep this color if needs attention adn when the cell is corrected the on change routine would reset it to the default background.
 

1 more replies
Answer Match 51.66%

Hi,

In Excel can you have two formulas for one cell? On the example below, I need to have it so it goes +100% if they are over their goal but -100% if they fall below. I have the conditioning rule for the negative to go red.

Varietal Ship Goal Shipped +/- Percent of Goal
Chard 385 602 156%
T Chard 32 9 -72%

Also, for some reason the other person who works on these files along with me, cannot highlight the row if there is a conditioning rule anywhere? Any thoughts on this as well would be quite helpful. Thanks in advance!
 

A:Excel - Two Formulas, One Cell

Hi, welcome to the forum.
Two formulas is a no no, but the formula can have nested IF and OR statements, your example is not quite clear (to me)
About highlighting, if conditional formatting is actie in that cell you can change and hightlight what you want, conditional formatting overrules this.
 

2 more replies
Answer Match 51.66%

Is it possible to change the color of a cell using a formula in Excel?
 

A:Cell color in Excel

13 more replies
Answer Match 51.66%

I am trying to populate some cells in excel from sheet1 to a continuous column cells in sheet2... so I would like to do is use vlookup to find the next non-blank cell in a range on sheet 1 and populate a column in sheet 2 from top to bottom. this is what I have so far.
=IF( AND( NOT( ISBLANK(Estimate!D3) ),$I$36=TRUE),VLOOKUP(Estimate!D3,Pricing!$C$4:$G$200,2,FALSE),"")

=IF( AND( NOT( ISBLANK(Estimate!D4) ),$I$36=TRUE),VLOOKUP(Estimate!D4,Pricing!$C$4:$G$200,2,FALSE),"")
 

A:excel next non-blank cell

got it figured out. see below.

=IF(A2<$K$35,"0.00%", IF(A2<$K$36,-$J$35, IF(A2<$K$37,-$J$36,IF(A2<$K$38,-$J$37,IF(A2<$K$39,-$J$38,-$J$39)))))

thanx for your help
 

1 more replies
Answer Match 51.66%

This post is from another board. I have seen this problem quite a while ago and resolved it (Excel 97), but can not remember what caused it.
As I "remember" the solution was relatively minor, but .....

Anyone have a clue?

Tx

"On my XP machine, for some reason recently if I open Excel 2002 and try to click anywhere on the spreadsheet, it will highlight a cell. Then I move the mouse and it keeps dragging an area to select. The problem is I click it another 10,000 times and it won't stop selecting cells. I have no clue why this mouse isn't working correctly. It works just fine everywhere else, but for some reason in Excel it suddenly found an error or something in the software and it seems to like it."
 

A:Excel - Cell Selection

7 more replies
Answer Match 51.66%

I have 6 columns which have hours & minutes worked per day, eg; 8h 37m. In the 7th column I want to add these together & have the result displayed as h & m.
In the 7th column I right clicked, format cells, custom, h.mm, okay., I then clicked the cell & in the text bar entered .... =SUM(C2:H2)., but the answer does not appear as hours & minutes.
Also once these formula's are entered can I click & drag over multiple rows.

Thanks in advance
 

A:Excel 2 Formulas' in 1 cell

It sounds just like you want to join the text string up. You do this by putting an & between the cell references you wish to join

=A2&B2&C2&D2&E2&F2

you might also type:

=CONCATENATE(A2,B2,C2,D2,E2,F2)

It's not a mathematical / summing function because there's no way of excel knowing that what you are stringing together is time related.
 

1 more replies
Answer Match 51.66%

If in cell A1, the user was able to write a cell number of their choice, say B5.
In the cells that they would be choosing, would be numbers. In cell A2, I want to use the contents of the cell that the user has typed into A1. Not the contents of A1, but the contents of the cell that has its cell number in A1. In this case, I would want to use the contents of B5. There will be around 15 boxes in which the user is free to write any cell number they choose. Then, in the formula box (A2) I would read the contents of these cells, and if something was there get its contents and then use the cell number that it says in a sum, in which the numbers in all of the cells which the user has chosen were added up.
What could I do?
 

A:Excel 97 Cell Contents

9 more replies
Answer Match 51.66%

In cell B5: 54.5 3L 1-39.16

In order to extract the 3 that is next to "L" in the middle
of the text, I made up the following formula:
=IF(R4>1,MID(B5,(FIND(" ",B5)+2),LEN(B5)-((LEN(B5)-FIND
("L",B5)+1)+((FIND(" ",B5))+1))),IF(R4=1,-MID(B5,(FIND
(" ",B5)+2),LEN(B5)-((LEN(B5)-FIND("L",B5)+1)+((FIND
(" ",B5))+1)))))

(Please note: there is a condition set for a value in cell
R4 too (If R4 is greater than 1). And also the text in the
B column which B5 is a part, varies in length, so the formula did get a bit clunky).
Anyway, I end up with "3" displayed in the cell that contains the above formula, as I wanted.

Say the formula that produces "3" is in cell S4: If I enter: "=S4" in another cell, excel doesn't seem to recognize "3" as S4 value. All I get is "=S4" in the new cell!

I can't do anything with it. Excel does not seem to
recognize the value "3" from the above formula. So can't use its value in another cell.

Is there a way to get around this? I have formatted the
cell to General, Number, etc. Doen't seem to make a
difference.
 

A:Excel does not seem to recognize a cell value.

Hi Brendon,

Try changing the formatting to General, then select the cell above S4 and then do a Find>Replace. Replace = with = and see if that fixes it. Don't ask me why, but I had the same problem before, and this solution was given to me by an Excel MVP at another help forum. It worked like a charm for me.

Good luck,
Richard
 

3 more replies
Answer Match 51.66%

i enter a whole number (any cell) and the result is a decimal number.
ie: enter 4 - result .4
have tried diffirent things with no change.
 

A:excel - cell format.

7 more replies
Answer Match 51.66%

I have already got the connection between the excel and the PPT open in my current code, and I am running a loop to add slides to the ppt based on another cell value. But, I now want to add a section of code to the loop that will look at the slide for the

OLEFormat.Object named "PageTitle" and replace the placeholder text with the

xlWB.Worksheets("Title").Range("C" & i).value from excel.

Any suggestions?
 

More replies
Answer Match 51.66%

EDIT (Again): ok, saving is working now too. Even thuogh I don't actually know what was wrong with my original code

EDIT:
Reading is working now but saving isn't. Please help me figure out how to save my spreadsheet. I'm calling:

inputBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
sheet = (Microsoft.Office.Interop.Excel.Worksheet)((inputBook.Worksheets).get_Item("Sheet1"));

((Microsoft.Office.Interop.Excel.Range)(sheet.Cells[row, column])).Value2 = "some text";

book.SaveAs(output, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
it saves, but the .xls file is completely empty. Please help me solve this!
>Original Post:
>
>
>I need to read cells' values in C#, from a spreadsheet. That's pretty much it. I need to be able to write to them later too, but I can't even get Read to work. Here is my whole code:
>
>
>string input = @"C:\Users\James\Desktop\foo.xls";
>Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
>Microsoft.Office.Interop.Excel.Workbook inputBook = app.Workbooks.Open(input, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true... Read more

More replies
Answer Match 51.66%

I am trying to return the value of the last completed cell in a row.

Using arrays, I've tried lookup, but it doesn't seem to like a formula as the critera. Match returns the position in the array, but not the value.

This is for a spreadsheet that is updated monthly--not necessarily on the first of the month so logic using dates won't work. I trying to create a summary table to automatically use the latest entries from the spreadsheet.
 

A:Excel-value of last nonblank cell in a row

Match will get you the row number, so use it in the Index function..

=Index(array,Match(),colnum)

So I'm not reinventing the wheel, Bob Phillips has a nice write-up on it...

http://www.xldynamic.com/source/xld.LastValue.html

HTH
 

3 more replies
Answer Match 51.66%

I want to ad 20% to the same cell I'm typing the value in, how do I do that ?
Example: I type 100 in cell L2 and when I move away from L2 it should add 20% and show 120.
 

A:Excel: Add % value to same cell as you type in

Only way I can see is to add a helper column with formula in the column where figure would normally go.
Suggestion attached

Pedro
 

2 more replies
Answer Match 51.66%

I just made a spreadsheet and i have formuals in certain rows i do not want to have changed. I understand to protect things i have to protect the sheet. But becasue some people will be needed to make changes to different variables i need to have certain cells "unlocked" so that they can go in and freely input there info on a weekly basis but not have the ability to delete the forumlas i wish to have locked in.

Can anyone help me please.

Time is of the essence.

Im handing the reigns over on monday.....

Thanks for any and all help.

A:Excel Cell Protection.

Protect the entire sheet. Then unprotect cells you allow to change.

3 more replies
Answer Match 51.66%

Hello,

Here is what I would like to set up to happen automatically when I enter data into excel. For example, if A1 is less then A2, the cell (A1) will have a single line border. If A1 is less then A3, the cell (A1) will have a doted line border. Also, if A1 is less then both A2 and A3, there will be a double lined border around the cell (A1).

I know how to do this in conditional formating, but as far as I can see it only will work for one cell at a time. I can't make it do the 3rd option if A1 is less then both cells.

Does anyone have any ideas or need further clarification?

Thanks.
 

A:Excel Cell Formating

9 more replies
Answer Match 51.66%

Sub NewInvoice()
' THIS PART WORKS OK!!!!
' Will replace Ctrl+n with "when workbooks opens do this"
' got that part ok already.
'
' NewINvoice Macro
'
' Keyboard Shortcut: Ctrl+n
'
' get new number for invoice
'
Dim x As Integer
Workbooks.Open Filename:="d:\number.xls"
x = Workbooks("number.xls").Worksheets("Sheet1").Cells(1, 1)
' Sheets("Quote").Activate
ThisWorkbook.Worksheets("Invoice").Range("n4:n4") = x
x = x + 1
Workbooks("number.xls").Worksheets("Sheet1").Cells(1, 1) = x
Workbooks("number.xls").Save
Workbooks("number.xls").Close

' I need HELP!!! here.....I'll write in plain English what I want to happen.
' Can somebody please write it so it works???

' select n4
' save current file (invoice.xls) using "File menu, Save As"
' use the contents of n4 as the filename.xls
'
'
' eg: n4=2 so new filename is 2.xls leaving original "invoice.xls" as blank
' so that when finished editing invoice, on close of Excel it asks do you want to save changes?
' tell it "yes" and it saves as 2.xls not overwriting invoce.xls

' any help would be much appreciated!!!!!
End Sub
 

A:Excel. Using cell value as new filename.

How about you save invoice.xls as invoice.xlt instead? that way, you have the template, doubleclicking it will open a new xls doc based on that template.
But, that will not allow you to save the file in the manner you want, for that, you will have to add the following to your code:

ActiveWorkbook.saveas Filename:=Range("n4").Value

see if that works
 

3 more replies
Answer Match 50.82%

Hi all,
i am trying to create a summary page for a test document, each module of testing has been given its own tab in the workbook and i want to use a macro to:
1, look up the correct tab
2, find the latest test run that has been finished
3, return the test round number, the number of tests run, and the number of tests that passed.
I am using Excel 2003 edition.

I can make the macro the problem is i have to make 3 SEPERATE macros to return the 3 values. I would like to only have one that can return all three values.

the following ths the macro that i have so far:

Option Explicit
Private m_iColumn As Integer 'm_iColumn currently on
Private Const m_iColumnJump = 7 'number of m_iColumns in between test runs
-----------------------------------------------------------------------------------------------------
Function Last_Test(Round_lookup As String)
m_iColumn = 3
Do Until Worksheets(Round_lookup).Cells(3, m_iColumn) = "0" Or Worksheets(Round_lookup).Cells(3, m_iColumn) = vbNullString
'runs until finds nothing for a test run
If Worksheets(Round_lookup).Cells(3, m_iColumn) = "0" Then
'looks to see if cell contains a testrun if it doesnt then
Else
m_iColumn = m_iColumn + m_iColumnJump
'adds 7 to the m_iColumn number
End If
Loop

If m_iColumn = 3 Then
Last_Test = "Test run not started."
Else
Last_Test = Worksheets(Round_lookup).Cells(1, m_iColumn - 7)
'the last m_iColumn is the name we want
End If
End Function
---------... Read more

More replies
Answer Match 50.82%

Hi everyone,

Have a general column of data in the following format:

A1
clarisselaan 29
I want to split it as following

A1 B1
clarisselaan 29

It is a large database, does anyone know a formula for this?
I cannot do it in text to columns because some names has space in between so i would get like 3 columns.

I need to separate the number from the text in to another column.
Hope to have been clear enough.

Many Thanks,
 

A:Excel 2010 split cell

6 more replies
Answer Match 50.82%

Excel had generally three sheet.I like formula like copy to sheet1! to sheet 3but if sheet1! any cell empty then take value from sheet2! is this possible?

A:Excel Formula copy of cell

Try this...=IF(Sheet1!A1="", Sheet2!A1, Sheet1!A1)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

3 more replies
Answer Match 50.82%

Is it possible to change the cell location coordinates eg FV|178, AR|23 etc.
I need to have numerals only in the cell location coordinates eg 67|43, 139|88 etc.
At the moment I am using a seperate set of numbered axis to identify cell locations which gets quite confusing as I have to keep scrolling to them to see where I am.
Hope this makes sense!!
 

A:Solved: Excel cell location

14 more replies
Answer Match 50.82%

Hi,

I am a begginer in excel programing and i don't know how to use cell value inside range command in VB
 

A:How to select a range in Excel using a cell value

6 more replies
Answer Match 50.82%

I've uploaded a test file for you to look at.

If you look at the file you will notice I have cell C17 as a totals cell for C4:C16. I would like to have an additional cell F17 to show me the remaining balance to be paid. But I need this to lower based on an x in a D cell by the corresponding C cells value.

So basically F17 should show 626 to start. An "x" in D5 lowers the amount by C5's value which is 191 making F17's value 435. Adding another "x" in D6 would lower it by an additional 96 making the value 339. So on and so forth.

I hope I explained that well enough. Can anyone help?
 

A:Excel multiple cell subtraction

Hi
See if this does the trick ...

=SUM(C4:C16)-SUMIF($D$4:$D$16,"x",$C$4:$C$16)

or just

=SUMIF($D$4:$D$16,"",$C$4:$C$16)
 

2 more replies
Answer Match 50.82%

Hi Guys,

where can I find out what the format of the edit mask is that you see when you select Format, Cells, Number Format , Custom?

e.g. _-* #,##0_-;-* #,##0_-;_-* "-"_-;[email protected]_-

I've found that the first 3 blocks indicate the formating for +ve, -ve, and 0 but the above example has 4 blocks; what's the 4th block for? and can there be more 4 blocks?

Thanks
Hew
 

A:Solved: Cell Formatting in Excel

6 more replies
Answer Match 50.82%

I need to export the contents of one and the same cell to 100 different text files.

For example... I need the contents of Sheet1 Cell A1 to be exported to new text files. The names for each text file is on Sheet2 Cell A1 - A100.

Does anyone have a code example for this? I have search and found some similar but i couldnt figure out the code to modify it.

Thank you in advanced.

asm
 

A:Export Excel Cell with Macro

Hi asm,
and will the exported file be a new one or can it be an existing text file?
If the file is already presnt then what? Delete the file or append the new data to it?
Which version of EXCEL are you using?
Macro's a problem ?
 

1 more replies
Answer Match 50.82%

I need help.a1*b1= my total,i want to link my total to e1*f1 = new total.Reason i also can put in a number into cell e1 if i want to .NOT this way a1*b1=total which is c1. c1*f1=total which is g1.
 

A:excel link into a empty cell

9 more replies
Answer Match 50.82%

There is a file shared by my group where we track work orders scheduled by date. Column 1 contains a list of dates, in order, like so
5/30/06
5/31/06
6/01/06
etc.
Sometimes, when you open the file, someone else has scrolled up or down the page, so if you want to view today's date, you have to scroll up or down to find it. What I would like to do is create a macro that takes you to the cell containing today's date. I tried creating a macro that did the following:
1- Go to a cell containing a formula =TODAY()
2- Copy the value of that cell (this will be today's date)
3- Open the Edit --> Find window
4- Paste in the value I just copied
5- Click find

This worked great on the day that I created it, but today, it still goes to yesterday's date. When I look at the macro, what it's doing is always searching for the value that "TODAY" had on the date I created the macro. It's not smart enough to actually paste in the value I copied, but instead sees it as FIND: WHAT="5/30/2006"

Is there a way to use the Find or CTRL-G, or any other formula to take me to the cell containing today's date? It seems like this should be a simple thing, but I just can't figure out a way to make it work.

Thanks for any help you can offer!
 

A:Excel: Go to cell containing today's date

If you have a cell on the worksheet with =Today() in it, that cell will always show the current date. You look up that in your macro and use that to find your current date in the data.
 

3 more replies
Answer Match 50.82%

I'm trying to save a various word documents into their respective directories, using an excel cell as a reference.

eg. i have a word document that i want to save into C:\(variable directory name)\admin\ where the variable directory name is taken from a cell in a spreadsheet (i.e. a four digit number). i want to be able to do this automatically, and i thought that vba would be the best way of doing it, as there are loads of documents.

any ideas?
 

A:Referencing excel cell in VBA word

13 more replies
Answer Match 50.82%

is it possible to have individual cells change colour automatically when a worksheet is active based on time.
i.e

cells c1,c2,c3,c4,c5 (all cells are blue)

what i would like to happen is when the sheet is active the first cell (c1) changes colour from blue to say red,,after 5 seconds the 2nd cell(c2) changes from blue to yellow, 5 seconds later the 3rd cell changes and so on until all 5 cells have changed, then after 5 seconds all 5 cells revert back to blue for say 10 seconds then the process starts again until the sheet is closed

any help would be appreciated
 

A:excel cell colour change

8 more replies
Answer Match 50.82%

How can I use a formula to make a cell truly blank?

The only way I know is to use =IF(ISBLANK(A2),"",A2)
but although if A2 is blank, the new cell appears blank, Excel treats these cells as if they contain text.
When you sort cells containing formulas that return empty double quotation marks, they are placed above blank cells.

Is there any way to create truly blank cells with a formula or function so they are sorted as blank cells at the bottom in both ascending and descending order?
Thanks,
 

A:Excel - how to make a cell truly blank?

Holly, check out my response in the other thread you started:
http://forums.techguy.org/business-applications/601257-using-linking-master-data-sheet.html
 

1 more replies
Answer Match 50.82%

I have a list of email address (i.e. "[email protected]" <[email protected]>, adriana lastname <[email protected]>) and they are all in one field in excel, how do I separate them so each email is in its own field in a column?

A:how to separate emails in excel that are all in one cell?

First,I don't know if you had posted actual email addresses, but I edited them to ensure that they were not valid. It is never a good idea to post actual email addresses in a public forum. It's less about caring whether the people receive spam and more about the security of our forum. We do not want to become known as a forum where valid email addresses can be harvested. Once the bad guys get in, it's really hard to get them out.As for your question, take a look at the Text-To-Columns feature on the Data ribbon.You should be able to use the Delimited option to separate the data at a specific character, perhaps the comma.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

2 more replies
Answer Match 50.82%

I have a Mac and I am trying to use the excel application. I need to start a new line in a cell and in the help menu it tells me to hit control, option and return. When I do that it sends me to a new cell, it does not create a new line in the cell I am in. I've used another computer in my office and in excel I hit control, option and return and it creates a new line in the cell, so it must be something with my computer. Any help will be greatly appreciated.
 

A:Excel. Mac. Can't start a new line in a cell?

Not sure if it is different in Windows version, but I just do ALT + ENTER to get a new line in a cell?

* I guess the mac does not have an ALT key, so that won't likely help.
 

1 more replies
Answer Match 50.82%

hello guys, I have a list of part numbers and a list of photos names as an entry i.e. 12345.jpg. Happens that the part number is the same as the photo name except with the .jpg extension.

I need to look in the part number column see if the same exists in the photo id column and if it does I need to write the photo id name next to the part number column.

If tried lookup, if's and last or(exact) formulas with no results.

Has anybody encounter a similar problem ?

JR
 

A:Excel Cell range lookup

13 more replies
Answer Match 50.82%

When the ink color and background color are the same, I only see the formula when I mouse over or click on a cell. C3 has yellow for text & background. Line 3 is also Frozen.
Is there a way to easily see my totals without changing the text color? That's all I'm concerned about other people seeing.
Even a mathematical genius can't figure out the total without knowing the hidden amounts.
I played around and googled this for 6 hours and I'm still no closer to working it out.
Thanks for help you can give me.
 

A:Solved: Excel - Can't see total in cell

9 more replies
Answer Match 50.82%

Hello,

We work with an excel sheet with multiple colleagues, This workbook is protected on some columns/fields and the workbook is shared.

Now we have several columns that keep changing it's cell properties. For instance:
Column: License Plate, this should have the property general, but every morning a colleague has to manually change it from date to general again, next morning the same thing.

This colleague has first removed the protection and share, next changed the column property and next put the protection and share on again. In my opinion the right procedure.

Does anyone have an idea what could be causing this?

Thanks in advance,
Wouter
 

A:Excel 2013 cell properties

13 more replies
Answer Match 50.82%

Hello,

I can't for the life of me remember how to put two formulas in one cell.

At the moment I have:

Cell 1 which contains a total:
=SUM(D68:T68)
Result is 5,245

Cell 2 shows the total as a percentage:
=$U68/U$83
Result is 9.16%

How do I put this into one cell to produce:
Total = 5,245 - 9.16%

Thank you in advance.
 

A:Solved: Excel - Two formulas in one cell

16 more replies
Answer Match 50.82%

I am trying to write a macro that looks at each row in sheet A, finds the last cell in each row, does a compare to the cell to the left in the same row on the same sheet and based on the result enters a value in the same cell address in sheet B.

I have the majority of it written, but can not seem to get the part where the active cell from sheet a becomes the active cell from sheet B.

Thanks
Lori
 

A:EXCEL VBA -same cell diff sheet

OK, for a workbook with 2 sheets:

Sheet1 has values 1, 2, 3, 4 in A11, and values 5, 6, 7 in A2:C2.

If you run the line below with Sheet1!A1 selected, it'll put 1 in Sheet2!D1.

If you run it with Sheet1!A2 selected, it'll put 5 in Sheet2!C2.

Ideally you should upload a workbook containing some sample data & the code you have already, so that we're clear on what you're aiming for.

Sheets("Sheet2").Range(Cells(ActiveCell.Row, 256).End(xlToLeft).Address) = ActiveCell
EDIT: forgot to say welcome to TSG.
 

2 more replies
Answer Match 50.82%

Hey Guys,
A little technical wizardry required for this problem, although it may just be impossible period.

what I am trying to do is best described using the numbers below treated as if they were individual cells

10 2 20 100 .20

So cells 1 and 2 multiply to get cell 3 or 20, cell three is then divided by cell 4 or 100 to get the value for cell 5 or .20.

What I would like to know is can I also affect cell two and three by changing cell 5, basically could I change the .20 to a .25 therby making it so 20 become 25 and the 2 become 2.5....
So having another formula that acts at the same time like cell 5 multiplied by cell 4 giving a new cell three which would at the same time have to change cell 2`s value from 2 to 2.5

Anyways i appreciate any help possible and thank you for looking at this question
 

A:Excel- Can two formulas affect the same cell

If all you want to calculate is cells 2 and 3, and the values in cells 1,4 and 5 are input manually, cell 2 = cell 3/cell 1, cell 3 = cell 4/cell 5.
If you want additional cells to be solved with formula you're out of luck, as you end up with an infinite loop of a formula with only one datum referencing a formula with one datum referencing the first formula ref............
 

2 more replies
Answer Match 50.82%

What I want to do is have a word in one cell, then sort the letters of that word in alpha order and put the result in another cell. Some kind of alpha sort function. So that 'backed' would become 'abcdek'.

Seems like it would be easy, but you know how that goes. Any ideas?
 

A:Quattro Pro/Excel - Sort within a cell

10 more replies
Answer Match 50.82%

I have the following formula:

=IF(Estimate!D2=0,"",VLOOKUP(Estimate!D2,Pricing!$C$4:$G$200,2,FALSE))

Where is says "Estimate!D2" I would like the "2" in D2 to become a variable so it take its info from another cell. i.e. I want to enter a number in cell F20 , lets say 15. to replace the "2" in D2. So now my formula would get info from cell F20 and excel would see it as D15 now. I tried put it in brackets and quotes and I cant see to get it to work. can anyone help.

thanx
 

More replies
Answer Match 50.82%

Background: I have 5 cells: Four contain scores (between 1-10 ? using drop-down menus) and the fifth evaluates the 4 scores and if any of the four cells contain a certain value (above 5) this cell reflects a numerical value of 1, only once regardless of how many times the score is above 5. This is to use Pivot Tables.

Additional Issue: there are half-scores in TEXT format, because the client wants the export to MS Word to show a 1.5/2.5 up to 9.5 etc with a DOT, not a comma.

Progress to date: Before it was text, I just used >= than 5 for the formula, but now I have the added issue of the TEXT. I?ve successfully created a formula for one of the cells, but can?t get it correctly combined for all 4 cells. What I have used is this:

=IF(OR(EE4>=5;EE4="5.5";EE4="6.5";EE4="7.5";EE4="8.5";EE4="9.5")*AND(EE4<>"1.5";EE4<>"2.5";EE4<>"3.5";EE4<>"4.5");1;"")

I can?t get it to apply to cell BT4, CO4 and DJ4 regardless what combination I have used to duplicate the formula to include all the cells.

Attached is an example of what I'm trying to do, in a simpler file example.

Can you assist?

Thank you
 

More replies