Computer Support Forum

Solved: Excel calendar simple function

Question: Solved: Excel calendar simple function

Hi all,

I believe this next function I'd like to have would be very simple, but I just don't know how/where to start.

I got an calendar overview in excel for the year 2010.
Now I'd like users to fill in something at a specific date with a form.
So they should select the month and daynumber and the text they fill in should be added in the cell next to this. See an example below. On the right-side of each number, there is an extra empty cell. So I.E. Here was filled in after they selected Januari 2nd Text: I.E. Here.

Can someone give me an example code?

Thanks in advance,
Willem

Relevance 100%
Preferred Solution: Solved: Excel calendar simple function

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

Answer: Solved: Excel calendar simple function

Don't need an answer anymore. I made it a lot easier for me, people just have to typ in the stuff on the correct place.

So this thread is now marked as 'solved'.

1 more replies
Relevance 85.69%

Hi,

Is there a way to get excel to convert a time to a decimal? I want 6:45 to return 6.75 or 6:30 to return 6.5
If it helps, here's my situation:

I'm a waiter trying to learn excel. Thought it would be nice to use it to track my working habits and wages. I'm trying to enter tips received(C), time in(E), time out(F), and then I want to use functions to give me how many hours I worked, and what my total pay was, and my hourly.

So in (I) I've put the function =E-F to get the total hours I worked, but I can't figure out how to convert the hours to a decimal so I can use them in math functions.
Here's an example,
In at 4:30 out at 11:15. e-f returns 6:15. I want to take that and multiply it by minimum wage. but to do that I need 6:15 to be 6.25.

Thanks for any help.
 

Answer:Solved: Simple excel function question

9 more replies
Relevance 73.39%

I have the following formula that is returning the words TRUE or FALSE.=IF(AND(L6=1, R6=7),1,0)If L6 equals 1 and R6 equals 7 shouldn't this formula return 1 in the cell? I get TRUEIf L6 equals 1 and R6 equals 5 shouldn't this formula return 0 in the cell? I get FALSEIf L6 equals 0 and R6 equals either 7 or 5 shouldn't this formula return 0 in the cell? I get FALSE.It seems so simple. What am I missing?You help is really appreciated. Terry

Answer:Windows Excel 10 IF AND function broken but really simple

I'm not sure why, perhaps the way the cell is formatted(?) but it seems that Excel is converting your 1 and 0 to logical 1 and 0, therefore returning TRUE or FALSE.That does not happen when I paste your formula into my spreadsheet.You could try this, but I don't see why you would have to:=IF(AND(L6=1, R6=7),"1","0")This forces a text 1 or 0 in the cell. Depending on what you are doing with the 1 and 0, Excel may or may not consider the text value to be a number. You'll have to try it and see.If it doesn't like it, you could try this to force a number, but again, you shouldn't have to.=IF(AND(L6=1, R6=7),"1"*1,"0"*1)or=IF(AND(L6=1, R6=7),1*1,0*1) (untested, since I can reproduce the problem)I'm just starting to use 2010 on a daily basis, so I don't know if there is a setting that would force 1's and 0's to be considered logical values. I've never heard of it.Have you tried this formula in a different workbook?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

5 more replies
Relevance 66.83%

On my worksheet I have three columns
Column B4 =276 Minutes
Column C4 = 14 seconds
In column D4, I need to calculate the minutes into seconds in B4, add the seconds in C4, convert the total seconds of both B4 and C4 back into minutes x £0.20
So far I got as far as =SUM(B4*60)+C4, which gives me 16574
I need to convert 16574 back into minutes then * the figure by £0.20

Thanks
 

Answer:Solved: Help with simple function please.

I think this should work.

=(((B4*60)+C4)/60)*0.2

Rollin
 

2 more replies
Relevance 66.01%

using MS Works 9 Spreadsheet
I can do a basic spreadsheet sort of but when it comes to the IFs commas colons () I am lost
I only need information from col F Totaled in col M if the cell in col I has no value
Col F is FullPriceSubTotal Col I is SalePriceSubTotal Col L is TotalSavings Col M is FullPriceSubtotal of
a number in this cell cancels info from col F going into col M
M2=SUM(F2) IF I2 is 0
M3=SUM(M2+F3) IF I3 is 0

I received good help yesterday with similiar problem in this thread
My problem yesterday was resolution was resolved by Garf13LD
K2=SUM(F2-I2) IF I2 is 0 do nothing K2=if(i2,sum(f2-i2),0)
K3=SUM(F3-I3) IF I3 is 0 do nothing K3=if(i3,sum(f3-i3),0)

I tried uploading spreadsheet but not happening.
 

Answer:Solved: IF function simple problem

I wont merge posts
but its pretty much opposite to the formula from the last post

but I have added the test in full - so you can see what happens

M2=SUM(F2) IF I2 is 0 = IF(i2=0,f2,0)
M3=SUM(M2+F3) IF I3 is 0 = IF(i3=0, (m2+f3), 0)

the if statement works as follows

IF ( the TEST , result if test is true , result if test is false)
 

3 more replies
Relevance 61.09%

I have added a pop up calendar control to my spreadsheet (Excel 2003) - is there a way of limiting the control to be active in certain columns in cetain sheets?
i.e. columns 11 and 12 on sheet 2 and columns 4 and 5 on sheet 4
Currently you can add a date to any field regardless of formatting...
Many thanks
 

Answer:Solved: Excel Calendar Control

8 more replies
Relevance 60.27%

Hey guys, I am quite new to creating macros. I do have some primitive experience with code. My goal is to take 3 or 4 colums of information and put them into Outlook Calendar.

A- Discription B- Start -C Finish

So, in the Calendar in outlook it would show a discription and connect the dates in B and C to the days in the Calendar. I don't know if it can be done. At my current state I can't figure out how to get even one to move over. Any suggestions on where to start??

I am running Outlook and Excel 2003 all updated. On Windows XP Pro Service Pack 2.
 

Answer:Solved: Excel Macro to Outlook Calendar

16 more replies
Relevance 60.27%

Does anyone know how to produce a landscape year calendar in excel. My users have a table in word that they update. Months across the top and days of the week (mon tue etc) down the left hand side. Each month's numbering starts on the first with the first being in the block that corresponds to the month and day of week for that month. Seems so simple. I want to put it into excel and access what they write for the specific days and use it to create folders. eg. Jan 1 20114 is a Wed. March 3, is a Monday and has APC planning sessions. I want to create a folder on a predefined path /APC/3 March 2014. Sounds easy - but I can't even create this calendar! Can anyone advise? [ps doesn't copy nice, have used ... to space out]
(columns) Jan Feb Mar etc
Sun
Mon
Tue
Wed........ 1
Thu ........ 2
Fri .......... 3
Sat.......... 4.. 1... 1
Sun ..........5.. 2.. 2
Mon .........6.. 3... 3 APC
Tue ..........7.. 4... 4
Wed......... 8.. 5... 5
Thu ..........9.. 6... 6
Fri (etc .... 10. 7... 7
 

Answer:Solved: A4 landscape year calendar in Excel

11 more replies
Relevance 60.27%

For some reason there is a problem with this programme I have built (with help from Computerman) now I have transferred it to another excel file.

When I try to set a Callback Reminder (my terminology), once I have entered the details an error comes up for some reason. The programme has been tried and tested by itself and worked brilliantly but for some reason it doesn't now.

Please use password lfc1979 on the login screen
 

Answer:Solved: Excel outlook calendar reminder

9 more replies
Relevance 60.27%

OK...question regarding Excel Calendar Control...

I have been fumbling around with it and I'm getting frustrated...likely because I don't have much experience using controls...

What I want is for the user to see a pop-up calendar when clicking on certain cells. After choosing the date from the pop-calendar, it enters the date in the cell in the format that I want.

Any thoughts?

TBaker14
 

Answer:Solved: Excel 2007 Calendar Control

6 more replies
Relevance 60.27%

An IF function showing, for example, 60% or greater 'Pass' or 50% or less 'Fail'. How would you include that anyone getting between 50 and 60% will get a 'supp'? Thank you

Jena
 

Answer:Solved: Excel if function

you need to embed another level of IF

so

=IF( cell >= 0.6 , "Pass", if( cell >= 0.5 , "supp", "Fail"))

Not tested - Cell = the cell you are testing ie A1

and I have put as fractions assuming that the cell is formatted as % so the actual number is less that 1

also using greater and equal to so
if it equals 60% or higher
 

3 more replies
Relevance 60.27%

what function do i use to compare two columns in excel and get the difference in a third. for example, column 1 had account numbers for unpaid bills, column 2 is an updated column a week later. how can I display a third column showing the account numbers that have been settled?
 

Answer:Solved: excel function

14 more replies
Relevance 60.27%

Hi All,

I need a function that will create line numbers depending on if anything is input in that row. I am doing up an invoice, and don't want to type in the reference numbers, and also do not want to leave static numbers in. If I have 5 colums (Item, Description, Units, Unit Cost, Total Cost) and if I enter any data in the Description colum, then I want the Item colum to display sequentially line item numbers.

thanx for the help.
 

Answer:(Solved) Excel function

A1: Item #................B1: Descr
A2: =if(b2>0,1,"")........B2:
A3: =if(b3>0,a2+1,"").....B3:

Copy A3 down the rest of the way.

Just email me for a real sample, Ran.

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

[This message has been edited by Dreamboat (edited 10-17-2000).]
 

3 more replies
Relevance 60.27%

I need to pop a function in to a spreadsheet but I'm struggling, any help would be appreciated.

What I'm after is;

If G269 is between 0.251 and 1.00
Value if true = K269
Value if false = 0 (zero)

I've tried the following function that I thought would work but apparantly not;

=IF(G269>="0.251"<="1.00",K269,0)

Thank you
 

Answer:Solved: Excel IF function

=IF(AND(G269>=0.251,G269<=1),K269,0))

That should work for you.

Also, if you put numbers inside "" then excel will think they are strings
 

3 more replies
Relevance 60.27%

Hello...to whomever may be able to assist:

I'm attempting to create a spreadsheet that fills in the required data from the first tab into the appropriate fields on the second tab. The function may be simple but my brain isn't clicking on all cylinders. Here's the specifics on what I'm attempting in the attached file.

Data tab has all the required information and the trigger that is needed for completing fields on Yes Only tab.

If there is a Yes in column P on the Data tab, I want the fields required in the Yes Only tab to automatically (using a function of course) populate. The trick is that I only want it to populate the required information for the Yes's (in column P on Data tab).

Any assistance or guidance would be helpful in conquering this. Thanks in advance.
 

Answer:Solved: Excel if, then function

12 more replies
Relevance 59.86%

wife got a job as a courier, want to track miles travelled, deliveries made, etc et al ad nauseum. Only quirk in the sheet is that I want to track her mileage as well, and then only on the days that she fills up. I want this to be as simple as possible, so that all she has to do is plug in the ending mileage travelled (beginning mileage will default to the ending of the previous day), and the mpg is something I just want to track for the heck of it. We need to track the gas for tax purposes regardless, so I am going to have access to the gallons via receipts, but the kicker is that, again, I only want the cell to activate when something is entered into the gallons column.

Give me a few to work out a template and I'll attach it, but I hope that's clear enough.

thanks,

v
 

Answer:Solved: should be simple excel stuff

16 more replies
Relevance 59.86%

I have an average on the product's i sell, ill loose 20% percent of the items. for an example: if i order 100 dolls 20 of it will not sell...

As much as i tried and as many people i asked i couldn't find a way to make an excel sheet to tell me the simple calculation of B2 should be 20% less than A2

I thought ill be able to find someone out there to help me.

With great appreciation.

Tzme
 

Answer:Solved: Simple Minus % - Can excel do it?

6 more replies
Relevance 59.86%

I'm sure this is a simple task for you boffins out there.

In Col A, I have a list consisting of a series of number ie
24,56,23,98,34,77,01
65,98,12,17,36,99,31
etc
Unfortunately, these lists of numbers are taken from several different sources and some contain spaces ie
24,56,23,98,34, 77,01
65,98, 12,17,36, 99,31
etc
I just need a macro, which I can then assign to a toolbar button, which eliminates these spaces.
All the list is contained to Col A and the number of rows varies from, say, 18 rows up to possibly 25.000 rows.

I'd greatly appreciate any help with this please.
 

Answer:Solved: Excel - Need simple macro

6 more replies
Relevance 59.86%

Got a table with the binary representations across the top (see attached). What I want to do is if I enter a 1 in a cell under a certain number's column, then have that number populate that cell. If a zero is entered, I don't want anything populated. I would imagine it's a simple if/then formula, but we'll see.

tia,

v
 

Answer:Solved: simple excel question

got it.
 

1 more replies
Relevance 59.86%

I am looking for the best way to have emails sent out periodically based on information within a simple spreadsheet.

I work for an airline, and receive a daily audit that tells me when certain passengers flying from cities X, Y, and Z meet certain criteria. For instance a simple example would be:

Passenger Smith From Boston No Luggage
Passenger Jones From Tampa Luggage
Passenger White From Tampa No Luggage
Passenger Walker From Denver Luggage
Passenger Evans From Tampa No Luggage

Each day the names, cities will change. For this example, I would like to send a standard email to each of my city managers ONLY if their city appears on the list that day AND column C = "no Luggage".

I don't want each manager to get the whole list, but only the rows that originate from their city and only those that meet the criteria for that day. Also, how do I tell excel/outlook that if the City = Boston use email address [email protected] and if City = Tampa use email address [email protected]

I'm pretty good with the basics of excel, but not with macros (which I think may be needed?)... any advice or direction would be appreciated in solving this (hopefully) fairly simple issue!
 

Answer:Solved: Simple Email From Excel?

16 more replies
Relevance 59.86%

Hello,
many thanks in advance for taking the time to have a look and any help any can offer. Basically I am trying in an excel spreadsheet to get from...
Code:

Section
Heaven Sword & Dragon Sabre
9781588991836
9781588991850
9781588991867
9781588991874
9781588992413
9781588992420
9781588992437
9781588992444
Section
Hellgirl
9780345497475
9780345504166
9780345504173
9780345504180
9780345506696
9780345508454
9780345512208
Section
etcetera...

to
Code:

Section
Heaven Sword & Dragon Sabre
Heaven Sword & Dragon Sabre
Heaven Sword & Dragon Sabre
Heaven Sword & Dragon Sabre
Heaven Sword & Dragon Sabre
Heaven Sword & Dragon Sabre
Heaven Sword & Dragon Sabre
Heaven Sword & Dragon Sabre
Heaven Sword & Dragon Sabre
Section
Hellgirl
Hellgirl
Hellgirl
Hellgirl
Hellgirl
Hellgirl
Hellgirl
Hellgirl
Section
etcetera...

I suspect this is probably not a massively difficult thing to do, but sadly it does fall just beyond the scope of my immediate excel knowledge (and what I can usually puzzle out myself from a quick bit of rooting around on the internet).

Basically I'm trying to manipulate my ecommerce data for import into something else which requires me to have an extra category variable per product. I have obviously copied the current column in my spreadsheet to retain the current information, this is an extra requirement.

It is always laid out in the Section, then the variable name I want to rep... Read more

Answer:Solved: Probably a very simple excel question...

If the values are in column A, you could use:

=IF(A2="Section","Section",IF(ISTEXT(A2),A2,B1))

in B2, then copy it down. And then copy > paste special (values) the results. Five minutes by hand, prolly.

(B1 = "Section" to start off)
 

3 more replies
Relevance 59.86%

emphasis on should. made a chart, got some random data over some random dates, for some reason excel adds the dates in between the random dates, on what it thinks is the logical pattern. Stupid program. Stupider user can't figure out why it is doing this.

For instance:
Column A has dates
7.24.05, 7.28.05, 8.22.05, 8.27.05, 8.31.05, 9.1.05, ,9.2.05, 9.7.05, and
9.9.05

But excel insists on filling in every other day from 7.30 to 8.22. I've seen this before, only in office xp, but was never annoyed enough to figure it out. Now I've found I can't, and as such, am here.

tia,

valis
 

Answer:Solved: should be a simple excel question

10 more replies
Relevance 59.45%

I am trying to figure if there is a way to average the numbers that are showing in blue in the attached worksheet. I really don't know how to use the AVERAGE function if all the numbers aren't connected. I am using Office 2003 if that helps any.

Thanks!
 

Answer:Solved: Average function in Excel

6 more replies
Relevance 59.45%

Hello guys,

I need to urgently set up a vlookup function for a very simple data. I was reading many tutorials on internet but I did not manage to create one that fits my problem. If anyone could set it up for me so I understand how the function should be, I would really appreciate. Thank You.

The Excel data I have is: Names, Grades (numbers), Grades (Letters). And then just a little table showing the values of the grades according to the letters. You will see on the attached file.

Its really simple, just have a look at it. Thx!!
 

Answer:Solved: EXCEL - vlookup function

I think the key was to append a new line to the number-letter table (0 = F).
See attachment.
 

2 more replies
Relevance 59.45%

I am using Office 2007. I want to use a formula to auto calc data from other fields.

I have a sheet with results from races. The sheet has the following fields:
Finished
Grade
Track
Dist
Time

I am looking for a formula or function that will select the "1st" from the finished field, filter the grade to select a single grade (eg 4th), select a single track, select a single distance and then return the average of all the winning times that meet those other criteria.

Is there an excel function that will do this and if so could someone help me with an example of the formula to be used...........many thanks
 

Answer:Solved: excel database function

16 more replies
Relevance 59.45%

Sorry about freeky subject, I wasn't sure about my nick and paswd

Ok here is the question and it's EXCEL related. I have a long huge spreadsheet with calculated per unit cost per invoice. On monthly basis I would like to calculate the average per unit cost by using the formula =average(a0:a660). I entered that formula but it's calculating the wrong average. IT's including the ROWS which have zero balances. For example if I have only three item in one column it will still devide the total to all 660 ROWS rather then only to three existing numbers.

Any idea why? Any help will be appreciated.
[This message has been edited by Shabby (edited 12-06-2000).]
 

Answer:{Solved} Excel Average Function

6 more replies
Relevance 59.45%

Hi folks. I'm kinda hoping that someone can help me get a raise

I've been putting together some Excel sheets at work for gathering and processing various statistics (HEAVY use of the IF / COUNTIF functions!).

I was wondering if there is a way to pick which colour is used depending on certain results.... eg

A£ - =IF(A1>A2"YES",))

Obviously, if A1=2 and A2=1, then A3=YES (sounds like BBC Basic to me!).

What I would like to do is also use the result to determine the text colour, so that a 'TRUE' result would appear red, and a 'FALSE' result would appear black.

Any hints / tips would be very welcome.

Thanks
 

Answer:Solved. Excel Function/Format

6 more replies
Relevance 59.45%

I have 2 columns of data. How do I manipulate the lookup function to add together all of the monies against that letter so that I have a total for A, B, C etc (see attached)?
 

Answer:Solved: LOOKUP function in excel

LOOKUP isn't appropriate in this case. Use the following in B17:

=SUMIF($A$1:$A$12,A17,$B$1:$B$12)

, then copy to B18:B19.
 

2 more replies
Relevance 59.45%

So thank you all for at least looking at my question.

I have a command button that when clicked runs the following

Private Sub commandbutton1_click()

Sheets("Printout").Visible = True
Sheets("Printout").PrintOut
Sheets("Printout").Visible = False

This works great it unhides a sheet named "Printout" then prints and rehides. Using the default printer selected for that machine.

So the problem has been brought to my attention that the end user wants the ability to select the printer before printing. So I thought it was an easy fix. (Maybe it is and I don&#8217;t know it). What I did was the following
Private Sub commandbutton1_click()

Sheets("Printout").Visible = True
Sheets("Printout").Application.Dialogs(xlDialogPrinterSetup).Show
Sheets("Printout").Visible = False

This now shows the available printers but I never get a printout.

Could someone please help!

Thank you so very much,

A.J.

Oh....HI BOMB!!!
 

Answer:Solved: Print Function using VB via Excel

7 more replies
Relevance 59.45%

Hi all, found this forum via internet search for a question I know, but right now I'm having a brain freeze.

I can't rememeber the "IF" function that returns a blank cell when my answer is 0 vs seeing "3DIV/0!" in the cell.

Here's my formula: =A8-B8/C8

Thanks!
 

Answer:Solved: Another Excel IF function question

14 more replies
Relevance 59.45%

Dear All,

I have written a nested IF statement in Excel 2010 that is not working quite how I'd like it to. I am wondering if anyoone can help?

SEV and OCC are whole integers between 1 and 10.

In the CLASS column: "CC" should return when SEV>= 8;
"SC" should return when 7>=SEV>=5 AND 10>=OCC>=4;
anything else should return nothing

I have managed to create the return situations "CC" and "SC" in rows 2 & 3 (in the picture above), with the following formula:

A2:
=IF(B2>=8,"CC",(IF(AND(7>=B2>=5,10>=C2>=4),"SC",(IF(OR(B2<5,C2<4),"")))))

The bold blue part of the formula was meant to cover the return situation of rows 4, 5 and 6. But instead of returning nothing, it's returning "SC". Can anyone suggest the changes I need to make?
 

Answer:Solved: Nested IF Function Help Excel - Please :)

This should work
=IF(B2>=8,"CC",IF(AND(B2<=7,B2>=5,C2<=10,C2>=4),"SC",""))
 

2 more replies
Relevance 59.45%

Hi, I'm wondering if the following is a possibility?

Essentially what I need to do is have a formula in cell AY that says

IF A2="Salary" then use the formula currently in AY which is: =(IF(AV60<>"",AV60,AO60)) , if A2 isn't salary than use the value in AX

My question is, is it somehow possible to perform a function in AY only if A2 = Salary?
Thanks for your help guys!
 

Answer:Solved: Excel - IF function with formula rather than value?

6 more replies
Relevance 59.45%

I'm not sure Excel is even set up to do this. Basically I have a chart for work that tracks an agency's monthly spending. We have a column for total contract allotment, and a column summing their actual expenditures. I want to know if we can set it up to have Excel automatically shade the cell red when the actuals are greater than 20% of the allotment. I hope this makes sense and/or is possible!

Thanks for your help!
 

Answer:Solved: Excel Formula/Function

the cell would be easy using conditional formating - changing a graph / chart will be different

lets have some dummy data loaded on a spreadsheet and we can set it up and then post how to do it

what version of excel

otherwise look up conditional formatting

this shows using a %
http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/aa93f3232468b8a9

this may help also
http://www.cpearson.com/excel/cformatting.htm
http://www.wikihow.com/Apply-Conditional-Formatting-in-Excel
 

3 more replies
Relevance 59.45%

I am trying to use the ROUND Function within Excel. I have the funtion to show one decimal place. However, if there is a number like 5.0, the cell only displays a 5. How can I get the cell to display 5.0?
 

Answer:Solved: Excel - ROUND Function

12 more replies
Relevance 59.45%

Hi,

I'm trying to create a function that will return text if the value of one cell equals a certain text.

Ex:
IF I2 = Instant Rebate
Then L2 = After Instant Rebate
If I2 = Mail-in Rebate
Then L2 = After Mail-in Rebate
If I2 = Blank
Then L2 = Blank

Here are the formulas I've tried that are returning an error:
=IF(OR(I3=”Instant Rebate”,I3=”Mail-in Rebate”),”After Instant Rebate”,”After Mail-in Rebate”)
=IF(OR(OR(I3=”Instant Rebate”,”After Instant Rebate”,(I3="Mail-in Rebate",”After Mail-in Rebate”))))

Plus neither of my formulas has the if i2 = blank then it should return blank.

Any ideas?
 

Answer:Solved: IF/OR Function for Excel 2007

Got it!

=IF(I3="Instant Rebate","After Instant Rebate",IF(I3="Mail-in Rebate","After Mail-in Rebate",IF(I3="","")))
 

1 more replies
Relevance 59.45%

Hi all,
Using Excel 2010 and Acrobat X.

I have a spreadsheet with a list of names in one column and email addresses in another column. I want to combine them so that the name is displayed and the email address is a hyperlink. I can do this easily by using =hyperlink("mailto:"&a1,b1). The problem is that I then need to convert the document to PDF. The conversion will keep manual links, but not links created with the hyperlink function. I've seen a couple of suggestions such as saving the spreadsheet to HTML and then converting to PDF or a rather ingenious solution to copy the contents of the worksheet and then use Acrobat X to create a PDF from the clipboard. Both of those work, but they lose things like the borders around the cells and the repeating rows at the top of each page.

Any ideas? Thanks.
 

Answer:Solved: Excel Hyperlink Function to PDF

This should be the links as per Microsoft will create.
Not sure if this is what you are looking for.
 

2 more replies
Relevance 59.45%

I need to use a median function on about 2000 records, which are separate by name. For example
Name Units Median
ADAMS, SALLY 1
ADAMS, SALLY 2
ADAMS, SALLY 4 2.5
BROWN, BONNIE 2
BROWN, BONNIE 3
BROWN, BONNIE 5
BROWN, BONNIE 7 4.5

So i need a median function at the end of each name (in a column to the right)
So for Sally Adams her median would be 2.5
I don't want to put this forumula in manually at the end of every name, I want a macro or formula to do this for me. Just not sure how to do this. The logic would be if name = name, then add the units and figure median.
Any help would be appreciated

[email protected]
 

Answer:Solved: Excel median function

10 more replies
Relevance 59.45%

I am trying to combine the LEN and LEFT functions. However, I am unable to get the formula to work correctly.

A1 - A5 Cell Entries

5 -100
3 - 45
10 - 40
15 - 100

Below is the code I have been trying to get to work in cell B1
Code:

=IF(A1="","",IF(A1="1", A1,IF(LEN(LEFT(A1))=2,"Yes","No")))

I need to test the number of charaters before the hyphen, and then the number of characters after the hyphen.
 

Answer:Solved: Excel -> LEN & LEFT Function

16 more replies
Relevance 59.45%

=RANK(R26,$R$6:$R$48,0)

I have used the above function to display rank of students over the years but it only displays only an integer. Now i need to DISPLAY RANKS AS 1st, 2nd, 3rd .... 40th.

i have taught this function, with some difficulty to other novice teacher excel users and will need to teach the new function to them as well.

Please find attached the .xls file and advise.

Appreciate some very early responses.
 

Answer:Solved: MS Excel 2007 function

8 more replies
Relevance 59.45%

Can someone help me figure out what I'm doing wrong.
I'm trying to autofill a column with a formula but it doesn't seem to be working right.

I have a column of numbers, cells A1=1, A2=1, A3=1, etc. (so the A column is all 1)
Then column B is various numbers, say... B1=1, B2=2, B3=3
These two columns are 20 rows. In cell B21 I have the total of all of column B
I want to put a formula in column D like =SUM(A1)-B21
Then I want to autofill the rest of column D with the same formula, changing (A1) with (A2) and so on as the column autofills. The problem I'm having is that it is also changing the B21, which I want to remain B21 for the entire column.

I hope this is a clear explanation of what I'm trying to do. Can someone help me figure this out. If you need more clarity, let me know. Thanks!
 

Answer:Solved: Simple Excel problem, I'm just overthinking, i'm sure...

Replace B21 with $B$21. You are using relative addressing where you need an absolute address
 

2 more replies
Relevance 59.45%

Hi,
I have a spreadsheet with 2 columns: First Name, Last Name. All I need to do is have First Name Last Name together in one column.

I tried selecting both columns and then use the Merge and Centre option, but this gives message that merging into one cell will keep left most data only (First Name only).

I can't figure out this 'simple' task. Can anyone help me out?
 

Answer:Solved: Simple Excel Merging Question

try using concatenate ( & ) in a new column

= A1 & B1

You can add a space between by doing this

= A1 & " " & B1

Then select the column
do a COPY of the whole column
Now
Paste Special
Past back the text
Now you just have the names in the column and can delete the other two rows
 

3 more replies
Relevance 59.45%

OK, I know this must be one of the simplest formulas to ask, but what formula do I use in cell J49, to find 8% of the total of H49. My mind has gone blank.

Doh! its =SUM(H49/10)
 

Answer:Solved: Simple Excel formula needed

not sure that works

8%
=8/100

to find 8% of a value then its

value * (8/100)
 

1 more replies
Relevance 59.45%

G'day Magicians

I am an incompetent Excel macro creator and would appreciate some help.

I have a spreadsheet column with text in it
I want to move every second row two cells right and one row up
I want to delete every third row

Should be easy but I've just wasted two hours trying to do it can someone please help me tonight?

Forever grateful

Mike.
 

Answer:Solved: Simple Excel Macro Required

16 more replies
Relevance 59.45%

OK, I'm annoyed with myself, cuz I've done this a few years ago for my old company, and want to recreate it for my new one, but can't get my head around it anymore. Should be quite simple for anyone with a bit of working experience with Excel.

In it's most simple form, the table lists stock movements of sample products.
The two main columns in question are:
Serial number: simple number
Movement date: date format
(New location: Text (client's name) only important in the context of information needed, but not vital to the formula)

I want to add a column which will indicate which of the movments of EACH sample was the last one. Then I can filter out those movements which are not the last one, and know where all my individual sample products are.

Logically, I understand that it has to look up all rows with the same serial number, within that group find the record with the greatest value in movement date, and output a certain value indicating it's 'true', but I can't find a formula or combination that'll do it.

Any help would be greatly appreciated
 

Answer:Solved: Simple Excel formula doing my head in...

16 more replies
Relevance 59.45%

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
 

Answer:Solved: Excel Simple Counting Problem

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

3 more replies
Relevance 58.63%

Hi guys, Please I would like to extract only strings starting with C:\Documents and settings...... from the following data in excel worksheet. Any suggestions will be highly appreciated.

C:\Documents and Settings\Joy Okonkwo\thesis_projects\weka\src\main\java\weka\attributeSelection\GreedyStepwise.java(498) C:\Documents and Settings\Joy Okonkwo\thesis_projects\weka\src\main\java\weka\attributeSelection\GeneticSearch.java(703) StringBuffer FString = new StringBuffer(); boolean didPrint; if (m_starting == null) { return getStartSet(); for (int i = 0; i < m_starting.length; i++) { didPrint = false; if ((m_hasClass == false) ||
if (i == (m_starting.length - 1)) {
else {

return FString.toString(); public String toString() {
C:\Documents and Settings\Joy Okonkwo\thesis_projects\weka\src\main\java\weka\attributeSelection\InfoGainAttributeEval.java(22) C:\Documents and Settings\Joy Okonkwo\thesis_projects\weka\src\main\java\weka\attributeSelection\ChiSquaredAttributeEval.java(22) package weka.attributeSelection; import weka.core.Capabilities; import weka.core.ContingencyTables; import weka.core.Instance; import weka.core.Instances; import weka.core.Option; import weka.core.OptionHandler; import weka.core.RevisionUtils; import weka.core.Utils; import weka.core.Capabilities.Capability; import weka.filters.Filter; import weka.filters.supervised.attribute.Discretize; import weka.filters.unsupervised.attribute.NumericToBinary; import java.util.Enumeration; import java.util.Vect... Read more

Answer:Solved: find and copy function in excel

16 more replies
Relevance 58.63%

I have a user who has entered a list of names in a cell and used Alt+Enter to make a hard return inside the cell. Now that user wants to find and replace the hard returns with semicolons. For the life of me, I can't find how to search for a hard return within a cell. Anybody know how to do that? She waiting, so any help would be ENORMOUSLY appreciated!
 

Answer:Solved: Excel 2003-Find function

I figured it out myself! I was pressing Alt+Enter but wasn't seeing anything in the window. When I clicked the drop-down arrow, I saw the square symbol indicating the function I had entered. Whew!
 

1 more replies
Relevance 58.63%

Hi Guys,

I'm using the average function in excel however its returning #DIV/0! errors because of blank cells. Is there anyway to correct this? Part of the problem is that my cells are not consecutive so every fix I've tried is telling me i have to many arguments for this function.

=AVERAGE(C7,E7,G7,I7,K7,M7,O7,Q7,S7)

Thanks
David
 

Answer:Solved: Excel 2010: Average function = #div/0!

6 more replies
Relevance 58.63%

I've written a number of user defined functions in VBA for Excel 2003 and they seem to be working fine. The issue I'm having is that each function requires a number of parameters and I would the user to see the parameter options as they're typing in the formula just like with the built-in functions in Excel. For the life of me I haven't been able to find out if that's an option.
 

Answer:Solved: Custom Function in Excel 2003

Hi there, welcome to the board!

Nope, we can't duplicate this functionality. The best you'll be able to get is to emulate what other functions show in the formula wizard, i.e. the fill in options with the refedit boxes, and to do that you can either use a higher programming language like Visual Studio (i.e. VB.NET, C#, etc) to create an xll, or Laurent Longre has options on his website with a free download...

http://xcell05.free.fr/english/

HTH
 

2 more replies
Relevance 58.63%

Hi. I have a simple formula:

=AVERAGE(I25,K25,M25,O25,AF25,AH25,AJ25,AL25,BB25,BD25,BF25,BH25)

How do I re-write the formula to ignore the empty cells that return a zero value?
All I want to average are the cells that have a number. If I delete the zero's in the cells that don't have a number the fomula returns a "#DIV/0!, which another formula picks up, therefore I have to keep the zero value and not delete them.

Thanks!
Bob
 

Answer:Solved: Excel Formula, Average Function...

16 more replies
Relevance 58.63%

Hi techsupportguys,

I have been trying to create a variant of the PRODUCT function (multiplies all numbers given as arguments) in Excel 2007, so that I "add 1" to each value before multiplying.

So the formula would be Product(x+1) for a series of values.

e.g. (0.32 + 1)*(0.44 + 1)*(1.22 + 1)* ...
I have not been able to do this as I am new to creating macros - but I imagine it is really simple to do - I just don't know how and it is driving me crazy! It would be really great to create a simple function that does this (or even find a way of editing the PRODUCT function in VBA)

Could someone please show me how it is done?

Many thanks,

Edoardo

P.S. I know the roundabout way of doing this is to create a new column that adds 1 to all the values, and then calculate the PRODUCT, but it would be really nice to create a new function that does it.
 

Answer:Solved: (Excel '07 VBA) Trying to create a custom function

16 more replies
Relevance 58.63%

Hi Guys,

The function itsslef is no problem but what I would like to achieve is the following:

I have the following formula which works perfectly:
Code:

=SUMIFS(Computación!$BB:$BB;Computación!$BK:$BK;Tabla!C$8)

What I would like to achieve is that the sheet which is named Computación! be variable.

I have a dropdown validation list in cell C6 which has a list of let's say 10 sheet names

So when I select another sheetname I would like the reference that now says 'Computación' be the value whihc I select in the dropdown list

How would I do this?

I tried [$C$6] I named the cell C6 and tried that but I only get an error that the formula is incorrect

I hope I've been able to explain myself a little
 

Answer:Solved: Excel 2010 Need help with a SUMIFS function

I found it, I had tried INDIRECT but forgot something.

The forumla is now
Code:

=SUMIFS(INDIRECT("'" & SECTOR & "'!$BB:$BB");INDIRECT("'" & SECTOR & "'!$BK:$BK");Tabla!C$8)

And it works
I'm closing this and marking it as solved.
 

1 more replies
Relevance 58.63%

I'm trying to get Excel to calculate percentages according to a table's row values.

I created a function in a macro, however when I change a value in a row, the function gets called for each row causing all rows to have the same value...

My function:

Public Function GetPercentage(cur As Single, max As Single) As Single
GetPercentage = (cur - max) / max
End Function
The cell I'm trying to assign the value to:

=GetPercentage([newPrice],[curPrice])
I've tried passing in column references/cell references and then using the ActiveCell.Row and Selection.Row for the calculation, and endless googling to no avail. Any ideas?

I'm using Excel 2007 under Windows XP.
Any help would be greatly appreciated.

P.S. If someone with good knowledge of Excel VBA knows that this can't be achieved, a reply stating so would also be very helpful, so at least I'm aware and can look for a different solution.

Thanks.
 

Answer:Solved: Excel calling macro function for each row

Seems to work perfectly for me; and I can't see why it wouldn't.

See, if the attached workbook works, if not then it'll probably be some setting in Excel VBE.
 

3 more replies
Relevance 58.63%

Is it possible to use the AVERAGE Function with cells that obtain their values by a formula? I guess what I am asking can the AVERAGE Function be sued with cells that contain formulas?
 

Answer:Solved: Excel Formula -> Average Function

6 more replies
Relevance 58.63%

On several occassions I have lost the functionality of the keystroke Ctrl Y in Excel. It usually happens when I "fat finger" the Ctrl Y keystroke. Unfortunately I do not know what miss-stroke I am making. All attempts to get the functionality back fail except for creating a new spreadsheet and copying the entire spreadsheet to the new one. Does anyone have any idea as to what is happening and how I can undo whatever I have done to get the functionality back? Ater this happens using the menu to repeat does not work either.

I am using OS XP Pro and Office Pro. I use this keystroke for pasting financial figures from my mutual funds web site to my spreadsheet. I first use Special Paste, Text to begin and there after use Ctrl Y. So being able to use Ctrl Y is a real keystroke saver.
 

Answer:Solved: Lost of Ctrl Y function in Excel

This is what Ctrl Y does
CTRL+Y Repeats the last command or action, if possible.
You may have possibly done another Command or Action and to make it work again you need to do a Paste Special again.

Thanks
Tim
 

3 more replies
Relevance 58.63%

I am trying to automate an Excel spreadsheet to calculate time worked. The sheet is attached and I am only trying to get it done on the first persons time section. They will enter their time in military format. My need is to get it were it will calculate, in the Hours Worked section, their time (minus lunch) in a H:MM format.

My secondary hope is to get it to look at the minutes in the H:MM calculation and compare it to the Minutes field in the 100Minute Clock tab and convert the :MM portion to the column on the far right. I don't know if this is possible or not, but it is my ideal solution. It must look exactly like it does now (no row/column additions or subtotals can be visible). I have automated the ABS Code portion for the most part with the addition of the Formula tab, which I will hide before rollout, but I am stumped on the time totals.
 

Answer:Solved: Help Needed on Excel Time Function

9 more replies
Relevance 58.63%

Hello All,

Thank for taking the time.

I have a simple question.

I am organizing a football pool and I need help with a certain formula.

Players are awarded points for their statistics throughout the football season.

I have a simple formula for that. But......

Players are also awarded bonus points for how many yards they amass in a game.

Example:

QuarterBack Passing Yards per game

300-349 Yards Passing = 5 Points
350-399 Yards Passing = 10 Bonus Points
400-449 Yards Passing = 15 Bonus Points
450-499 Yards Passing = 20 Bonus Points
500 + Yards Passing = 30 Bonus Points

I have set up a spreadsheet that tracks all the players stats on a game by game status.

Is there a formula which allows me to set a value for the number of bonus points allowed depending on the value of a cell or cells?
 

Answer:Solved: Need Excel help with a simple formula for Football Pool

9 more replies
Relevance 58.63%

I have applied conditional formatting to a column of cells to highlight the cell red when the date contained in it is > 180 days. The formula applied to all of the cells is =TODAY()-180. Works perfect, however, there are cells with no values (dates) entered yet and they are also highlighted.

Ideally I'd like the cells with no date in them to remain white until a date is entered and then apply the conditions after a date is entered.

Thanks in advance for any help.
 

Answer:Solved: Excel Conditional Formatting question- simple

16 more replies
Relevance 58.63%

Hi,

We have a spreadsheet that keeps track of when our computer warranties expire. Would like to run a macro using the warranty expired date to create an appointment in Outlook. That part I understand. The problem is that when I go to run the macro again, it creates a duplicate appointment if the appointment already existed. Very frustrating!

This is the code below, any help would be appreciated!!!! Thanks
Sub AddToOutlook()
Dim o As Outlook.Application
Dim ai As Outlook.AppointmentItem
Dim r&, sSubject$, sBody$, dStartTime As Date, dDuration#

For r = 4 To 10

sSubject = Sheet1.Cells(r, 1).Value
sBody = Sheet1.Cells(r, 4).Value
dStartTime = Sheet1.Cells(r, 3).Value
dDuration = 30
Set o = GetObject(, "Outlook.Application")
Set ai = o.CreateItem(olAppointmentItem)
ai.Body = "Service Tag= " & sBody
ai.Subject = "Warranty Expires for Server " & sSubject
ai.Start = dStartTime
ai.Duration = dDuration
ai.Close olSave

Next r
End Sub
 

Answer:Solved: Excel Macro to Update Outlook Calendar Creates Duplicates

6 more replies
Relevance 58.22%

I'm working with a large database and trying to write a countifs function. Each part of the function works independently and some of them work together, but when I try to add them all together, I get a #value error. I've given each column in my database a range name to make copying functions easier.

This function works and gives a correct answer:
=COUNTIFS(Pre_DS,">0.99",Pre_DS,"<2")

This function works and gives a correct answer:
=COUNTIFS(Tx_Type,"cha",Pre_Cyl,"<-1.99",POM_3_UCVA,20)

This function gives me a #value error
=COUNTIFS(Tx_Type,"cha",Pre_Cyl,"<-1.99",POM_3_UCVA,20,Pre_DS,">0.99",Pre_DS,"<2")

Help
 

Answer:Solved: Excel 2007 problem with countifs function

6 more replies
Relevance 58.22%

Hi using Win 7 Excel 2007.

I have a multi-sheet workbook. When I did a "subtotal" on one sheet, Excel created new columns (1,2,3) for those subtotals.

When I copy a row(s) from this sheet to another, Excel insert these new columns on the new sheet.

(1) I just want the data from that row, I don't what the subtotal columns on the new sheet! How do I turn off these subtotal columns?

(2) question: I'm using a MACRO to copy the data row(s) from the first sheet to the second. When I open the Excel,
It has a "security Warning Macros have been disabled." message. What do I need to change to have the Workbook open with Marcos enable? Also does it matter if my Marco is "private" or not? Now it's not private.

Thanks!
 

Answer:Solved: Excel Marco and Subtotal function issues

6 more replies
Relevance 58.22%

Ok, look at screen shot below. These are all individual transactions of a sales file. I want to find out how many of each stock number was sold. Is there any way to group these so that it adds the total sales of each stock number? If not, it there a simple equation to do so? I am a total newbie when it comes to excel.

So I want something to look at that tells me I sold 7 of stock # 111 at a quick glance. And so on for each stock number. Any help is appreciated.

Sorry, but please don't question format or why I'm using excel. It is irrelevant. This is imported from our sales file and used just for a quick reference. I just want to be able to tell total sales with grouping or a equation. Its not that in depth.
 

Answer:Solved: Excel 2002 Simple adding of column with Criteria

How quick is the glance? Select the data, then use Data > Subtotals, with:

At each change in stock number -- use function Sum -- add subtotal to qty. Then you can use the (show level) 2 button below the Name Box.
 

2 more replies
Relevance 58.22%

Hello,
I'm finally getting near to the end of my mission to get my trading website online but I have one nutty task left to crack with my data, and I could do with a bit of help please.

I have about 6000 products (graphic novels - I run a comic shop) and I have painstakingly put together my related product info in a spreadsheet laid out as in the 'current layout' below. For most of the products there are 4 related products (0,1,2,3) but for some there are only 2 (0,1) or 3 (0,1,2). The 0,1,2,3 simply pertains to the order from left to right the related products are shown on the site.

What I need to do is get them into the 'required layout' shown below so I can just cut and paste them into a specific Access table that already exists in the online trading software.

However, I'm struggling with this as my Excel skills don't really go in macros and loops and whatnot. (I'm pretty chuffed with myself when I use something like concatenate!)

I get the impression this should be reasonably straightforward though, which is very frustrating. Can anyone recommend an available macro or bit of code that does this sort of thing? Or am I just missing some very obvious functionality in excel?

many thanks in advance,
Jonathan
CURRENT LAYOUT (this is meant to be 5 columns - Product Reference, 0, 1, 2, 3 but it doesn't want to tab nicely)

Product Reference 0 1 2 3
9780867196818 9781593073558 9781593075330 9781593077778 9781560979494
978156... Read more

Answer:Solved: Is there an available macro to do the simple rejig of my excel data I require

6 more replies
Relevance 58.22%

I'm trying to write an Excel macro that goes down a column. When it sees an empty cell, it should fill that cell in with the value of the cell that is just above it.

Thanks in advance.
 

Answer:Solved: Simple Excel Macro to Fill empty cells

8 more replies
Relevance 57.81%

Hi,

I need help with a macro. So far the macro is operating as it should , creating MS Outlook 2010 calendar appointments using data from an excel data spreadsheet (data base). The issue I&#8217;m having is that every time the macro is run for a new entry, duplicate calendar appointments are created for the already existing data in the data base. I would like to have the macro do a search to ensure an appointment does not exist before creating one. I have read a few threads with similar problems but have had major issues incorporating the solutions to resolve the issue I&#8217;m having.

I have attached the macro in txt. format for reference.

Thanks for your help.

kkfonty
 

Answer:Solved: Excel macro updating Outlook calendar is creating duplicate appointments.

Hi
You can use this function to check if an appointment exists based on the Start Day/Time

Code:

Public Function CheckAppointment(ByVal argCheckDate As Date) As Boolean

Dim oApp As Outlook.Application
Dim oNameSpace As Outlook.Namespace
Dim oApptItem As Outlook.AppointmentItem
Dim oFolder As Outlook.MAPIFolder
Dim oMeetingoApptItem As Outlook.MeetingItem
Dim oObject As Object

On Error Resume Next
' check if Outlook is running
Set oApp = GetObject("Outlook.Application")
If Err <> 0 Then
'if not running, start it
Set oApp = CreateObject("Outlook.Application")
End If

Set oNameSpace = oApp.GetNamespace("MAPI")
Set oFolder = oNameSpace.GetDefaultFolder(olFolderCalendar)

CheckAppointment = False
For Each oObject In oFolder.Items
If oObject.Class = olAppointment Then
Set oApptItem = oObject
If oApptItem.Start = argCheckDate Then
CheckAppointment = True
End If
End If
Next oObject

Set oApp = Nothing
Set oNameSpace = Nothing
Set oApptItem = Nothing
Set oFolder = Nothing
Set oObject = Nothing

End Function

For this to work you need to have Tools/References/Microsoft Outlook xx Object Library enabled.
Then alter your macro like this

Code:

Private Sub cmdOutApt_Click()
' Create the Outlook session
Set myOutlook = CreateObject("Outlook.Application")
' Start at row 2
r = 2
Do Until Trim(Cells(r, 1).Value) = "&q... Read more

2 more replies
Relevance 57.4%

Creating a To Do list, I would like to insert a non-volatile date as the date this task was assigned. 1/23/06 would be fine. Can I do that using a function? I checked http://www.officearticles.com/excel/index.htm - didn't solve my specific need, but I did learn there the concept of "volatile". Thanks.
 

Answer:Solved: Excel 2000 - non-volitile date function sought

Answering my own question --

Google search on [excel and "non-volitile date"] returned a single hit and answer in another forum(?) Quoted here, but abridged:
[posted on: May 30, 2003 1:02 PM by kevlinbert]
The easist way to enter a non-volitile date is with a quick macro:

Alt F11 to open the vBasic editor
Double click on the Workbook item in left hand window, then paste the following into the window on the right hand side:

Sub Todays_Date()
ActiveCell.Value = Now()
End Sub

Close the editor and return to Excel, then chose Tools/macro/macros... and 'Options' to give it a shortcut.

This will paste the current date (and time) into the active cell."

Then to format to 1/23/06, highlight the column, right click, format cells, category date,
type 3/14/98.
 

2 more replies
Relevance 57.4%

Hi,

I'm trying to use the Excel 2010 LINEST function with two columns of data. One of my columns contains blanks, so I receive an error. Can I run the LINEST function for these columns, but skip the rows that contain a blank?

I appreciate any help or guidance you can give me. Thanks!
 

Answer:Solved: Excel 2010 LINEST Function with Blanks in Column

Ahhh, if if use Slope(), Intercept(), and RSQ(), I get the answers I need without the blanks causing any problems.
 

1 more replies
Relevance 57.4%

Is there a way to use the LEFT Function with Paste Special?
 

Answer:Solved: Excel Macro -> Use LEFT Function with Paste Special

16 more replies
Relevance 57.4%

It's difficult to describe my situation in words, so I'll just show you what I'm trying to do.

I have a table that looks like this:

A, B, C
Name, Hours, OT hours
Jane, 166, 0
Jack, 163, 17
Mike, 168, 0
Dot, 167, 24
Erin, 164, 8
Carmen 158,0

and I want to create a list that will have 2 rows if there are OT hours, and 1 if there are not.

A, B
Jane, 166
Jack, 163
Jack,17
Mike,168
Dot, 167
Dot, 24
Erin, 164
Erin,8
Carmen, 158

I can't use macros or VBA. It has to be formula driven. I can use a helper column or two. The starting table will change depending on who charged hours where. I've been trying to use IF and OFFSET and ROWS to do it, and it works until there's 2 consecutive rows that have OT hours.

Just to clarify, I just need to know how to get column A listed properly elsewhere in the spreadsheet - I can derive Column B using vlookup.

Thanks so much!
 

Answer:Solved: MS Excel: create list based on data in a simple table

2 Formulas - 1 for finding the name, the other for finding the appropriate value.
Note - must start on row 3 or greater.

Names (mine start in cell G3): =IF(G2<>"",IF(G1=G2,INDEX($A$2:$C$7,MATCH(G2,$A$2:$A$7,FALSE)+1,1),IF(VLOOKUP(G2,$A$2:$C$7,3,FALSE)>0,G2,INDEX($A$2:$C$7,MATCH(G2,$A$2:$A$7,FALSE)+1,1))),$A$2)

Hours (mine start in cell H3): =IF(G3=G2,VLOOKUP(G3,$A$2:$C$7,3,FALSE),VLOOKUP(G3,$A$2:$C$7,2,FALSE))
 

3 more replies
Relevance 56.99%

Hello everyone! I have been working with a data set of mine that needed this issue resolved, and I think I got it. I seem to have resolved it in a very roundabout (and still somewhat unstable, I know - but bear with me) fashion, but it works . Let me give you my reasoning.


My goal is to have essentially make a "live" Top 5 chart in Excel, that when data is posted, it automatically makes the decisions about what goes where using logic. HOWEVER, here are my constraints:

No VBA/macros allowed - many people I work with have difficulty with normal PC tasks, much less intimate Excel use - and even LESS VBA and macro usage. Also, I will not be constantly tending this file.

No array equations allowed - Again, array equations are sensitive to Ctrl + Shift + Enter, and there is a chance that they will be turned off of array mode and interpret incorrect data. Also, the nature of the data is large enough to where large amounts of array equations can bog the file itself down. (one of my last gigs, I was using array equations on ~200,000 cells with a single core processor-let's just say it took a little while to calculate)

So the goal was to make these transmutations by using normal Excel logic, that can be readily transferred from computer to computer and still count on it functioning.

I want to post this, to A) help everyone out that may be in the same predicament as me and B) I hope for a little input, to drastically refine it and remove some of the unnecessar... Read more

Answer:Solved - Using Excel 2007 Large()/Small() function without duplicate values!

This solution also functions in Excel 2010 as well, now that I have it.
 

3 more replies
Relevance 56.99%

Hello,
I'm obviously missing some really basic point.
In column A, I have a date of 12/28/2006. In column B, I write the formula =YEAR(A1) and the result is 6/28/1905. Can anyone tell me what my error is?

Thanks!!!
Patricia
 

Answer:Solved: Excel 2003 YEAR Function Not Giving Expected Results

Hi Patricia, welcome to TSG. It sounds like your formula cell is formatted as a date. If you change the format to General, you should see the result change to 2006. Hope that helps.
 

3 more replies
Relevance 56.99%

Hi,

I had to create an excel file for my workplace which is to be accessed by many users and this excel file relies on formulas for quite a bit of calculations that need to be done internally.

Now the problem with excel is that if a user cuts pastes data in any referenced cell, the formula referenceing to that cell tends to throw a #REF error, so thus to prevent users from causing this error I pasted the following code in the ThisWorkbook module of VBA :

Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)

Select Case Application.CutCopyMode
Case Is = False
'do nothing
Case Is = xlCopy
'do nothing
Case Is = xlCut
MsgBox "Please DO NOT Cut and Paste. Use Copy and Paste; then delete the source."
Application.CutCopyMode = False 'clear clipboard and cancel cut
End Select

End Sub

Private Sub Workbook_Activate()
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CellDragAndDrop = True
End Sub

Private Sub Workbook_Deactivate()
Application.CellDragAndDrop = True
End Sub

Private Sub Workbook_Open()
Application.CellDragAndDrop = False
End Sub

Now while the above code achieved the intended goal it created another problem in the form that sometimes when users have to copy data into the workbook from another workbook, the data does not get copied as the paste option remains disabled. (PS. The copy and paste works for the workbook internally).... Read more

Answer:Solved: Disable the cut function in excel to prevent users from messing up formulas !

11 more replies
Relevance 56.58%
Question: a simple calendar

can anyone recommend a simple 'free' calendar/reminder that appears on boot up so I can see upcoming events. Doesn't have to be fancy, but XP compatable.I did have calendarmaker by Brian Curtin, but XP doesn't like it.Cheers.

Answer:a simple calendar

You should have a calender with XP and also if you run yahoo or hotmail there are free calenders and organisers on those. Maybe just look with a bit more care?Regards,Charles

5 more replies
Relevance 56.17%

Hi

I use MS 2010, and in my Excel work book I have a list which works fine for me. But when I send it to others, the cell with the list in does work. - By not working I mean that there is no "arrow" to access the different variables that the list should show.

I assume this is a compatibility issue. Was just wondering if there were any known solution.

Thanks
 

Answer:Solved: List function in data validation not compatible with earlier version of Excel

14 more replies
Relevance 56.17%

I found a simple calendar program but it is still not exactly what I am looking for.  
 
I found and tried Desktopcal, a Desktop Calendar program, which allows showing a month by month calendar, able to let user enter text information to plainly be visible right on each calendar day, but I still find some problems:
Calendar Day numbers font color cannot be changed
Background color for each calendar day must be empty, ability to make it colorless or white
Notes entered on any calendar day maintain a fixed font size and cannot be changed
Border around each calendar day difficult to see - contrast cannot be changed
Printed calendar is no better for visibility
 
I have tried internet search for a simple calendar program but this Desktopcal is the best I have been able to find.  Does ANYBODY know of another better one?  The way I would want to use it is to make a MONTH calendar, put in reminder notes on some calendar day, and print the calendar and post it on a wall.  Desktopcal is just not good enough.   Anyone know of ANYTHING like what I look for ?
(Preferably also want to keep each calendar as a file)

Answer:looking for SIMPLE calendar program

I use the Lightning calendar add-on in Mozilla Thunderbird, and it works well for me.
 
YMMV

3 more replies
Relevance 56.17%

I need a simple program to give me the ability to construct month by month calendars and allow me to enter/edit data for each day. There may be one in XP that I don't know about (I've just transferred from Windows 98 and havn't learned all that's available). I would like to keep the cost as minimal as possible. Thanks for your help.
 

Answer:Simple Calendar Program

9 more replies
Relevance 55.76%

Hi, I've been searching the threads all night trying to find an answer to this and haven't been successful.

I am using Excel 2003. I have two Worksheets in one workbook. The first worksheet is timesheets, the second project name list. We have numbers assigned to each "job" we are doing. The timesheets are being created to log how many hours are spent on each job.

My goal is for the employees not to have to enter the sometimes lengthy job name/description. I want them to be able to put in 05101 under project number - and have it automatically fill in a different cell with the job name.

I've got =IF(G14= "05101", ProjectNameList!B3) - but obviously that only gives me that ONE job # and name...how do I string them together to list if G14= a different number, it will be a different name?

I've tried this =IF(G14= "05101", ProjectNameList!B3)+IF(G14 = "05102",ProjectNameList!B4)+IF(G14 = "05103",ProjectNameList!B5

.... but it doesn't work. HELP Please!

Thank you!
Rebecca
 

Answer:Solved: Solved: Excel IF Function

7 more replies
Relevance 55.35%

How do you put the calendar on the form and then remove it after placing the date into a field in table? I can get the calendar on to the form but cannot get rid of it after updating the table field.
 

More replies
Relevance 54.94%

Hi All,

I'd like to have a simple analog clock and desktop calendar(like vista gadgets) on my desktop.
Windows 8.1 seems to not have much adjustablity in how the preloaded works.
I've seen sites where you can download gadgets to 8.1 but am leary of installing anything in my new computer. I do have an anti virus program running but am cautious. Any advice?

Thanks in advance.

Marc

Answer:I just want a simple analog clock and desktop calendar

Looking for something free or paid ? I use Stardocks ObjectDock has calendar, clock, search docklets you can add. This is mostly use for desktop icons set in a dock, not sure what you can use, but i find it useful to un clutter desktop and there is more customizing then regular desktop can be

5 more replies
Relevance 54.94%

Hello,

Was wondering if anyone might suggest a very simple, uncluttered calendar-scheduler that would reside on my Desktop. Hopefully free.
Would show the calendar for the present month, but clicking on another month and day would bring up a simple screen for some notes.

Would just like a calendar page to pop up for a month I select, and then the ability
to put some notes of a to-do type in a screen for that page.

Really, really simple is what I'm after; truly basic, simple, and uncluttered.
Perhaps it's called a task scheduler that I'm looking for; not sure.

Found many available on the web, but all a bit too much for what I'm after.

Thanks,
Bob

Answer:Simple Calendar/Scheduler Suggestion Redquested ?

Maybe something like Destkop iCalendar Lite

1 more replies
Relevance 54.53%

Hi,I'm creating a letter and would like the user to select a text box or something similar that would trigger a small calendar. From this point user can select day and document would be populated with day/month/year.Many thanks,

Answer:Is it possible to insert calendar function in word

hyperlink to a calendar?johnny

5 more replies
Relevance 54.53%

Inspite of checking the boxes in the "Options" box and making Windows Calendar my default calendar, no pop-up reminders appear when the caledar is not running. Why??

More replies
Relevance 54.12%

I have a formula that has multiple steps and the last step only partially works. The function looks at a list and if there is more than one unique value it returns the word VARIES, if it only has one unique value it needs to return that value, but right now the way I have it written it will only do that if the value is in cell K10, but the value could be in a different cell. Can anyone help me fix this?

=IF(SUM(N(FREQUENCY('TO 10'!$K$10:$K$57,'TO 10'!$K$10:$K$57)>0))>1,"VARIES",'TO 10'!$K$10)
 

Answer:'simple' function (I hope)

ok maybe I am being lazy, I am not clear on what ( to you) constitutes a unique value? If I type two 6's that is only one unique value, if I enter a 3rd value other than 6 then there are 2 unique values... therefore if I enter 1 value or any duplicate of that value I only need to use this formula...
=IF(SUM(N(FREQUENCY('TO 10'!$K$10:$K$57,'TO 10'!$K$10:$K$57)>0))>1,"VARIES",MAX('TO 10'!$K$10:$K$57))

its the same as yours except I added the MAX function and the ending range... be sure to press... CTRL + SHIFT + ENTER as this is an ARRAY formula.
 

1 more replies
Relevance 54.12%

Hi there,

I've been reading loads of forum posted regarding excel macro's, i've been trying for days now to manipulate one of them into my own project with no avail.

this one should be easy and possibly a repeat of another but please would someone be able to shine a light on this one for my i'll be forever endebted

Try a row of data, once complete i want to click a macro button at the side and lock the row that i've just typed.

Kind of like a bit of validation.

here's what i've gotten

Sub Macro2()
'
' Macro2 Macro
'

'
If Range("O4").Value = "Locked" Then
Range("A4:M4").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("O4").Value = "Unlocked"
Range("A5").Select
End If

If Range("O4").Value = "Locked" Then
Range("A4:M4").Select
Selection.Locked = False
Selection.FormulaHidden = True
Range("O4").Value = "Locked"
Range("A5").Select
End If

End Sub
Help?
 

Answer:What should be a simple Macro function?

By default all cells are locked.
Locking and unlocking will not work unless you protect your sheet.
You must also put your macro into sheetchange event, for it to be "auto".
It will be something like this

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim row As Long
Dim col As Integer
Dim curRow As Range

col = Columns("O").Column
If Target.Column = col Then
row = Target.row
Set curRow = Range("A" & row & ":M" & row)
If Target.Value = "Locked" Then
ActiveSheet.Unprotect
curRow.Locked = True
curRow.FormulaHidden = True
'Target.Locked = True ???
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
curRow.Locked = False
curRow.FormulaHidden = False
ActiveSheet.Protect
End If
End If
End Sub

Btw, you might want to unlock column O first before triggering the macro.
 

2 more replies
Relevance 53.3%

Hello my good people, hope you can help me.The IF function works as follows:Cell A1 shows Status either Active or OverdueCell A2 shows end dateCell A3 shows current dateFormula is: "IF(A2>A3,"Active","Overdue")lol, it works!!How can I add to the formula this conditional format:Make "Active" font colour GreenMake "Overdue" font colour RedAny thoughts, suggestions would be much appreciated.

Answer:Simple IF function require conditional addition

Use Formula Is andA2>A3 to set green fontA2<=A3 to set red font.

4 more replies
Relevance 52.07%

I need a calendar program for my computer since I recently upgraded my computer with student office 2007, which doesn't have outlook like the 2003 edition. Can I have any suggestions? I don't want a web based program that needs that internet to function.

thanks
 

Answer:Solved: Need a calendar program (not outlook or gmail calendar)

Thunderbird with Lightning:
http://www.mozilla.org/projects/calendar/lightning/
 

3 more replies
Relevance 52.07%

We have got a problem at work with excel 2007, we share documents over our network so that everyone can get to them when they need to. But, at the min we have a problem where when somebody opens an excel file edit it and then save and close it nobody else (including the user that changed it) can open the file and edit it. they can olny see it in read only mode.

This is getting really annoying and after reading people comments on the web i have found that this is something that is happening to a number of people.

I have read about a setting in Word which is "Copy remotely stored files onto your computer, and update the remote file when saving"

This setting is not listed in Excel's settings, is there a way of making excel work in this way?

All help will be great, this problem is getting really annoying because we have to keep restarting the computers.

thanks

JPLamb
 

Answer:Excel 2007:Function in Word is it in Excel?

Hiya, there are a couple of things you can try to see if they are affecting the issue you are having.

Firstly
Double check the Spreadsheet properties to make sure that they are not becoming readonly when saved. Right Click on on doc in Windows Explorer and click Properties, make sure read only is not ticked.
Secondly check the setting in the area highlighted in xl2, especially the share workbook. It may be that for some reason it thinks someone still has the spreadsheet open.
Thirdly try the option in xl3

Let me know how you get on.
 

2 more replies
Relevance 50.02%

I wanna make sure if a path exist or not? I'm using this code but it's not working:
Code:
If dir("C:\Text Files", vbDirectory) = "" Then
MsgBox "dir worked"
End If
but it lead to a compile error, any idea why?
 

Answer:Solved: dir function error; Expected function or variable

How about this one:
Code:
Sub test()
On Error GoTo NoDir
ChDir "C:\Text Files"
MsgBox "dir exists"
Exit Sub
NoDir:
MsgBox "dir does not exist"
End Sub

Jimmy
 

2 more replies
Relevance 50.02%

Here's the deal - I have a quick shortcut to my C drive on the desktop, so that I can just double-click it and go straight to browsing my files. However, today, the search function decided to make itself the primary function. When I double-click it now, it opens a search window instead of the C: drive.

Can someone tell me how to make it so double-clicking it will, by default, use the "open" function instead of "search?" Thanks in advance!
 

Answer:Solved: Search function has overridden open function in XP

13 more replies
Relevance 49.61%
Question: Excel Calendar

I have a list of events in Excel which take place regularly at specified
monthly intervals. The only factors which determine the actual month an
event is due to occur are the month in which it was first held and the
number of
months from then to the next event and so on.

The number of months, i.e. the frequency of the event, is either 1, 2, 3, 6,
12, or 24-monthly intervals. My rows contain the name of the event, the
month of the initial event and the number of months between events. I need
to produce on demand a list of all the
events which become due in any specified month during, say, the next ten
years.

I thought this looked easy but have produced a very tortuous formula which
doesn't work, also have not had much joy with a lookup arrangement based on
the next ten years! Is there a straightforward method in Excel or Access
which I can try?
 

Answer:Excel Calendar

Hi, B. Welcome to TSG!

I could ask you a bunch of questions back and forth, but if you're willing to send the file, we can probably take care of it pretty quickly.

Willing? All files handled with the strictest of confidence.

[email protected]
 

1 more replies
Relevance 49.2%

hi,
i have a workbook that i use as a diary, i have been using this for a while but have consistent problems of jumping to a specific date.
i have just inseted the activeX calendar control and put it at the top of the worksheet, im wanting to be able to click on a date on the calendar, and upon doing this it highlighting or jumping to the date on my worksheet.
i hava attached a copy of this workbook with just the first few weeks in january. you will probably understand more of what im aiming for when you open the file. basically i am wanting to be able to use the calendar to take you to that specific 'dated cell' in the worksheet.

i have searched and searched the web to no avail, your help will be muchly appreciated

any probs please let me know

thanks d3ell
 

Answer:help with excel activeX calendar

9 more replies