Hi everyone,

I'm having some trouble autofilling an Excel formula. On my 'Data Entry' tab, there are several columns of data. For every three data entries, there is a merged cell in the column adjacent to the data that performs a 'SUM' function. For example, cells C4, C5 and C6 are summed in a merged cell that covers D4, D5 & D6.

I am trying to transpose the data in the merged cells into another tab for analysis and other functions, including plotting. The formula for this is quite simple - " ='Data Entry'!D4 ". This is in cell A4 of the 'Processing' tab. In Cell A5 of the processing tab, I want the data from the next merged cell on 'Data Entry', so this is cells D7, D8 & D9. Again, the formula is simple - " ='Data Entry'!D7 ". I have entered these two and have the result I need. Problem is, there are several thousand merged cells that need transposing into 'Processing'. When I try to autofill my formula down, it fails to skip the cells in between and starts doing this:

='Data Entry'!D4

='Data Entry'!D7 (So far, so good)

='Data Entry'!D6 (Returns a zero value, because it is merged with cells D4 and D6 on 'Data Entry')

='Data Entry'!D9

='Data Entry'!D8

And so on.... I've filled in several formulas to try and get it to work, but no matter how far I go (I got to D34) it just refuses to work.

I know that I can use the formula " ='Data Entry'!D4:D6 ", and that this will solve the issue, but this will not plot in the way I need it to. I need a 'Scatter with Lines' graph, and when I tried plotting the transposed data using this formula, the lines on the graph were notably absent. As far as I can tell, this is down to the formula, because it works fine when I use the " ='Data Entry'!D4 " style formula.

Please let me know if any other information is required - I've tried to be as descriptive as possible without confusing the issue.

Many thanks in advance

CBR

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link __ http://downloadreimage.com/download.php__. (This link will automatically start a download of Reimage that you can save to your computer.)

How are you doing the "autofill"? Are you doing it with highlighting the entered cells so far than then "pulling" the handle at the bottom right corner of the highlighted cells? Or are you copying and pasting? It may not matter, but if you have only tried one way, then try the other.That is my best thought, which might not be much.

Read other 7 answersHi,

I am having problems trying to fill formulas in Excel 2007 for the rest of the Summary worksheet. There are basically other 4 Qtr1 - 4 worksheets in the workbook with exactly the same layouts. Under the selected cell, I got the formula =SUM('Qtr1:Qtr4'!B5), after which i tried doing the normal dragging to try to fill up the other columns but it did not seem to work. After which, I went to Excel options > Proofing > Autocorrect options > Autoformat as you type > fill formulas in tables to create calculated columns, it is ticked. I do not know what is the problem? Can someone help me? Thanks. Would really appreciate a lot.

I'm having an issue with an excel formula.

I have:

Code:

=

IF(D7="DSOA",'Common Milages'!D8,

IF(D7="SRHS", 'Common Milages'!D6,

IF(D7="ECT", 'Common Milages'!D7,

IF(D7="-","0",

))))

This code works fine on a single cell. The problem occurs when I try to duplicate it, either by using 'fill series' or copy and paste.

Excel automatically increments the cell numbers, so that D7 is D8, and so on as it goes down. This is a good thing.

But the formula also references a second sheet 'Common Milages'. The cell values in the formula that references this sheet always needs to stay the same. In other words 'Common Milages'!D6 should always be 'Common Milages'!D6, no matter where the formula is located.

Is there a way to exclude certain cell numbers from auto-incrementing?

Hello all,Hoping someone can point me in the right direction on this. I have a user on our network (win2k servers, XP pro clients) who logs into the domain but uses a local (non-roaming) profile as he travels with his machine off-network quite often. When he opens up IE and types in "www.a" into the url it finds / prefills with "www.anot-goodsite.com", not exactly what you want to come up in front of a client, especially when your corporate website starts with "www.A" I've cleared cache & such, ran all the "normal" spyware removal tools, have good virus protection, etc. and have been unable to find a fix for this. Interestingly enough, it only happens when logged in on that computer as that particular user, so it appears to me that it's part of his profile. I'm reluctant to delete his local profile as there is a LOT of setup done on this particular machine that would be time consuming to reproduce. Any help or ideas would be greatly appreciated.BobMod Edit - Link to inappropriate site altered - Leurgy

In IE, go to Tools>Internet Options>Content>Autocomplete>Clear forms. That is where you can clear the URL's history and disable saving URL's. You can also run Ccleaner to delete index.dat files to remove the history.

Read other 2 answersHi guys, im trying to find out how to solve this for really long time, so the last option is to ask someone more experienced - you! :)

My problem:

After filling a webpage with informations and choosing from dropwdown menues and selecting files to upload, I want to save this, to send this "filled' page multiple times in time. All the files to be uploaded will be in the future in the same place on the computer, so i hope its not a problem.

What im looking for:

Some software, add-on, browser plugin, anything, that will just by clicking fulfill the information once again like i did last time. I have more then 1 version of that what i want to save like this, so just saving it like Project 1, Project 2 etc would be enough.

Example:

For once i have filled the whole page with alot of info, choosing from drop down menues and chosen few photos that will be uploaded with sending the page. I will hit the "button" that will save all this filled info and i will name it Project 1.

After 1 week, i have to fulfill that same page again, so i just hit the Project 1 "button" and all the info will appear. Something like more complicated copy paste :)

Hope I well expressed myself. If you dont understand some part, let me know. I will try to explain it in more details :)

Hi, welcome to TSF

RoboForm is one of the most popular programs of this type. It should be able to do what you're asking. See here for details: How RoboForm Works (scroll down to the 'Fill Forms Fast' section)

Depending on which browser you're using, there are several other alternatives, like this Firefox extension: FillForms

I use my xp computer to download and recently whenever I go to any search box this fills completely with zero's and when I try to delete they fill again.

It not a sticky key as I use the same keyboard for my vista machine.

Hello,

Have you scanned your computer for malware?

On the computer running xp every search box fills with zeros and will not let me delete them or enter anything else. Is this a virus and how do I get rid of it?

Perhaps a stuck key on the keyboard? Have you tried a different keyboard?

Read other 3 answersHi guys, im trying to find out how to solve this for really long time, so the last option is to ask someone more experienced - you! :)

My problem:

After filling a webpage with informations and choosing from dropwdown menues and selecting files to upload, I want to save this, to send this "filled' page multiple times in time. All the files to be uploaded will be in the future in the same place on the computer, so i hope its not a problem.

What im looking for:

Some software, add-on, browser plugin, anything, that will just by clicking fulfill the information once again like i did last time. I have more then 1 version of that what i want to save like this, so just saving it like Project 1, Project 2 etc would be enough.

Example:

For once i have filled the whole page with alot of info, choosing from drop down menues and chosen few photos that will be uploaded with sending the page. I will hit the "button" that will save all this filled info and i will name it Project 1.

After 1 week, i have to fulfill that same page again, so i just hit the Project 1 "button" and all the info will appear. Something like more complicated copy paste :)

Hope I well expressed myself. If you dont understand some part, let me know. I will try to explain it in more details :)

Hi, welcome to TSF

RoboForm is one of the most popular programs of this type. It should be able to do what you're asking. See here for details: How RoboForm Works (scroll down to the 'Fill Forms Fast' section)

Depending on which browser you're using, there are several other alternatives, like this Firefox extension: FillForms

Google Toolbar not auto filling in IE12. Don't know what is wrong as I have it set to auto fill!

There seems to be a problem, quite a few are having issues,

IE11 Google Toolbar no auto search suggestions, no search history? - Windows 10 Forums

You sure about that version of IE? I thought we were still on 11.

Google Toolbar not auto filling in IE12. Don't know what is wrong as I have it set to auto fill!

There seems to be a problem, quite a few are having issues,

IE11 Google Toolbar no auto search suggestions, no search history? - Windows 10 Forums

You sure about that version of IE? I thought we were still on 11.

Help! I dont even know whether it can be done in Excel XP. Every week I do a table for Sports results. Teams in random order with results of that game. ie Team a 6 - 1 Team b So team a won got 3pts for a win 1 for a draw and 0 for a loss. I have 18 weeks of tables for these events. I want to make another table to calculate the position of these teams.

Have 5 columns

Rnd, Date, Team, Score, Team

1,15/03/2003, Burleigh Heads, 6 - 0, Banora Point

1,15/03/2003, Merrimac, 6 - 0, Kingscliff

1,15/03/2003, Coomera, 1 - 2, Mudgeeraba

1,15/03/2003, Northern Stars, 0 - 1, Southport

1,15/03/2003, Murwillumbah, 6 - 2, Palm Beach

1,15/03/2003, Burleigh Heads, 3 - 3, Runaway Bay

2, 22/03/2003, Mudgeeraba, 1 - 0, Northern Star

2, 22/03/2003, Merrimac, 6 - 0, Kingscliff,

2, 22/03/2003, Coomera, 1 - 2, Burleigh Heads

I want to make another table from this one to calculate the points in a league system. So if after 2 rounds Mudgeeraba have played 2 games, won 2 games, scored 3 goals, had one goal scored against them, thus they have 6 points.

So to reflect these scores:

Team, Games played, Won, Lost, Drawn, Goals Scored, Goals Against, Points.

So every week I would like to be able to enter the results in the above table of the scores then these calculations are automatically entered into the results table, ending with a league table.

Hopefully this is a little clearer to understand. If its still a little confusing my web page is

http://home.austarnet.com.au/nellie/14div1

Cheers Sca... Read more

Hi, Scad.

I don't mean to be rude, but I cannot understand what you're asking and, likely, others can't either and that's why you're question hasn't been answered.

Can you:

Give us column headings.

Put commas into your explanation, because we cannot figure out where your information starts and ends.

and even REALLY helpful:

Save your workbook or a reasonable sample as a text file, and load it up here.

Is there a way to view the formulas in the cell in order to print them out, rather than seeing the result?

Thanks!

Tools-Options, View tab.

Check Formulas.

This also allows for printing.

There's a shortcut for it, but it escapes me at the mo...

I have been asked to help with Pledge management at church and having manipulated lots of numbers into order I am now trying to do two things (1) is to show whether a pledge is equal to or more or less than the previous year; seemed simple enough to compare two columns and then a assign an answer, however I can do any one of the three comparisons and get the correct answer but fail to string them together and get the correct answer....I have been trying to use the if/or function. (2) if I could then sum how many of each the Equal or Increase or Decrease answers are i can give the treasurer what he seeks.

I am using Windows XP Professional with Excel 2003 (11.5612.5606) there is windows service pack 3 installed at work. I am trying to create a file that will track percentages for emloyees. The pink highlighted ones are nights and the white is days, they have different formulas. The sheet is set for automatic updating but they are not updating the percentages. If you change the entered numbers then change them back to zeros, they don't enter 0%. I'm not sure it uploaded the file either. It says invalid file when I try to upload, it's 133 KB.

can you zip the file up and then load - MAKE sure that no personal data exists - this is a public forum and you need to protect any data - so use dummy data

Read other 2 answersHi all

Please refer to the spreadsheet attached...

Can anyone tell me if there is a formula to find the minimum score & maximum score for each column? I know I could just enter it myself but I just want to know if such a formula exists.

Also, could someone please check my formula in the ‘Results’ column to make sure it reflects the following correctly?

If the students average is greater than 75, they should receive and ‘Excellent’ comment, between 55 and 74 is ‘Good’, between 45 and 54 is ‘Fair’ and anything below 45 is ‘More Work Needed’.

T.I.A!

Hey guys,

I have a little problem with excel. I did allready research on the web but

nothing works.

I want 2 formulas in 1 cel but my percentage doesnt work.

You can see attached my excel file.

I want the winst/verlies and rendement in one cel.

Who can help me?

Thank you

Hi Bellic,

Welcome to TSG. You can't display both a currency and a percentage in the same cell and keep the formulas. You can kind of do it for display purposes with something like this (I used $, but I know it's really euros, so you'd use the euro symbol):

="$"&J2&" "&K2&"%"

But you wouldn't be able to perform calculations with that cell. Is there a reason you need to display them both in one cell?

I am using the "Rate" formula to arrive at the

ineterest of a mortgae payment.

I enterred the ff:

MOnthly Payment ( principal & ineterst)

Number of total payments

Loan Amount

After entering the numbers, I get NUM#

What am I doing wrong?

Thanks for any inout!

Hi,

I'm trying to create a Summary page which is fed by a Data sheet which will be populated by multiple users. I've a couple of questions on how to show running totals which are dependant on multiple critieria.

On the Summary sheet I need to show the number of times a value is entered into a certain column (Column G) for each manager.

In addition I want to be able to show on my Summary sheet if the value in column I on my Data sheet has not been updated within 15 days of the date in column G. This must also be specific to each manager.

There are 7 managers on my Summary sheet and 200 rows on my Data sheet.

I'm sorry if I've not explained this very well. Please contact me if you have any questions.

Thank you so much for any help you can give me.

Hi, welcome to the forum,

A small sample with non-sensitive data and mention the version of Excel you are using will make it easier to answer

I am wondering if it is possible to imbed a Vlookup formula inside of an IF formula.

I am creating a spreadsheet in which I have a column with dealer prices and another column with Retail prices. I want to be able to create a formula that allows me to pull up a value from either the Dealer column or the retail column depending on which radio button is pressed.

This is the formula that I have and it is not working for me please let me know if I am missing something

.

=IF(EndDump!$G$3=1,"=vlookup(EndDump!$G$2,EndDump,4)",=if(EndDump!$G$3=2,"=vlookup(EndDump!$G$2,EndDump,5)","")

Thanks,

Semduvidasinger

As far as I know this should be possible. However, reading this may help:

http://www.cpearson.com/excel/nested.htm

Looks like the syntax may be wrong, I don't think the = goes inside a nested function.

Guys,

I need help with a formula in excel.

I am trying to have the formula say

If A1>100 and A1<500, Display X if not leave blank.

I have the if(A1>100,"X","") down, I just need help with the syntax.

Help Please!

I need some help plotting and graphing some equations that look like this,

y = e^(-x^2)

y = sin(x)

I have a few more, but you get the gist.

Any help would be greatly appreciated.

Cheers.

Sounds like homework. Did you do a search on Google? There seems to be lots of information about it.

Hi I need some help with formulas in excel.

This is what I need and I cant figure out an easy way to do it, only manually.

ok so I have a spreadsheet already set up and i need to average a column, easy enought however i need to only have certain rows in that average.

column c has either the words SFR or Lot

column v has numbers

i need only the cells in column c that reflect "lot" to be in the formula for average on column v

How do I do this?

Thanks in advance

something like this... =DAVERAGE(C1:V6,D1,C1:C2)

the C1:C2 has to reference the column name and with C2 contains the value LOT, you can just stick it somewhere as a reference, doesn't seem to let you hardcode the LOT value

Hello,

I am trying to add two formulas in an excel cell.

I have a work sheet that has the minutes spent on a job and the staff member doing the job.

I then have a list of names (significantly longer than the example)

Next to that list I want a count of how many times each staff member has spent over 180 minutes on a job.

ie. in the example sheet, J. Bloggs name appears three times, however on two occasions he has spent over 180 minutes on seperate jobs, so it should count as two in the far right column. if his name was to appear again having spent over 180 mins on another job then it would count as 3 and so on......

I can get it to count how often his name appears, i just cant do it to only count when the job is over 180 mins..... can anyone help?

Gary

Hi, welcome to the form.

Place the formula below in I2

Code:

=COUNTIFS(C:C,$H2,B:B,">180")

This will give you 2 for Bloggs, just copy the formula down for the others

Hi all,

I'm sure this is the stupidest question ever but I've been trying to sort it out for about an hour, and I can't seem to get at the problem!

I'm filling in a timesheet made for me in Excel, with details of number of hours spent on each project over a period of days. At the end of each row is a total cell, with a formula in it that calculates the total. An example of this formula is =SUM(B16:AF16) where 16 is the row number. The formula works fine in some rows, but not in others - sometimes it just doesn't add up any of the numbers, sometimes it ignores all numbers below 1, so that when I've spent half an hour on something and entered 0.5 it just ignores the value. I've checked all the cells are in general format not text, and I know that the row numbers within each formula are correct - beyond that I'm baffled! Pleeeeeeease can someone enlighten me?!

Put a 1 in any blank cell.

Copy it.

Select all your hour amounts.

Hit Edit-Paste special, Multiply.

This forces Excel to see the cells as containing numbers.

It's just a stupid Excel thing...

If that doesn't work, send the file to [email protected]

Need a formula for number of years worked. In other words, looking for a formula to take info from cell such as date hired through current date to =current years service. Any info creating formulas. Thanks.

Use the datedif function

i.e. =DATEDIF(Date1,Date2,"Interval")

Where

Date1 is Start Date

Date2 is the end date

Interval is one of the following: You must use the inverted commas

"m" Months

The number of complete months between Date1 and Date2.

"d" Days

The number of days between Date1 and Date2.

"y" Years The number of complete years between Date1 and Date2.

"ym" Months Excluding Year

The number of months between Date1 and Date2, as if Date1 and Date2 were in the same year.

"yd" Days Excluding Years

The number of days between Date1 and Date2, as if Date1 and Date2 were in the same year.

"md" Days Excluding Months And Years

The number of days between Date1 and Date2, as if Date1 and Date2 were in the same month and the same year.

Anyone know how to set the formula for sum = + or - 5%? I can only do one or the other, but not both at the same time. Thanks muchly!

I NEED HELP BAD on formulas in excel for Vista. Trying to make a spreadsheet of percentages off a total amount.

Example: in cell A... any # I key in it will x # by 2.8%= total in cell A. then cell B is cell A total x 17%= total in cell B x 18%= total in cell C= total in cell C x 65%=cell D

This will happen no matter what # I key into cell A. It will auto cal cell ABC&D

A x 2.8% B x 17% C x 18% D x 65%

I hope someone can understand what I am trying to do. HELP PLEASE ASAP!!!!

Hi Ginkle,

I can?t tell why Excel is auto calculating without more info. What were you originally trying to calculate?

Also, I would need to see what formulas you?re using. Could you submit a screen shot with formulas displayed?

HOW TO DISPLAY FORMULAS:

Press CTRL + ` (grave accent).

To switch back to hidden formulas: Press CTRL + ` (grave accent) again.

Fortunately, Excel is easy to work with, so it should be a simple fix

First post-probably a long one. Sorry, username says it all.

An Excel file of 23 columns and ever increasing rows (currently 3000+) keeps track of data on customer transactions. Column T records each transaction's location name, column U records the location number, and column W records the sale price.

I'm creating stats of:

1. Top 15 regions by number of sales

2. Top 15 regions by sale amounts

There are hundreds of location numbers (formatted 000-000 or 0000-000) relating to regions. For example, 357-000, 780-000, 930-000, and 1446-000 are all main locations of region A. Additional loctions change the three digits after the hyphen; however, 357-003, -004, -005, belong to another region (meaning I can't just look at the numbers before the hyphen).

I used SUMPRODUCT to calculate the number of sales, but had to enter every single location in the formula to get it to work. And once another location is added to any region, that region's formula needs to be updated.

I used {=sum(if(u15:u10000="357-000",w15:w10000,0))} but it only allows me to add one location's sales totals at a time.

I even created another spreadsheet with all the location numbers and names to try to use VLOOKUP to at least standardize the names and go from there, but that formula =VLOOKUP(U2634,Locations.xls!$A$2:$B$370,2) is giving a result of #N/A.

I can't include a copy of my spreadsheet b'c the info is considered confidential. Please help! Please no VB sol... Read more

How do I create an IF formula that has more than one criteria to produce more than one result? For example: =IF(A40 <1400,14)or IF(A40>1399 and <1600,13.75) etc.

You could just use "nested IFs", eg:

=IF(A1<1400,14,IF(A1<1600,13.75,0))

Personally I quite like a variation of VLOOKUP that dispenses with the actual lookup table :

=VLOOKUP(A1,{0,14;1400,13.75;1600,0},2)

HTH,

Andy

I am looking for help with an excel formula. I'm not sure which formula I need. I have two spreadsheets. One is the budget for the year. The other is actual cost for the year. these are both by month. At the end of the actual cost spreadsheet I have a column to put year to date budget. I want to be able to compare the budget to the actual as I enter each month's actual. So I need the formula to only add the budget amounts to the month that my actual has filled in. Can anyone help with this???

I am working with two fairly large spreadsheets (different workbooks) and need to create a formula that will do the following (if possible):

IF B2 on sheet 1 equals a cell in column B of sheet 2

THEN write the value of the cell in column D (sheet 2, same row as match in column B)

IF no match, then write 1

I hope that this is understandable and I REALLY hope that someone can give me a clue.

Thanks in advance!!

This will work

=IF(ISERROR(VLOOKUP(B2,Sheet1!B:B,1,0)),1,VLOOKUP(B2,Sheet1!B:B,1,0))

Just wondering how use formulas to find the average of a list of numbers. How can I find formulas to help with this.

=average(a1:a10) will provide the average value of cells A1 through A10.

Hello,

I am trying to create a new spreadsheet for work that needs to have certain look up fields that return certain values depending on what options the user selects.

My spreadsheet columns are as follows:

A= Category

choices in category are - Cooker, Dishwasher, Washing Machine

C=Deliver From

choices in deliver from are - supplier A, supplier B

J = Installation cost (this would be £10 for cooker, £15 for dishwasher and £20 for washing machine from supplier A and £1 for cooker, £5 for dishwasher and £8 for washing machine from supplier A )

I am trying to make the spreadsheet automatically populate J (with the costs above) when A and C are both selected from the drop downs. So there are multiple outcomes.

So, if washing machine was selected and supplier A it would populate £20, but if supplier B was selected it would populate £8. Then if dishwasher was selected from supplier A it would populate £15.

Hopefully this makes sense and someone can help me! I have managed to do part of it but cannot do the 2 lots of choices in 2 fields.

I am using a mac

Thank you

Hi all. I'm on Vista and an Acer Aspire 5920G laptop.

A few weeks ago, my pc stopped remembering my username on my Hotmail account.

I didn't change anything and wasn't aware of downloading anything that would have changed the settings. I have tried to sort it out, but can't make it happen.

Other usernames arre remembered (the one on TechGuy for example).

I go to my emails quite often during the day and have a long username, so it's getting a bit boring.

Can anyone offer a solution??

Thanks in advance. Mark.

Hi have you tried system restore to a date when it was working

First off I'm new to the forum, and just wanted to let you all know that everthing I know about Excel I tought my self. So, that being said I need help with mixing functions and formulas. What I want to do is be able to type a number in to a cell then in the adjacent cell have the number divided. But the problem that I've run into is I need this number to be rounded down. I'm not sure how to add the rounddown function with my existing formula. Any help will be appreciated.

Justin

Can you average Letters instead of numbers? Say I want to grade some people, simple first column name, every column after is a grade A-D and the letters NA as well. I have another sheet labeled "Grades" I use to perform one column is the grades and the next column is their numerical identity. I know in excel you can embed up to 7 "if, then, else" functions. I also have experience using "sumif" so I was curious can I combine one function to show an average of letters using a "vlookup" table to know what those letters weigh numerically on another sheet. But would the formula know if the letters equate to something like 3.7 to round it up to an A or would I have to write one formula to get the mathematical expression and another formula seperate to get that value's grade?

EG: check out my excel sheet, I would prefer to stick to letters just for my own liking since I see numbers all day long and want to know if I can do this. I know I can itemize things with sumif and I know I can go the opposite way and give different numerical grades a letter grade with an "If, then, else" function that embeds multiple ones. But can I do it the opposite way and is it a combination of a VLookup that I'm not that familiar with using and something else?

Any help is much appreciated, please see attached.

I am very new to using excel that said

I have been working on a simple spread sheet and changed everything in the spreadsheat to a table just so i could read it easier. Since I did that none of my formulas are showing up and I don't know how to take it out of table format and not sure if that is really the problem. Thanks if anyone could help out?

I just realized that the sheets I am using I have been copying and pasting from my original worksheet. Do formulas not get copied into new worksheet?

Hello. I am using Excel 97 here at the office. I have numerous spreadsheets that have formulas in them. About a week ago, the spreadsheets quit automatically calculating the formulas. Say the formula is to calculate the sum of a column of numbers. I change on of the numbers in the column and the formula does not automatically update until I save the file.

Also, I copy a formula a lot and paste it to cells along a row in the spreadsheet. Now I get a message that says "cannot empty the clipboard". It still lets me complete the paste function but only after hitting the OK button on the error message twice. Rather annoying since I do this all day long.

I've searched Excel's stupid help feature everywhere and cannot find an answer. I, also, searched Microsoft's tech support section and no help there either. Please help!!!!!!

Check to see if the calculation mode is set to automatic by going to Tools/Options/Calculation tab and tick automatic.

Here is the scenario...

C10 is blank, F10 is 1

Using a macro, I would like to place a formula in C10. The formula is ...

=IF(F10=1,dateandtime(),"")

This is how I would manually add this formula if I typed it into the cell. However, the complexity of this spreadsheet dictates that I add it using VBA. I've played around with a variety of code and this is as close as I can get to making it work. However, it still bombs out.

Code:

Range("C10").Value = "=IF(F10=1,dateandtime(),"")"

Now I've determined that the problem is the "" in the actual formula. So if I change the code to this, it works.

Code:

Range("C10").Value = "=IF(F10=1,dateandtime(),C2)"

I'm not looking for C2 or a value of any cell. I realize this could be used as a work around, but can quotation marks be used in VBA for this scenario? I thought somewhere I seen &" be used, but that is not working either.

Well, well ... sometimes asking your own question sparks another idea to test. Typically, it is one of those solutions that makes you say "Duh".

***Double the quotations***

So for the next person searching for an answer ...

Code:

Range("C10").Value = "=IF(F10=1,dateandtime(),"""")"

This works correctly.

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.

Hi all!

I am having a problem with Excel formulas on a time sheet. I need it to calculate the total days taken as annual leave and public holidays but I can’t get my head around the formula. Can someone please help?

On the time sheet:

Annual Leave Entitlement is 30 days and is in Cell G8.

Public Holiday Entitlement is 10.5 days and is in Cell G9.

If we take annual leave, we put 08:00 (which is 1 day, half-day would be 04:00) in Column I and “A/L” in Column J.

If we have a public holiday we do the same but put “P/H” in Column J.

At the bottom of the sheet, I need to show the total A/L days remaining and the total P/H days remaining at the end of that month.

So, in a nut shell I need some sort of formula that translates the hours (08:00 or 04:00) to days (1 or 0.5), then deducts this from the total allowance for A/L and for P/H. (e.g. If cell J20 says A/L, then deduct cell I20 from cell G8) (can that even be done??)

Does that make sense? Sorry if I’ve explained it badly, I am getting myself into a muddle just trying to think about it!!

On a separate issue - I also don’t understand one of the formulas that is already entered...

K5 has the value 40:00 and the formula =(+G5/24)+(H5/24/60). G5 is 40 and H5 is blank. What is the point of the formula, what does it actually do?!

Any advice would be greatly appreciated!

Thanks

S x

I'm trying to create a spreadsheet for some CDs I have, and I was wondering how I can go about subtracting a sum of numbers from another number. So it would be:

# - (sum).

Normally, this wouldnt be a big deal, but I would like to have all this in a time format. I cant seem to figure out how to create this formula so any help is appreciated.

Hi tech guys I am trying to figure out the formula to comapre date ranges in two columns. I work in procurement and I want to know if date in the first column as compared to the second column date is < or = 5 days. If the date is > than 5 days it would "not meet" our variance schedules. For Example: 11/2/11 and 11/5/11 Meets our variance date, however 11/2/11 and 11/12/11 does "net meet" the variance.

Can you please help?

you could use conditional formating to cange the colour of the cell that does not meet - as well

the if statement would be

assume 1st column is A and second is B

Example: (A2) 11/2/11 and (B2)11/5/11 Meets our variance date, however (A2) 11/2/11 and (B2) 11/12/11

=IF( B2 > A2+5, "Not Meet", "Meet")

with dates if you add +5 that should add 5 days

depends if there is any time involved

i have added the conditional format as well in attached spreadsheet exampe

The company that I am doing internship for has a file in excel that needs to be updated. They keep informations of different companies in one sheet, and each sheet is named by diferent months. Every time each company pays the bill, the "Status" cell is classified as "paid", else "unpaid". When "unpaid" cell are still the same by the time the next month arrive, all this companies that didn't pay yet are copied to the next sheet, which is the next month.

Example:

I have the following Columns (They keep informations of different companies):

Debtor, Shipment Status, Invoice, Broker Code, Amount, Status, Amount Paid, Date and Shipping Date.

I wanted to create a formula or Macro to transfer all these unpaid info to the next month's sheet automacticaly, and leave the paid one where it was...

How do I do that?

ZIP and attach a sample workbook to your next post and either me or someone else will write the code for you.

Rollin