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

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.

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?

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?

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 ~ ***

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.

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?

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?

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.

=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

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

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

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.

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

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

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

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.

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

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

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 repliesI 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.

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

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

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

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

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.

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.

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???

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?

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

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

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.

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.

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

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

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.

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

Try this:

Code:

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

Jimmy

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 ?

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

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!

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.

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

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

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

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!

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

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.

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

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.

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.

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

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

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.

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

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?

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.

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

Highlight the cell and click Normal.

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........

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

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?

Hi gabyggod,

use this:

=TRIM(PROPER(A2))

should do the trick!

Arowana

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

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

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

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!

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?

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.

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

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.

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.

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.

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

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

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

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

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?

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.

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.

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!

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.

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),"")

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

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."

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

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.

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?

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.

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

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.

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?

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

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.

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

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.

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

Suggestion attached

Pedro

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.

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

3 more repliesHello,

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.

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

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

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

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,

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?

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 repliesIs 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!!

Hi,

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

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?

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)

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

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

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 ?

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.

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!

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.

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?

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

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,

Holly, check out my response in the other thread you started:

http://forums.techguy.org/business-applications/601257-using-linking-master-data-sheet.html

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?

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 repliesI 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.

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.

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

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.

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

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.

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

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.

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

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............

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?

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

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