Free Microsoft Excel 2013 Quick Reference

Constant value in formula Results

I havent used excel in a while and I'm using $ symbol (as a formula) somehow wrong...

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

The below formula when viewed in 'Formula Auditor' refers to '(L4)' as a
'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'!


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:


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


Hello, my last post here was a reply to an older thread, asking for similar help, but I think perhaps this is different enough that it perhaps needs its own thread?

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!

I have a spreadsheet with items and their maintenance dates. Each item may be listed multiple times with various dates. I want to create a pivot table that will show the most recent and previous maintenance date. The most recent is easy by using the built in "max of" in the Pivot table Wizard.

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!

hyy all,

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.

Hi there - i have cells in ("column S") which collects live data from a program via DDE.

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

in excel i have a value in C1 which is my 'constant' value and i have a list
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

I would like to change the text color or cell color based on a comparison of
the cell value to a constant. The value in the cells are number inputs not
results of formulas.

I have created a spreadsheet in there are values in A1-A10, D1-D10, etc. ,
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!!!

in excel i have a value in C1 which is my 'constant' value and i have a list
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

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.


In a formula, how can I reference the first value in a column of numbers when the row number of that first number is not constant [ie] the row number changes regularly from day to day as I insert new rows at the top of the column each day?
[and the row number is always greater than 1]

I am trying to create a formula to quicken up my works expense sheet. there are several instances where I have to write the expense detail in column B then put the expense value in column F.the value of certain expenses is always the same. Can anyone please tell me how to create a formula so that if I enter "road toll" in B a value of "5" will appear in the corresponding row of the F column?

I'm assuming I need some sort of VBA code for this as I have tried various different combinations of IF, MATCH, ADDRESS, COUNTIF, OFFSET formulas with no avail. I'm looking for a way to automatically display the value in the quantity sold column if the product code 52197 exists in a row and for it to automatically fill in the value for each corresponding user ID. The data in sheet 1 constantly changes as it is imported from a daily sales report. This is only a small sample of the report, so i'm looking for excel to sift through the data and return the number sold of product ID 52197 and return a 0 if that ID hasn't sold any. The problem with the MATCH route is that it only accounts for the first instance of the product ID and will only return a value for the first user ID and not the rest.

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

Attached is a spreadsheet.

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

Say for instance I have column b which has values in its cells.

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 have built a huge spreadsheet and would like to format the constant values in bold blue to differentiate them from the cells containing the results of a formula in order to facilitate my audit and debugging efforts (and to avoid others inadvertantly changing a formula to a value). Any way to do this?


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

Thanks in advance for your help.

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

List B

List C

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.