Free Microsoft Excel 2013 Quick Reference

- How to Create excel formula with same Vba's Select Case sentence behavior?
- Create different text strings from mutiple cells via user selected inputs
- Handling Time in a worksheet as a value to compute a formula
- Counting from a sumproduct formula
- Create Maco with Vlookup and Autofilter
- Conditionally Use Cell Value in A Formula
- Creating A History Page
- Value Of A Cell Be Used As Row In Formula
- Seeking Way To Display Numbers Underlying Formulas
- List and formula in the same cell
- Cant use "created" range in Sumif
- Formulas
- IF formula difficulties
- Building a link formula by concatenating the parts
- "Re-Displaying" Named Ranges
- Formulas
- Formula to calculate if files are on time
- Creating a Schedule with VBA
- Formulas : schedule issues
- Create Chart using Dynamic Named Ranges for Series

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 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,

James

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?

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?

Thanks!

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

VB:Please help!Vlookup() Dim lookFor As Range Dim rng As Range Dim found As Variant Worksheets("Sheet1").Activate 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) Sheets("sheet1").Range("d2").Active ActiveCell.Value = found Loop On Error Goto 0 End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Please see the attached, it's probably clearer than explaining it here!

Thanks to all,

L

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

-Ben-

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

http://putstuff.putfile.com/86230/1428695

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.

thanks,

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:

=IF(B9=1,(B4*Data!G5*Data!F5),IF(Calculations!B10=2,(Calculations!B5*Data!G5*Data!F5),IF(Calculations!B11=3,(Calculations!B6*Data!G5*Data!F5),0)))

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.

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?

TIA

Keith

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?

=sumif(($A$2:$A$9="A")*($B$2:$B$9="M"),true,$C$2:$C$9)

Doesn't sumif accept "created" ranges?

Regards

Reenen

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)

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!

J.

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

='C:ME_FY06[ME-019.xls]REP-WKLY'!D$16

I broke it into parts and built a concatenation formula

=CONCATENATE("='",$B28,"[",C28,"]",G$5,"'!",G$7,G$6)

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

Thanks

-marc

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?

Thanks,

David

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

Gator

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 )

Thanks

Gary

I'm now trying to get my chart to use that range. I read at http://dmoffat.wordpress.com/2011/05...the-right-way/ 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.

ERROR MSG:

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.