Free Microsoft Excel 2013 Quick Reference

Formulas creating formula Results

Hi, i want to create the "cases" formula for excel to simulate Select case behavior (with multiple arguments and else optional).
If A1 and A2 are excel cells, this is the goal:

A1 Case: -- A2 Formula: ---------------------------------------------------------------- A2 Result
5 ------- cases({A1>5,"greather than 5"}, {A1

I am trying to create a txt string from multiple cells, but having some problems.

I have a list of six items (A1,A2,A3,A4,A5,A6) in one column. Not all of them will be occupied at one time. i.e sometimes only A1 and A2 will be occupied and A3:A6 unoccupied. If a row is occupied then there will be additional data in columns BCDEFG.

I wanted to use the data to create one txt string but the user can vary the way the txt string is represented. I want 6 different ways of the representing the txt string.

I created a drop down box (data validation) that has 6 text options (for example Option 1 in the drop down box reads “To item 1 (A1) in a solvent (A6) was added items 2(A2)-6 (A3,A4,A5) at Temperature X (C2)”

By selecting one of the options i want the txt string to follow that basic formula and detect whether column 1 has entries and only quote the rows that are filled.

I also want the formula to have multiple outcomes so if the user chooses option two from the drop down box, instead of the results from option 1 a different formula will be used to create the txt string in the same cell as the outcome from option 1. This will need to be done for 6 formulas in total corresponding to the 6 different options.

I know it might go:

=IF(C14="To item 1 in a solvent was added items 2-6 at Temperature X","To"&" "&B7&" "&"("&G7&" "&"g"&" "&ROUND(D7,2)&" "&"mmol"&")"&" "&"in"&" "&B13&" "&"("&ROUND(#REF!,1)&" "&"mL"&" "&ROUND(C10,1)&" "&"mmol"&")"&" was heated for 18h"&"."&" "&T(A11)&" "&" "&T(A13)&" "&T(A15)&" "&"To give"&" "&T(B8)&" "&"("&G8&" "&"g"&","&" "&ROUND(D18,1)&"%"&")"&" "&"as a"& " "& C19&".",do nothing)

I am thinking if this will include 6 different if formulas then it might get quite long.

You help would be much appreciated.

Many thanks,


If I have a column that contains different times listed in a 24 hour format, for example 13:25 (signifying 1:25pm), do those numbers have to be handled in a certain way similar to dates being treated as true dates using "DATEVALUE" and numbers as true numbers?

Also can I create a formula in another cell to pick up the earliest time from that column as well as the latest time? What would that formula look like?

Hi Guys,

I've used a sumproduct formula to add up the values of criteria matching 3 different fields, but don't know how to use this information to create a count formula (e.g. counting how many of these cells have been added together).

The sumproduct formula is:
=SUMPRODUCT(($G$27:$G$65533="H")+0,($H$27:$H$65533="12 ")+0,($I$27:$I$65533="")+0,$E$27:$E$65533)

Is it possible to create a count that uses these criteria?


Hello - I am well versed with formulas but still a begginer with VBA so bear with me.

I am trying to write a Macro that performs a Vlookup using a value in Sheet1 against an array in Sheet 2.
There are three columns in sheet 1 - ProductCode, CodeFamily, CurrentInventory
I want the value of the function to drop the new inventory number (off the array in Sheet 2) in new Column D
Then after it returns that value I want in to autmatcally filter the differences between CurrentInventory (column C) and UpdatedInventory (column D)

Here is where I got so far - I am stuck on the Loop function and haven't performed an autofilter yet...

    Dim lookFor As Range 
    Dim rng As Range 
    Dim found As Variant 
    Set lookFor = Sheets("Sheet1").Range("A2") 
    Set rng = Sheets("Sheet2").Columns("A:B") 
    ActiveCell.Offset(0, 0).Select 
    Do While Not IsEmpty(ActiveCell.Select) 
        On Error Resume Next 
        found = Application.VLookup(lookFor.Value, rng, 2, 0) 
        ActiveCell.Value = found 
    On Error Goto 0 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Please help!

I want to create a 'virtual switch' so that if I change one cell from 1 to 0, that a formula in another cell becomes active. I can't just have the formula cell * the 1/0 cell, I need to comment out the formula or it draws in too much data.
Please see the attached, it's probably clearer than explaining it here!

Thanks to all,


Hey everyone,

I have been working on a project recently and im at a loss of how I should do this. I will try and describe what I am trying to do: I have 2 columns which list part number and the price for that (both of which have been gathered from a number of other excel documents and programs). Once a year, all the information gets updated (a simple copy and paste process), and then the year is canged (which you will be able to see on the front page).

Now to keep a record of the history, one would normally have to copy and paste the old data and Special Paste it into the clolumns next to the current (as seen on page 2), what i am trying to do is make it so that when the date gets changed/or the data gets updated, it automatically copies the info from that year and adds it to the end of the history table (which would be page page 3, but i had to remove it so the file would fit, but page 3 is essentially just a copy of the 2003-2006 data in a differnt page) other words, when the data gets changed to the 2008 data, the current 2007 data will be copied, and special pasted (dont want to use the formulas, just the numbers in the table) into the history.

I have been informed that I may have to use VB, which I have never used befor in my life, but if it can be done without, that is just fine by me.

I hope that this wasnt to confusing for eveyone to understand, and I will be attaching a sample so you can see how it works. (It may get rid of the values under 2007 std, just assume that they are filled with $ amounts)

Thanks in advance to everyone, and sorry for such a long post.


I also put up a file that you can download, just a bit over 100mb so you can see what I am talking about.


I want to create a formula such that it reads a value from one cell in a column and depending on the value, it selects the appropriate row in a second column. e.g

column C has values in 50 rows,
column F has (as values) the row number of column C like F1=11, F2=15, F3=40

In column H, I want to sum the values of column C from c1 to row number stated in column F. i.e =sum(c1:row number of C mentioned in F1[which is c:11])

I will appreciate any help.


I'm looking for an answer about how I can show the numbers underlying a
nested IF function. The formula works fine, but I need to be able to show
how I arrived at the numbers that I did. Here is an example of one of the formulas:


Instead of the reference to the cell, I would like the formula that the cell represents. I know that I could go through individually and pull each piece apart with control F9, but since there are numerous cells and because the data changes every time I create a new budget, it would help if I could have some way to display it all with one action.

Thanks for your time.

Hey All, I am working on a worksheet template that creates a list in which the length is controlled by data input into a cell. One of the columns of the list contains a Vlookup formula to load data in the cell automatically. The cells in the column also contain a drop down list.
Okay, everything works fine. If I set a list length to a certian number and fill in the data, using the drop down list on some cells and filling the others in automatically, it works great. Now if I make the list smaller, the cells in which I set the data through the drop down list remains filled with old data. It appears they lose the formula part of the cell and only the drop down remains.
Is this clear at all? Is there any way to overcome this prediciment?


btw instead of the old data I would like to revert to the original formula (Vlookup) AND the dropdown list.


I'm doing some HR data, and would like to create summaries. PIVOT table will work great for this, but I recently discovered array input (Ctrl-Shift-Enter), and I want to experiment. Could someone explain to me why my formula in this example sheet isn't working?


Doesn't sumif accept "created" ranges?


Could any one help me.

I want to create a formula that will put a specific value in a cell based on what is imput in another cell.

For example if 4.99 is entered into cell A1 I want Cell D1 to auto give a value of 1.45, but if A1 value is entered as 2.99 I want the value to automatically say 0.70 instead and so on.

Can anyone help me!!!!!

Much appreciated

Dean (beginner on Excel spreadsheets)

Hello, would be grateful if anybody can help me! This may be a silly question, so apologies in advance. I

am compiling a spreadsheet which details whether four different countries consume a product. The products are listed down the spreadsheet, the countries across the columns. I have put a 'yes' or 'no' in the columns depending on whether the particular country consumes the product.

I now need to create a fifth column which will say whether any of the countries consume the product e.g if none of the countries consume a product, the fifth column will say 'no'. But if one or more consumes the product, the fifth column will say 'yes'. Is it possible to compile a formula to do this?

Thanks in advance!


Hi all

I would like to build a flexible link formula where user
can change any 1 of the parts and formula will recalculate based on new choices

Here is the link created by enter = in cell on wrk1 and clicking cell in wrk2


I broke it into parts and built a concatenation formula

B28 is path
C28 is file
G5 is tab
G7 is Col
G6 is row

Is there a way to accomplish?
If no this method, a different way?
User friendly is best.
Users don't necessarily know row links
I was thinking of a drop down of word choices
Then use vlookup to get number based on word chosen

My spreadsheet attached

Hello there,

I'm trying to automate a spreadsheet that will display a Weekend Duty Roster.

Besides "DATE", I've got 3 Columns:
CoordinatorTech 1Tech 2
Each column has a list of names. Each list has a different number of people. There are more Coordinators than Techs ... e.g.- Techs are On Call more often than Coordinators.

I know I can put the 3 lists on a seperate page, and reference them somehow with formulas. But how can I create formulas that automatically "know" how long each list is, and adjust if names are added/deleted?


I have created an invioce on excel to bill clients by the hour.
I have one row for hours and the other for cost. What I would like to happen is for the cost to change automtically when I enter the number of hours.
For ex:

1 $25.00
1.5 $37.50
2 $50.00 ect...
Please assit

I have a spreadsheet to track the timing of incoming files and a formula to calculate if the files arrive after their deadline. My problem arises when there is a file that is actually early but the formula reads it as late because it comes before midnight (i.e. the file is due by 2 AM and it arrives 3 hours early at 11 PM and the formula marks it as late). The dates are in the first column. Is there a way to create a formula that calculates the timeliness of the file based on the day? Thank you!

The following is a schedule we have for our church daycare. We have three groups of kids. Is there a way with VBA or a formula to create a schedule from this schedule so that the three groups are not doing the same thing at the same time. Except for Breakfast, Snack Time and Lunch. I hope this made since. If not I will try to explain better.

6:00 a.m.-7:15 a.m. open center / free time
7:15 a.m.-7:30 a.m. bathroom break / wash hands
7:30 a.m.-8:00 a.m. breakfast
8:00 a.m.-8:15 a.m. clean-up / wash hands
8:15 a.m.-8:30 a.m. story / group time
8:30 a.m.-9:45 a.m. free play
9:45 a.m.-10:00 a.m. bathroom break / wash hands
10:00 a.m.-10:15 a.m. snack time
10:15 a.m.-10:25 a.m. bathroom break
10:25 a.m.-11:15 a.m. outside / gross motor activity
11:15 a.m.-11:45 a.m. craft time
11:45 a.m.-12:00 p.m. clean-up / bathroom break
12:00 p.m.-12:30 p.m. lunch
12:30 p.m.-3:00 p.m. nap
3:00 p.m.-3:30 p.m. wake-up / bathroom break / quiet time
3:30 p.m.-4:00 p.m. free play / center time
4:00 p.m.-4:10 p.m. bathroom break / wash hands
4:10 p.m.-4:30 p.m. snack time
4:30 p.m.-6:00 p.m. free choice / outside ( pick up time )


I am trying to create an excel employee schedule for 4 different outlets.Two managers schedule at different times and I need to find a way to format cells representing the same work shifts in different outlets(i.e sheets)not to allow overlapping shifts.I'm htinking maybe i could use an "IF" formula

I've created my dynamic named ranges using the OFFSET function, ex. ="OFFSET(SAMPLE!$D$4,1,0,COUNTA(SAMPLE!$D:$D)-1,1)"

I'm now trying to get my chart to use that range. I read at that I need to make sure my series reference is a fully qualified reference. So I've entered that series reference using the name of the workbook followed by the range name (=2012-PIRS.xlsx!SAM_CLAR2DEL). I have two copies of my workbook (one as xlsm with VBA project, and one with xlsx without VBA) and I can't get Excel to accept the series value in either workbook.

The formula you typed contains an error. Try one of the following:
- Make sure you've included all parentheses and required arguments.
- To get assistance with using a function, click Insert Function on the Formulas tab (in the Function Library group).
- If you include a reference to another sheet or workbook, verify that the reference is correct.
- If you are not trying to enter a formula, avoid.........blah, blah, blah.

Please help.

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