Free Microsoft Excel 2013 Quick Reference

- Cell to remain constant value in column
- Constant Value in formula.
- VBA Code To find Hardcoded Values in Formula
- Transform constant values into formulas w/macro
- Nth Largest Value In Pivot Table
- Maintain reference to constant value while filling formula down column
- Freeze Value of Live Data Cell & return frozen value in another cell
- Total based on one constant value
- How to set text color based on value in cell compared to constant
- Formulas won't drag down the column
- Total based on one constant value
- SUM values in a column till you reach a flag in another column- Urgent
- How can I reference the first value in a column of numbers when ......
- Using a constant value
- Display value in column 5 of a row if two dependants exist in row
- Adding Rows based on corresponding value in another column
- Formula to Increment Value in Cell by One Based on Other Cells
- Conditional format for constants vs formulas
- Reduce Excel File Size by Automatic Filling of Formulae ???
- Dynamic Calculations Using Various Value Combinations

Its my desire to keep the same value (or same cell) as a constant value in my formula down my column. But as a result of my misuse of the correct placement for "$" it isnt keeping this value for each row when I drag formula down my column...please help...yikes!

Thanks everyone....

'constant'. How do I get it to recognize it as a 'valid reference'. I am

trying to reference it from cell 'M2'.

=IF(ISNUMBER(L4),"Good","Enter Again!")

Please help... I'm 'bumfuzzled'!

--

Thanx,

Guy

I am struggling to create/find a procedure that will find hard coded values in formula. So far I have been using code that searches for special cells e.g. GoTo Specials cells for constants with Number and Text. However I can not find a way of selecting cells which are part refering to another cell and part refering to a hardcoded value or a formula that just refers to a hardcoded value i.e. a formula with no precedents.

Examples of formula I can not automatically select:

=A1+1

=1+2

=1+A1+H78*J80+9+K7

The formulas above contain a hardcoded value and its cells that contain this type of formula I want to automatically select without selecting every formula in the worsksheet.

Hope People can help

Best Regards

Anjohn

Am looking for a way to transform a list of constant values by wrapping a formula around them.

In this case, the list of values are all in column G.

I would like a macro that can add the following to each cell: "="..."+$G$33".

So if the original value in column G was "100%" (constant value), then the result of the macro would be "=100%+$G$33" (formula that includes the original constant value).

It would do this for every value in column G.

Can anyone help? As I said, I did ask about this last night in a different way by replying to an older thread that seemed to have a similar theme. I have not received reply yet but I think this is different enough from the original thread that I should restate it in a new thread anyway.

Hope this is OK? Let me know if can help, this would be very helpful for me in many ways!

Can someone tell me how to use the "Large" function in a pivot table? or is there a better way to get the 2nd largest value? Auto Merged Post;

Bummer! No answers yet. I thought there might be some experts out there that would have a really tricky way of doing this.

From the Excel help it looks like this isn't possible:

" In formulas you create for calculated fields and calculated items, you can use operators and expressions as you do in other worksheet formulas. You can use constants and refer to data from the report, but you cannot use cell references or defined names. You cannot use worksheet functions that require cell references or defined names as arguments, and you cannot use array functions."

I think I may end up adding a column to the original spreadsheet to create the 2nd largest value and use it in the pivot table.

Other ideas welcome!

what i need to do is.....

i have a value in cell C2 (which i want to keep constant), now i have another value in cell D5 and E5 and the final result in F5.

i want to keep the value of cell C2 constant.

now, i want to multiply the value of cell D5 with cell C2 and than add the value of cell E5 which could give me the result in cell F2. (see the attachment for details)

in cell F5 i m placing the formula =+((C2*D5)+E5)

but when i drag the formula downwards, the cell C2 changes to C3, C4, C5, etc. means like this; =+((C2*D6)+E6); =+((C2*D7)+E7).

I know i can do it manually but i have almost 500 rows to work with and this aint easy at all.

how can i keep the cell C2 constant in the formula but let the cell D5 change to D6, D7, D8, etc and E5 to E6, E7, E8, etc.

any help is appreciable. sorry for my bad english.

One of my calculation cells from Column G has the following formula in it

Column G (example) =IF(F5>0,(S5-F5)*10000,"")

Column R adds up all the calculation cell for 2 rows :

Column R (example) =SUM(G5,I5,K5,M5,O5,Q5,G6,I6,K6,M6,O6,Q6)

Now because Column S is constantly changing, i would like to add another Column X to the worksheet which will capture the present value in range (active row selection & column R) and then return that value as a fixed value in column X.

Perhaps i will just put a button on the sheet so that a user has to just click on the relevant row and then hit the button which will fire a macro for the above or something.

Would anybody be able to help me with this please?

Thanks in advance...

of variable values in cells A2 to A40 which i then want to calculate which I

can do however i can not get the formula to auto calc when copying the cells

it give me the wrong values. ANy help please by direct reply to phill

costello at

the cell value to a constant. The value in the cells are number inputs not

results of formulas.

then I have a constant value in A20. I then created a formula A1*A20 in cell

B1, and I would like to drap this formula down so that I get the following

result A2*A20, A3*A20...but when I drag the formula it gives me A2*A21,

A3*A22, A4*A23...Somebody please help!!!

of variable values in cells A2 to A40 which i then want to calculate which I

can do however i can not get the formula to auto calc when copying the cells

it give me the wrong values. ANy help please by direct reply to phill

costello at pcostello@aopa.com.au

My data is something like this:

Column1 Column2 Flag

1 200 0

2 300 0

4 500 1

6 400 0

7 300 0

9 600 0

I need a formula to add values in each of the first two columns till

the flag equals 1. Then another formula to add the values below the

flag seperately.

In other words- I am expecting 7, 22, 1000, 1300 values to be output

for the above sheet.

The number of rows is not constant in my worksheet although the order

of the data is standard. I would want to avoid VBA if I can do this by

a simple formula.

Please help. This is Urgent.

Thanks in advance.

-Naveen

[and the row number is always greater than 1]

Any help on this would be soooo helpful, i'm at wits end

Items are organized by category, and subsequently items values are calculated by the count and the value. The spreadsheet is constantly changing with items added to categories.

I have a few things I need to do, and cant seem to do without manually changing all formulas each time an item is added.

1) Based on Category I would like to add the negative and positive values separately and display in another cell. For example

IF Column A = 'Stationary' then SUM negative figures in Column E

IF Column A = 'Stationary' then SUM positive figures in Column E

2) Secondly I would like to add together multiple figures based on the item names. For example

If Column B = "Twix", "Mars Bar", "Coke" Then SUM corresponding values in Column F

I hope that makes sense been struggling with it for a while and I'm not sure if I can just use formulas or I need some advanced coding. Any help would be greatly appreciated!

Thanks :D

I am looking in a formula which will return a value in the adjacent cell in column a.

The value in column a will be a number plus 1 of the value above it.

I only need this value to be visible though if there is a value in the adjacent column b cell.

For instance

A2 = 1 B2 = Value

A3 = 2 B3 = Value

A4 = Blank B4 = Blank

The values in column B are not constant and everyone will be different.

I have attached a, er, basic workbook.

I know my request may seem strange but if a solution is found it will be very useful for people having the same of my situation.

I am gonna make it simple to explain.

I have a workbook that holds guests requests of a hotel, it consists of 31 sheets (a complete month) each sheet/each day will contain in Column A the different guests requests up to 100 requests with thier relevant values in other columns (time, status, duration…etc)

The last sheet is called Monthly Summary that has formulae applied on all the days.

As you know, the maximum number of requests throughout the month can be 31*100=3100 requests, so there are 3 columns in this Monthly Summary sheet hold formulae that do calculations based on the above-mentioned relevant values in each day, I had to drag down these formulae till the cell 3000 to cover to the maximum number of requests throughout the month but in the other hand this caused the Excel file to have a very large size and causing delay in opening and saving (charts and macro also added as well).

I tried to reduce the filled cells with formulae, and indeed the Excel file size has dropped significantly.

My question, is there any way or a Macro VB that can count the number of requests in each day and based on that drag down or fill the formulae in these 3 column, in this case the file size will get larger based on the number of requests.

It is important to note that in case what I am asking can be done, the formulae are dynamic and changeable according to the cells, we are not filling a constant values.

I hope I could make clear.

I uploaded the file so other people can benefit from it and can also have a clearer look on it for a better help and contribution.

The zip file contains two files, the original and the reduced size one (big difference you can see)

You will notice in the reduced size file that I deleted rows (101 till 3102) in the "Delayed" sheet in the Column A, B, C

These are the column that I want their rows to expand automatically starting from the cell 101 and get filled automatically with the formulae.

The expansion number is based on the total orders in each day; you can benefit from the total orders sum I have put in the end of each day.

It is on this link http://www.mediafire.com/?28ca2n7pvdfb32q

Thanks in advance for your help.

I have a question about creating an automated way to calculate formulas based on various combinations of data points. (I do not have much experience dealing with codes and macros etc. But am willing to research and learn if someone can be nice enough to point me in the right direction/confirm that such a task is possible in Excel.)

Here is the simple formula I need to calculate:

Calculation = (Value from List A)*(Fixed Number A)+ (Value from List B)*(Fixed Number B) + (Value from List C)*(Fixed Number C)

The data I will dealing with:

List A

A1

A2

A3

A4

A5

etc.

List B

B1

B2

B3

B4

B5

etc.

List C

C1

C2

C3

C4

C5

etc.

And:

Fixed Number A

Fixed Number B

Fixed Number C

(which will be constant for every variation of the formula)

For example, given the above example, where there are 5 values in each list, that would mean that there are 5 * 5 * 5 = 125 possible combinations of these values that can be inputted into the above formula (if I remember anything from statistics class!). The problem is that I anticipate having enough values in the lists so that there could 1000+ combinations for {List A Value, List B Value, List C Value} - and the values in the lists may change in the future - so I am trying to figure out if it is possible to create something dynamic that would calculate this formula for every possible combination I have presented in these lists.

Any suggestions would be most appreciated - Thanks!

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