Free Microsoft Excel 2013 Quick Reference

Display formula in all worksheets Results

I have a spreadsheet with numbers and text. Many numbers are derived from
cell formulas. I can vertically align the text in all the cells in the
numbers/text worksheet, but if I change the numbers to display formulas only,
then the cell values automatically align at the bottom of the cell no matter
what I have set the alignment to be.

I just want to align the formulas at the tops of the cells so that I have
room to write underneath them on a printed copy. Is this possible?

I have a workbook with used for forecasting. I use a template sheet for each
forecast (all forecast sheets -54- are the same and contain sheet-level
names).

On each sheet I have a formula that counts the number of chargeable
engagements (where the chargeable engagement is indicated by a "C" in the
preceding column) and places the results in a cell with concatenated text.
The 'chargeable designation' column is a named range.

I can get the formula to work on only two of the worksheets. It can be any
two sheets, but it will only work on two sheets maximum. I have tried a
number of work arounds with no success. I sure this is a straight-forward
problem, but I'm unclear where to start. I have a feeling the named range is
creating the problem, but referencing the range itself does not work either.

example: (this is an excel spreadsheet, clearly, I'm not an designer)

| A | B |C D
E
-- |-------------- |--------------- |-----------------------------------
1 | Chg Status | Engagement |
2 | "C" | eBay |
3 | "C" | Yahoo |
4 | "C" | Google |
5 | "A" | Admin time |
6 | "H" | Holiday time |
7 | | |

(is array formula entered with Shift+Control+Enter)
{=CONCATENATE("Chargeable Client Count = ", COUNTIF(Chg.Type,"C"))}

Where the range in column A is named Chg.Type

I've tried changing the named range (chg.Type) to the direct reference
(A2:A6), but all I get in either case is the formula text displayed in the
cell

{=CONCATENATE("Chargeable Client Count = ", COUNTIF(A2:A7,"C"))}

I have a number of other COUNTIF functions in the worksheet that work fine.
It's the array formulas that are not functioning correctly. The workbook is
currently about 7megs in size.

Any assistance would be greatly appreciated.

thom
hunter1sf@yahoo.com

Recently I found this forum through a Google search along with the perfect solution to a problem I was having finding and replacing text based on a table of replacement values. Here is the thread:

http://www.ozgrid.com/forum/showthread.php?t=29700

I incorporated the SuperSub function that steveorg developed as a result of that thread into my worksheet by creating a new module and pasting the following code into it:


	VB:
	
 Range) 
    Dim cel As Range 
    Dim strOldText As String, strNewText As String 
     ' loop through list of old_text used in substitute
    For Each cel In rngOldText.Cells 
        strOldText = cel.Value 
        strNewText = cel.Offset(0, 1).Value 
        OriginalText = Application.WorksheetFunction.Substitute(OriginalText, strOldText, strNewText) 
    Next cel 
    SuperSub = OriginalText 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It works like a charm as a formula, but I also have a macro in the same workbook that, as soon as it selects the sheet with the formula, calls the SuperSub function and starts executing the code as a macro (even though there is no call to this routine in the macro). The macro ran just fine before incorporating the function into my worksheet.

The formula looks like this: =TRIM((supersub(UPPER(E2),rngSubst)))
If cell E2 contains the string "101 North Main Street, Apartment 5", it would return "101 N MAIN ST APT 5"

Here is the macro:

	VB:
	
 Import() 
     '
    Sheets("Shoebuy FTP").Select 
    Range("A2:R200").ClearContents 'This is where it jumps to Function SuperSub(OriginalText As String, rngOldText As Range)
     
    Range("A2").Select 
    Workbooks.OpenText Filename:="Y:ShoebuyTest.txt", Origin:=437, StartRow _ 
    :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ 
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ 
    , Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 2 _ 
    ), Array(2, 1), Array(3, 9), Array(4, 9), Array(5, 9), Array(6, 3), Array(7, 1), Array(8, 1), _ 
    Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 2), Array(13, 1), Array(14, 9), Array(15 _ 
    , 1), Array(16, 1), Array(17, 9), Array(18, 2), Array(19, 1), Array(20, 1), Array(21, 1), _ 
    Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1)), TrailingMinusNumbers:=True 
    Range("A1:R200").Select 
    Selection.Copy 
    Windows("Shoebuy FTP.xls").Activate 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The function runs for every occurrence of the formula in the worksheet, and when it is finished, all of the cells containing that formula show a value of "#VALUE!". I should mention that it does not change the formula at all, but I then have to highlight each cell that contains the formula, press to edit, then to get it to display the correct results again.

It is probably some stupid little thing that I didn't do when I created the function, but I can't figure it out. I have written numerous macros, but this is the first time I have ever created a custom function. Perhaps Steve who wrote this function, or somebody else on this board that has used it, can help me figure out why it is behaving this way and how to fix it?

Thanks in advance for any help.

Alan

Hello all.

Again, forgive my ignorance in Excel, a project was thrown at me with certain expectations that I haven't the knowledge to produce. Hoping you guys can help again.

I have attached my sample workbook, and basically all I am trying to achieve is a formula that will generate a list of parts that need to be produced on Sheet 5, from sheets 2 and 3 based on two primary criteria.

I have conditional formatting as an eye catching aid to display a red background if an item drops below a desired inventory level, is at 0, or if the "Qty on Order" field exceeds the "Inventory Qty" field. Simple enough. So what I need the formula to do, is if any part numbers "Inventory Qty" field has a red background, to make a list on Sheet 5 of all those parts. However, if the "Work Order" field changes to "Open" for the coresponding part number, than I need the part number to not display to that list.

So in practice I will have a list of parts that need work orders, but once I make one, and change the work order status to open, it will no longer be on the list, as it no longer needs a work order, but is not yet produced to bring the inventory level up high enough to cancel the red flagged low inventory level.

Thanks in advance for your help.

Hi All,

I got a Excel workbook from a friend. I observed that in a worksheet the excel formulas are not working i.e. if i type a formula in a cell and click enter the cell displays the formula but not data for the formula.

E.g.: In attached screen shot, the columan P should display Date instead =A1 is displayed.

Please let me know how to enable the formulas.

Thanks & Regards,
Abhi Ram

Hi everyone

I have one of those perplexing problems with a formula that works correctly on some rows but not on others, even though they appear to be formulating the data in the same way.

The only way that I can show this is by creating a mini workbook showing some correct and incorrect examples, which I will explain as follows:

The worksheet containing the problem is “Collections” and affects results in columns headed “Wks” and “Rate” following a Zero balance from the previous week.

For customer Pamela Swift (row 6), the formula worked correctly in that there is a Zero balance in Week 34 (AC6) and Week 35 “Wks” (AD6) and “Rate” (AE6) are correctly displayed as zero.

Then we come to Trevor Smith (row 61), where a Zero balance is shown in AH61 and despite using the same formula, in the following week 35, “Wks” (AI61) continues to increment by 1 as normal and “Rate” (AJ61) displays a value instead of Zero.

The same ‘bad’ result also happens in rows 85, 101 and 111 but I cannot understand why the formula does not work on these occasions.

I am also trying to find out why I cannot search on the Num (B) column. If I try to search for a customer number, Excel says it cannot be found. I assume this is something to do with the fact that the number is obtained via a formula but the column is formatted as a number.

Lastly, this is only a very small version of the workbook that I use. The Input worksheet may have up to 15,000 rows of Data and the Collections worksheet could have up to 2000 rows of data. Plus there are additional worksheets, although these do not perform a major amount of calculations. All in all, it takes some time to save the workbook or update the calculations; so I am looking for ways to speed this up. Any suggestions gratefully received.

My main concern is the first problem described above and I am hoping someone can assist in solving this dilemma.

Thanks in advance …David

Hi,

I've searched through 20 pages and found a thread that I thought would solve my problem, however the result didn't make sense so here goes with my problem:

My music collection is recorded in excel. I have a worksheet for ‘tracks’ and another worksheet for actual ‘records’.
Within my tracks spreadsheet I want a formula in the last column against each track which denotes the corresponding actual record I.D. number (Column I)

In the Tracks spreadsheet, column H I've Concatanated to give a more unique reference. Column I is a Look Up formula but obviously only finds the first instance.

The problem is, I do have different versions of the same track eg. 7”, LP, Compilation LP etc. So Look Up only finds the first exact match, whereas I need a formula that finds all matches in my ‘Records’ spreadsheet for the particular track. I know a possible solution is to use the Find function, but with over 10,000 tracks that’s a lot of finding, plus if I sell a record I want it’s reference to be automatically removed from its corresponding place in the tracks spreadsheet, and I’m sure given all Excels capabilities there should be a formula for this!?
Eg. In my Tracks spreadsheet Line 2, column I, should show result: 1.3, 6

Is there a way?

I've attached a small example file for ease of understanding.

Thanks in advance!

I'm an entry level excel user and have a query on how to get excel to search a worksheet with different types of data and copy specific rows to separate sheets.

In the attached file, you can see 3 different sections- A, B & C, all with different column headings.

In all sections, entering the 'Code' should get the 'Name' to automatically appear (I think i've got that sorted by using a vlookup formula to search another sheet). In Section A, the formula will also automatically display the 'Ref' and 'Ref2'.

This sheet needs to be completed around once a week and the number of entries in each section will vary on each occasion. Therefore, any solution would have to be able to compensate for extra/fewer rows in each section.

What I need is to set up a master file with 3 sheets- 'A', 'B' & 'C', and I need some way of searching each new weekly file created, copy all the data from section A on the weekly sheet to worksheet A in the masterfile, copy all the data from section B on the weekly sheet to worksheet A in the masterfile etc.

Ultimately, the masterfile's 3 sheets would maintain a record of all the entries ever made.

Am I making sense? Is this possible with a macro (assuming each weekly file is named in a specific format)?

Can anyone please help?

The add-in defines a custom function that returns a catalog price. The
formula in the cell is "=CAT05("1234", Price). When the user that created
the file opens the file, all is well. When another user opens the file we
have problems. Even though all of our users have the same add-in, the file
worksheet will try to reference the creators add-in, i.e. in the non
creators display the cell say "='C:Documents and SettingsUserApplication
DataMicrosoftAddInsCustomTools.xla'!CAT05("1234", Price)", where "User"
is the file creator. So the cell returns the "#NAME?" error.

I have been using find and replace to delete the path of the originator and
just display "=CAT05("1234", Price)"[ That gets it to work correctly]. So my
question is what do I need to do, either when the file is created or when it
is reopened, so the path is not inserted into the formula?

Hopefully someone can help out with this. I've searched and search but the keywords I use must be too vague because I just bring up too much random stuff, none of it useful.

Anyway, I have a large workbook with many worksheets (somewhere over 2,000), and all of my main page data uses conditional formulas to total counts from other worksheets in the workbook. Every once in awhile, although I get no errors on save/close, when I reopen the file, I get the whole "unreadable content" thing going.

Eventually Excel returns something like this:
Removed Feature: View from /xl/worksheets/sheet1503.xml part

Now, obviously, I want to see what's wrong with page 1503, but short of sitting there counting, I don't know how to find it. Is there a simple way to either display a list of worksheet numbers and corresponding worksheet names, or a way to make Excel jump directly to this worksheet? Or even to randomly click on a worksheet and display its worksheet number?

I can't even know which formula would be affected by changes to this page since my formulas reference page names and not numbers.

Any help appreciated.

I'm stuck with this! Basically I need sheet 2 to read a text value in sheet 1, but I do not want the formula to be displayed as I only need the text value.

So if cell A1 in Sheet 1 read 'Excel', I would normally enter '=Sheet1!A1' in Sheet2 - This is later preventing me from using a macro which looks up all the main headings when recording certain data.

Hope that makes sense!

I am working on a financial spreadsheet. One cell is a formula that equals the profit or loss of a given trade based on a number of assumptions.

One assumption in another cell (let's say B35) is the final stock price (i.e. in a few months...a prediction). However, I would like to create a column that is a series of potential final stock prices (maybe 100 prices or so) and then have the cell right next to each of those assumptions equal the profit/loss that the trade would then equal. I don't know how to take all these new assumed stock prices and plug them into the equation so my new profit/loss is based on the new assumption. I don't want to have to change the assumption each time. I want a bunch of assumptions and then the output for each of those assumptions.

This should be pretty simple to do but I don't know how to do it.

Thanks

Update: I posted my worksheet. Right now I input my assumed final stock price into B3 and then the total profit/loss from the trades (up to 4 calls, 4 puts, 1 long and 1 short) in cell B35.

However, I'd like to have this final profit/loss read out in cells B43:B443 based on each cell's respective assumed stock price (the cell right next to it in column A). e.g. since A97 is 13.50 and the two trades currently loaded in my worksheet would then equal a 1,722.22 profit, cell B97 should be a formula that reads 1,722.22.

thanks for any help

I am wanting to copy cells in my worksheet from B11 to B23. The formula in B11 is referencing another cell in a different sheet. B11 equals 'q1'H3. (q1 being the other sheet name) As I move down to B12 and so on they also reference the other sheet. All of them have row 3 in the formula eg: B12='q1'G3 B13='q1'E3.
As you can see there is no real order here. I am getting horizontal values from the other sheet and wanting to display them vertically.

What I want to be able to do is copy the formulas from cells B11 to B23 and paste them into B27 to B39. I need the reference to the sheet though to change from Row 3 to Row 4. This would mean that B27 should equal 'q1'H4

Is anyone able to help.

Hi...hope you can help.

I have a few worksheets with some personnel info. I put all of this in one worksheet. One of the items I have listed is their promotion date.

On another worksheet, I copy the promotion date so I can do a calculation (=Personnel!E6). The formula I use to calculate their time in grade is
=IF(E8<>"",DATEDIF(E8,TODAY(),"y"),"")

The problem I have, if the promotion date in the "Personnel" sheet is blank, the formula is returning a value of 106 instead of being blank until I enter a promotion date on the "Personnel" worksheet.

Cell E6 on the "personnel" sheet is the promotion date
Cell E8 on "sheet2" is copied from the personnel sheet
Cell E9 on "sheet2" contains the above formula, and it's returning a value of 106.

Is there a way to fix that?
Thanx

I have notice recent instances where I have placed a formula or function in a
worksheet and the cell displays the equation and not the result.

This occurs even though I have autocalculate enabled. Also pressing F9 does
not resolve the issue. I have also tried toggling CTL+` without sucess

Interstingly this does not occur in all cells in a worksheet, the function
or equation shows results in other locations.
--
LBarnes

SAMPLE: LITERATURE-ENGs.xlsm

I have data in (Sheet1) for all 12 months of the year. Each month has 4 columns of data. In the other worksheet (Sheet2) I need it to automatically show 4 months worth based on the information in (Sheet1). However the user determines which 4 months.

Let me explain how the data is laid out in each worksheet:

(Sheet1)
Starts with September and goes to August. Each month has 4 columns of user inputted data and also formulas, including text and numbers. For the formulas, the values are the essential information, not the formula itself.

September
Permanently displayed in ("D3"=9)
All its data is always displayed in ("D7:G91")

October
Permanently displayed in ("H3"=10)
All its data is always displayed in ("H7:K91")

November
Permanently displayed in ("L3"=11)
All its data is always displayed in ("L7:O91")

.....and so forth, each month has 4 columns of data that extend to row 91. The last month is August.

(Sheet2)

Has the identical amount of space, but only for 4 months worth of data.
Month A - Data needs to be in ("K7:N91") and the title of the month represented by a number is in ("L4")
Month B - Data needs to be in ("O7:R91") and the title of the month represented by a number is in ("P4")
Month C - Data needs to be in ("S7:V91") and the title of the month represented by a number is in ("T4")
Month D - Data needs to be in ("W7:Z91") and the title of the month represented by a number is in ("X4")

Which four months is determined by the user. This is how:
in "F2", the user enters a number. For example 1 (where he's referring to the month of January)

Automatically the result is:
("L4"=11) representing November
("P4"=12) representing December
("T4"=1) representing January
("X4"=2) representing February

As soon as the user enters a value in "F2" I need the information from the corresponding months in (Sheet1) to be instantly entered in (Sheet2) to all the cells corresponding to the 4 months that are asked for in L4, P4, T4, X4.

Keep in mind that the months in (Sheet1) begin with September and end with August. So, sometimes data will be needed from August-November, which aren't in sequence in (Sheet1)...

SAMPLE: LITERATURE-ENGs.xlsm
Can someone help me please?!

Thanks!

I am working in MS XP. I have aa Excel workbook that has ~ 40 worksheets. Worksheet Num1 is the Master worksheet that compiles data from all the other sheets.
The scheme is as follows:

Each sheet represents a 12 month budget for a division.

Each month is in a column and summed in row 3. Months and sums alternate columns. Col. A=Jan., Col. B=Jan costs, Col. C=Feb., Col D=Feb. costs, etc.

The Master sheet souce links to the data as follows:
C8='Div. 1'!B3, D8='Div. 1'!D3, E8='Div. 1'!F3
C16='Div. 5'!B3, D16='Div. 5'!D3, E16='Div. 5'!F3
C48='Div. 10'!B3, D48='Div. 10'!D3, E48='Div. 10'!F3
where worksheet tabs are labeled Div. 1, Div. 2 etc.

I want to write a macro that will alter the formulas by adding ()*1.06 where the formula in the cell is in between the parantheses. The resulting displayed value would be the sum of the previous years contract costs multiplied by 1.06 for a 6% annual increase.

So far using the relative reference command copies the formula from the originally recorded macro start point and inserts it into the subsequent cells and the source cell seperation is lost.

Any suggestions?

Excel 2007

I have a column that has the following in it
The column is NOT in cell reference order

I want to change what is in the cell to display like this:


anyway to FILL that for all 500 rows?
OR any way to temporally sort by cell reference instead of value and then I can do the FILL

Any help here will be appreciated.

Thanks in advance

duce

VBA automation -
Excel added the formula - but they all return "Not Found"
After the VBA automation - I visit each cell, see the formula is correct and then press Control +Shift + Enter; then the correct value displays! Of course, the curly brackets also appear in the formula bar.

In VBA I tried to use the:
objXL.ActiveCell.FormulaArray = "=IF(ISERROR(INDEX(PositionDataSell!$T$2:$T$505,MATCH(" & lngContractID & "&" & strQuote & strConCat .....
The Excel Formula bar for the cell is just empty (blank)

Using only the Formula property:
objXL.ActiveCell.Formula = "=IF(ISERROR(INDEX(PositionDataSell!$T$2:$T$505,MATCH(" & lngContractID & "&" & strQuote & strConCat .....

The correct formula is created in each cell, but...
Until I visit that cell and use the Control+Shift+Enter - the lookup will not work. It is proof that the right formula is there, without the curly brakcets.

In Excel - my vba code successfully constructs these formulas:
In essence: It checks for an error and prints "Not Found" if no match is found in the check. It test for two values in a row - matches them to two columns on a row in another worksheet, and returns a third value for the matches of the same row.

and, maybe I also need to think of a way to prevent the users from visiting a cell as well
Maybe I hide the cells with the actual formula and display a cell with the actual value of the cell with a formula?

I'm having a problem here... recently upgraded to a new version of excel and I've been looking in all the options can't seem to find a solution.

It seems whenever I enter a formula referencing a blank cell on another sheet, instead of displaying a blank value like on the other sheet it shows a "0". I still want to display 0 values where appropriate, so selecting "do not show zero values" doesn't work for me.

I just want it to show a zero in cells formated at numeric, and blank values (nothing) in cells formatted as either general or text or whatever.

Any help would be appreciated,

Thanks.

Chris


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