Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Not sure how to fix Results

Hi,

I have a strange question.

I tried to create a chart today that shows the % of transfered amounts across four markets.
Y axis is percentages, X is labeled Transfers and then the data is only percentages. But when I finish creating the chart it appears as if there are two different categories in the X Axis ( for example if there were Transfers and Incomes)

I am not quite sure why is this happening. Could it be because I am using data that is in merged cells ??!?!

There is an attached file showing the end result with the X Axis out of scale.

Does anyone know how to fix this?

Thanks in advance

untitled.JPG

Not sure how this happened, but could use some help.

I am trying do an EDIT > REPLACE in my excel workbook, but in the LOOK IN:
drop down box it only has "Formula" - The "Values" & "Comments" options are
NOT available to pick from. Subsequently, I am not able to do a "replace" in
any of my workbooks.

Any idea what happened to cause this and how to fix it?

While I'm here, I have another question to ask..
Is is possible to change the FIND & REPLACE "Look In:" default to "values"
instead of "formula" in the FIND and REPLACE feature.

Hi,
I have a strange problem that I don't know how to fix. I'm using Excel
2000. Something happened recently and I'm not sure why or how, but in
every spreadsheet I have if one enters a number into a cell the number
will be divided by 100 automatically. This happens in all my
spreadsheets, so I thought Excel might have been corrupted somehow. I
have reloaded Excel, actually "Office 2000", three times now and, also,
I tried to delete any files that I thought might be related to Excel
(before reloading again) but the problem remains. I've looked and tested
all the menu items but I have not come upon a feature that causes the
problem (or solves it). Could it be that every Excel file I have has
been modified somehow so they all have this problem that I don't know
how to get rid of? I'm almost at my wits end with this. I'm using MS
Windows Me.

Any ideas to help would be greatly appreciated!
Al

Im trying to nest a large If Statement and need a little guidance....

=IF((AND(N2=1,F2<=EDATE(A2,-12),””,IF((And(N2=2, F2<=EDATE(A2,-24),””,IF((And(N2=3,F2<=EDATE(A2,-36),””,If((And(F2>=EDATE(A2,-9),F2<=EDATE(A2,-12),”F1”,If((And(F2>=EDATE(A2,-21),F2<=(A2,-24),”F2”,If((And(F2>=EDATE(A2,-33),F2<=(A2,-36),”F3”)))))))

Not sure how many closing parenthises are needed, and not sure if this is evan do-able.

A2 holds a fixed date
F2 is the variable date
N2 is coded to dollar amounts but relate to the date fields as well.

I am wanting the active cell to return "text" based on the EDATE criteria.

Thanks to anyone who can tackle this one.

Hi
I keep getting n/a as my solution when i try this function. I think it's because of my text options but am not sure. Can anyone take a look at the file and help me figuire out what is wrong?? I'm reading how to fix but don't fully understand.
Thank you!!!

Here's an example of what I'm trying to accomplish with a formula:

A B C D E
1 33789 4 26001 28000 1
2 23000 ERROR 28001 30000 2
3 27999 1 30001 32000 3
4 32001 34000 4
5 34001 36000 5

Columns C, D, & E are fixed values. User would input an amount into A1 and then B1 would populate based on the value compared to columns C, D, & E.

What I need to say, but just not sure how is:
IF A1 > C1 BUT < D1 THEN B1=E1 ELSE IF A1 > C2 BUT < D2 THEN B1=E2 ELSE IF A1 > C3 BUT < D3 THEN B1=E3 etc etc etc

I have about 27 rows (of "ranges") that I need to compare that number to - so I figured there has to be an easier way than having a formula that's a mile long...any suggestions??

Thank you SO much!
Michelle

Hopefully someone can give me the answer or point me in the right direction. It's a bit tricky to explain, but here goes - I will try to keep it as simple as possible.

It will be a lot easier to explain if you view the workbook I have attached to this message.

The workbook represents an investment into four funds. The amount to invest is in cell B8 (currently £1,000 - this can be changed at will).

Column A: contains the four fund names. These do not change.

Column B: represents the percentage allocation of the investment to allocate to the particular fund . IE: B2 contains 10%, so 10% of our investment of £1,000 is to be allocated to this fund. B3 contains 20%, so 20% of £1,000 to be allocated, and so on. Note that the total of column B is 100%, just to be sure that all of the £1,000 has been accounted for. For the purpose of this example, these percentages will not change, but we can change these in the future if we decide to change the amount we allocate to each fund. Remember, though, that whatever % allocation we give to each of the four funds, the total across all four will always equal 100%.

Column C: this is the minimum amount that can be invested into each fund. This does not change.

The next five "PARSE" columns are a filter. This will be explained later.

Our main aim:

To invest the £1,000 across the four funds, and split it according to the percentages in column B, subject to the minimum amount.

The twist:

If a particular fund's minimum is not met, the amount that would have been invested into it should be split across the other funds, taking into account their own % allocation.

The filters in columns D to H make this calculation. I will explain later the problem that I am having which needs a solution, because I'm stuck!

FILTER 1 - calculates the amount that will be allocated to the particular fund. IE: for FUND A, the formula is =$B$8*B2 which translates as £1,000 x 10%, which is £100.00.

FILTER 2 - checks to see if the result of FILTER1 is less than the minimum amount in column C. For FUND A, the formula is =IF(D2<C2,0,D2). If true, make the result of FILTER 2 zero. If false, stick with FILTER1's result. Note that FILTER2 has a total, which represents the total of the funds that passed this FILTER2 test. This will be used in FILTER3's calculation:

FILTER 3 - Getting intense now! Checks to see if the result of FILTER2 is not zero. IF it is, keep it as zero. If not, it divides FILTER1's result into FILTER2's TOTAL to get a percentage. This percentage represents the new percentage amount to be allocated to the fund, and is effectively spreading the funds that failed the FILTER2 test across the remaining funds that passed the FILTER2 test. In our example, FUNDS B and C failed the FILTER2 test, because they did not exceed the minimum amount required in column C. For FUND A, the formula is =IF(E2>0,D2/$E$6,0)

FILTER 4 - subtracts FILTER 2's total from FILTER 1's total and applies FILTER 3's percentage to get the new EXTRA amount to invest for this fund. If FILTER3's result was zero, FILTER 4 will stay at zero.

FILTER 5 - Adds the results of FILTER 2 and FILTER 4 together to give the new total amount to invest in the fund.

FILTER 5 therefore contains the amount that needs to be invested into each fund, allowing for any funds that do not meet the minimum amount.

This filter system can probably be simplified, but that's for another time.

Now, on to the problem!

To illustrate this better, change all of the % allocations in column B to 25%. Make sure that the total in B6 is 100%, but leave the minimum amounts in column C alone.

Test 1 - change the total investment amount in B8 to £2,000. Everything works fine, because each fund is to receive £500. The highest minimum amount is FUND D, which is £400.

Test 2 - change the total investment amount in B8 to £1,500. FUND D's minimum is now not reached. £1,500 x 25% = £375, which is less than the minimum required of £400 (column C). The filters then take over and work out how much of FUND D's £375 should be spread over the other funds. As the other three funds each receive 25%, it is spread evenly. Everything fine.

Test 3 - change the total investment amount in B8 to £1,000. This is where we start to have problems. FUND D still doesn't reach it's minimum amount, so that's OK. The problem is with FUND C. On face value, everything looks OK. As £300 is needed for FUND C, but only £250 is available, then it doesn't meet it's minimum. Correct, but is it? What about the £250 we have available from FUND D? If FUND D's £250 is spread over the other three funds, we would have an extra £83.33 available to each fund, making a total of £333.33 available to each fund, thereby exceeding the minimum amount of £300 required for FUND C.

So the problem is this: FILTER1 is only checking the original intended amount for the fund, not what could be available to it.

I can see the problem, I just don't know how to fix it. I expect some elaborate expanding of the FILTER system will be needed. I would say the best method is to work out the order of which fund would fail to meet it's minimum amount first, then second, then third, and so on.

If you wanted a challenge, then this is surely it? Good luck and I will remain in your debt if you can solve this! Thanks everyone.

Hi All,

So I made the Excel to look the way I wanted and just wanted to ask you if you would not mind changing one more thing or PLEASE tell me how to do it. I'm kind of running out of time on it as I need to submit similar document today.

1. Is it possible to be able to have the new entries from the Bucket list paste in both Release 1 and Release 2 spreadsheets below the colored cells onwards depending on the value in column F, whether it is number 1 (then it goes to Release 1 tab) and if it's 2 - it goes to Release 2 tab? In other words, if there is anything new enetered in Bucket list (starting from cell A42), with 1 in column F it would be sent to Release 1 and there it would be pasted from cell A34 downwards in Release 1. If anything new in the bucket list with 2 in column 2, then it would be pasted in Release 2 tab, starting from cell A8 downwards ?
When I do it now, it pastes the rows in the middle of Release 1 and duplicates many times rows in Release 2.

Please look at the macros already in place. It just needs one little fix with the automatic pasting in Release 1 and Release 2 being done from the specified above rows.

Also, please make sure that the entries in Release 1 and 2 do not get duplicated as I run the macro. (the bucket list is going to be created on a regular basis and I would like to see only the new entries added to both Release tabs and at the same time be able to keep track of all the entries in the bucket list. This has been already programmed, but just wanted to make sure that this is not deleted by mistake.

Please help and thank you very much! Spreadsheet attached.

Regards,
lidalik

Hi! I'm working on a project that involves fixing a DBA's mistakes. This DBA must have been especially dense!

What I'm looking at is a list of lessons (Lesson ID, Lesson Date, etc.). There is a column in the sheet called Listed Participants. This field has MULTIPLE participant ID's in it. The DBA should have listed each participant with a lesson ID as a single record, rather than clumping them all together.

I'm at the point now, where I need to separate each Participant ID, insert some rows, and use the lesson ID as the first column and the participant ID as the second column. I'm not sure if I need a lookup. I've already separated the participant ID column into multiple columns (one for each participant ID), using the Text To Columns feature. I have a column that counts the number of participant columns (this number is used to tell the VBA how many rows to insert) I can (through VBA) insert rows in the spreadsheet to accomodate for the new records, but I'm having difficulty telling the system what to copy and where.

Here's what I HAVE:

Lesson ID ------------------------------------------------------ Listed Participants
-----------------------------------------------------------------------------------------------------
2594 ----------------------------------------------------------> 1525, 1425, 4487, 6698
4796 ----------------------------------------------------------> 3468, 1711, 5689

Here's what I NEED

Lesson ID -----------------------------------------------------Participant ID
----------------------------------------------------------------------------------------
2594 --------------------------------------------------------->1525
2594---------------------------------------------------------->1425
2594---------------------------------------------------------->4487
2594---------------------------------------------------------->6698
4796---------------------------------------------------------->3468
4796---------------------------------------------------------->1711
4796---------------------------------------------------------->5689

Now, you ask why I don't just do this manually. I have 4455!!!!!! lessons with multiple participants and need to speed this up.

Any recommendations? Thanks so much! I don't expect code answers (I think I can handle the code), but maybe some insight into how to approach this in a way that doesn't involve throwing the DBA in a lake. Thanks again!

KellyJo

Good Afternoon Everyone and thanks in advance for any help you can give me on this issue! I am by no means an expert at Excel Macros, but I'm doing my best to work through this one.

I have a user that created an Excel form, this form is a protected worksheet with comments sections that were created using merged cells (not my choice). As we all know when a document is protected excel can't automatically expand merged cells to fit the text. I have added a macro that runs when a user changes fields on the form. When the user exits a comments field the macro basically unprotects the document, expands the row with the merged comments cell, and re-protects the document.

So, here's the problem, when filling out the form if the user enters a comment and hits tab the macro runs. In my macro code the comments field will be re-selected automatically when the macro if finished running. I had to re-select the comments field in order to make sure it would be unlocked after the macro finished running. But, after the macro runs if the user then tries to tab to the next field the tabbing order seems to be messed up because it skips a field. For example if fields A1, A2, and A3 were unlocked fields and the macro had just ran on field A1 if the user then hit tab they would be taken to field A3, completely skipping filed A2. I'm at a loss as to what is causing this or how to fix it. Can anyone please help? The macro code is posted below.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$55" Or Target.Address = "$G$65" Or Target.Address = "$G$67" Or
Target.Address = "$G$69" Or Target.Address = "$G$71" Or Target.Address = "$G$73" Or
Target.Address = "$A$83" Or Target.Address = "$E$83" Or Target.Address = "$I$83" Or
Target.Address = "$A$84" Or Target.Address = "$E$84" Or Target.Address = "$I$84" Or
Target.Address = "$A$85" Or Target.Address = "$E$85" Or Target.Address = "$I$85" Or
Target.Address = "$A$88" Then

Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
Dim OrigRwHt

ActiveSheet.Unprotect Password:="hello"

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
OrigRwHt = c.RowHeight
c.EntireRow.AutoFit
If c.RowHeight < OrigRwHt Then
NewRwHt = OrigRwHt
Else
NewRwHt = c.RowHeight
End If
c.ColumnWidth = MrgeWdth

c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With

Range(Target.Address).Select
Selection.Locked = False

ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, Password:="hello"

End If
End Sub
Thanks again for any help anyone can give!

Hi Guys

I need a bit of help with the below macro which I am trying to create

I recorded the below vlookup, which works perfectly. It checks a list on sheet “Map” and returns a value depending on whether the reference is one of the 6 or not. These 6 are likely to change over time so I would prefer to declare them as variables rather than build them directly into the macro

Do
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]=""CBT 10Y SWAP 09/Sep"","".03125"",IF(RC[-2]=""CBT
10Y T-NOTE 09/Sep"","".03125"",IF(RC[-2]=""CBT 2Y T-NOTE
09/Sep"","".03125"",IF(RC[-2]=""CBT 2Y T-NOTE
09/Dec"","".03125"",IF(RC[-2]=""CBT 5Y T-NOTE
09/Sep"","".03125"",IF(RC[-2]=""CBT T-BONDS
09/Sep"","".03125"",""1""))))))"
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -2))
Was thinking of declaring the variables which will always be in C2-C7 as below and substituting each reference in the vlookup. However something is wrong as I am getting a value of 1 for all
Dim strCurr1 As String: strCurr1 = Sheets("Map").Range("C2").Value
Dim strCurr2 As String: strCurr2 = Sheets("Map").Range("C3").Value
Dim strCurr3 As String: strCurr3 = Sheets("Map").Range("C4").Value
Dim strCurr4 As String: strCurr4 = Sheets("Map").Range("C5").Value
Dim strCurr5 As String: strCurr5 = Sheets("Map").Range("C6").Value
Dim strCurr6 As String: strCurr6 = Sheets("Map").Range("C7").Value

    Do
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]=""& strCurr1 &"","".03125"",IF(RC[-2]=""
& strCurr2 &"","".03125"",IF(RC[-2]="" & strCurr3
&"","".03125"",IF(RC[-2]="" & strCurr4
&"","".03125"",IF(RC[-2]="" & strCurr5
&"","".03125"",IF(RC[-2]="" & strCurr6
&"","".03125"",""1""))))))"
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -2))
Can anyone suggest what might be wrong and how I might fix this please?

P.S. "Curr" is probably not the right one to use but am not sure which is

Thanks All

Noel

Hi,

I have a large dataset with 21 columns and 3000 rows, but to keep it simple, the data looks more or less like the first dataset in the attached excel file.

This results in 3 curves (time vs force). However, there is excess data in there, I only need data as soon as the Force becomes positive (>0) untill the Force drops below 0.02 again (I consider that the end-point of my curve). So, this dataset would look like the second set in the excel file after the macro-manipulation.

I think one of the difficulties is making sure that the macro doesn't cut of my data right in the beginning, when the curve first crosses 0.02. In that case, you get the last dataset. And this is not what I want...

I hope I explained my problem well, if not, please let me know.
So, is there anyone who knows how to do this?

By the way, is there anyway to insert small excel tables in this post? Cause I wasn't planning on having an attachment, but if I copy-paste the data in this post, than the formatting gets undone and it looks like there's no way to fix it...

Thanks!
MLC

I wrote a macro that fills out a master process log for a piece of manufacturing equpment that I use and then hyperlinks the master log to the individual process logs. All of this data is saved on a Network shared drive. The macro runs fine, and the hyperlinks address the individual run logs ok, until the workbook is saved. At that point, the hyperlinks change.
They go from:
"///shareddrivefolder1folder2folder3folder4folder5folder6folder7file.xls"
To:
"../../../../../../folder1/folder2/folder3/folder4/folder5/folder6/folder7/file.xls"

If there are any spaces in the folder names, those spaces are replaced with
"%20". I am assuming that this has something to do with how I have my network drives mapped, but I am not sure. Here is a portion of the hyperlink part of my macro:

LotFile = InputBox("Enter the file folder in the ZPS90Process drive that contains the Logs")
MyPath = "Shareddrivefolder1folder2folder3folder4folder5folder6folder7" & LotFile
Before = InputBox("Enter the Row number with the lowest Lot Number to Hyperlink") 'Select the file to start from
After = InputBox("Enter the Row number with the highest Lot Number to Hyperlink") 'Select the file to end with

For I = Before To After 'To select the range of the files to format
SubHyper = "A" & I
HyperFile = Range(SubHyper)
Select Case True
Case HyperFile > 999 'Makes sure the file name requested is the same as the actual file name
LotNumber = "LOT" 'Ex.-Lot0151 vs. Lot151
Case HyperFile > 99 And HyperFile < 1000
LotNumber = "LOT0"
Case HyperFile > 9 And HyperFile < 100
LotNumber = "LOT00"
Case HyperFile > 0 And HyperFile < 10
LotNumber = "LOT000"

End Select

MyFile = LotNumber & HyperFile & ".xls" 'To input the log file name

Range(SubHyper).Select

ActiveSheet.Hyperlinks.Add _
Anchor:=Selection, _
Address:=MyPath & "" & MyFile

Failsafe:

Next I

If there is something that I can add to the program to correct this, or if it is a network addressing issue, please let me know. I would really appreciate some advice on how to fix this problem. If this is a network drive mapping issue, I would assume that fixing it would also fix the issue that I have copying and pasting hyperlinks to other worksheets on other shared drives, and the hyperlinks getting messed up (taking on the beginning of the shared drive address that the worksheet that the hyperlink is getting pasted to instead of it's original designation). Thanks so much again for any assistance.

I am building a spreadsheet at the moment that requires cells to be pasted into the column that matches the date for the month that the report is being compiled for. I have the code to paste the cells, but i do not know how to set up the macro so that it pastes into the correct column. Below is the code as i have it so far...

'initial selection of cells
Range("B7:B120").Select
Selection.Copy

'Below is my attempt to paste the cells based on the dates that go from cell D5 to O5, the current report date is in cell B5

For i = 4 To 16 Step 2

If Cells(5, i) = Range("B5") Then
Range(Cells(5, i)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next i

End Sub

I am sure that the fix to this is relatively simple, however i have not done much VB coding. Back in the day i was quite proficient in Basic but years of not using it have depleted my skills somewhat.

I need to get this workbook finished by next week to present to various members of a management team at a conference, so any help would be greatly appreciated.

Cheers, Mat

I have macros that are name "gonightly", "enterinfo", etc
im not sure what happened or what i hit but now they are:
"register.xls!module!17gonightly" , "register.xls!module12enterinfo"
and now excel cant find them because i have it typed in as "gonightly"
anyone have any idea how to fix this?

thanks in advance

My text file has 4 lines that I'd like to copy into Excel file. I want one line per cell in Excel, but somehow it breaks every text file line into multiple lines. It looks to me that Excel is breaking it every time it sees <p>. So instead of seeing just 4 Excel cells being filled up, I see over 50+

I don't want it to do that It didn't happen with one of my other text file so I am not sure what I did wrong this time…

I searched the forum and see most people want to split up the content, I don't want that in this case.

How should I fix this?

Please help. Thank you in advance.

Hello, this has been happening for about a month. It does not happen every day, sometimes it will be fine for 2 or 3 days, but it seems to happen randomly. All of the characters on my keyboard when typing in excel will change to weird characters, that I can only assume is russian. ; , : , " , ' , . symbols change to something different aswell as just regular keys. I will attach a picture to give an example. How can I change this back? Please note that I can get it fixed by exiting Excel & opening it again, but I have to save all my work and close all my spreadsheets to do this. If there is some kind of shortcut or key that I can press to fix it, that would be wonderful. And I'm not sure why it happens. Usually I'll be away from my keyboard for a hour or up to a day, come back, and everything's as I've described. Thank you for any help !

excelkeys.JPG

Trying this again - seems I keep getting logged out and my post not posting...

I posted last night for a combined If and Vlookup functio - thank you for the help!

I have slightly changed the formula to this...

=IF(VLOOKUP($A14,Sheet1!$A$2:$P$11,7,0)="-","-",VLOOKUP($A14,Sheet1!$A$2:$P$11,7,0))/M14

Getting an error: #VALUE!

Need to show "-" any ideas on how to fix?

Also this formula is pulling % attainment (sheet 1 = goal) and (m7 = actual) but %attainment need to be in reverse (goal = 15% and actual = 12% (good at 125%) if actual = 16% (bad =93.8%)) could you look over this formula to make sure it will reflect correct? It looks to but want to make sure my math is right.

Thank you for any help you can provide!

HI, I have a spreasheet which schedules time on a shop floor. I built it using the 1904 date system; however, because I needed to link it to another sheet to bring in data I had to uncheck the 1904 date system option.
Virtually all of my functionality seems to have stayed intact EXCEPT one problem....

The shops "Booked" hours are subtracted from the "Available" hours. I am using 24 hour clock and format of [h]:mm as the shop can easily have 50-60 hours work booked in a day. Currently the formula for booked-available works UNTIL you exceed 0 hours available and then it goes to an error. It used to show the hours available as a negative when it was 1904 and now it will not. Is there a way to fix this? Ideally I would like to know if I can convert the value to 1904 date format, but not sure how this is possible.

Thanks for any help on this.

Hi All
need a little help
i am cleaning up some xls'. im not sure if i am on the right path however
could anyone reccomend a fix for the following

i have names from one sheet in coulum
A
i have names from another sheet in column
C
i have their emails in column
D

A and C are from diffferent sheets/data and do not match, so i cant sort them.
i would like to populate B with emails from D IF they are present or match ( A and C)
like i said i am not a hundred percent sure i am on the right track, but i have washed the data so the names in A and C are the same.
i have used the IF formula to work a little however i would like it to search the entire column not just the one row
id like a more expansive IF than
 < i think that's how i used it. 
i tried something like
 but it didnt work...
with my limited formula i get results like

(a)______ (b)___________(c)___(d)
bob(formula returns email) bob email
tom (formula returns email) tom email
harry (formula does not return email) garry email
garry (formula does not return email) harry email

thanks for any input.
if you can find a forumla to populate col B, id be most appreciative
-edit hope this complies with the rules and the eg book is okay.


No luck finding an answer? You could always try Google.