Run Formula only if Cell has certain Criteria

[karobonas]

Hi,

I have a s/s and would like certain forumla's to display in certain cells contain the criteria in order for the formula to run.

i.e. If a date is displayed (any date) in Cell D106 (this could alter as the data is dynamic, it could be D107 in another instance).,I would like a forumal to automatically run in cell O106 (If the data is in D107 then the corropsonding O107 would display the forumla).

Currently, When I copy and paste the formula over multiple cells, the forumla displays in every cell and sometimes does not work as there isn't a pattern of the same data in the correct cells for the forumla to run. Please see attached screen shot for better illustration.

Any help is much appreciated,

[royUK]

Maybe

If(ISBLANK(A1),"", formula)

[NBVC]

Preface the main formula with a condition to check if the cell in D is blank.

e.g =IF(D106="","",your_formula).

[karobonas]

Preface the main formula with a condition to check if the cell in D is blank.

e.g =IF(D106="","",your_formula).

Hi,

thank you for your quick response....

=IF(D106="","",your_formula). did work although only for one of my formula's. I have other formula's below that I applied your code to but these failed to copy in the same way due to the corropsonding cells not necessarily containing the information (as the data can move around).... is there anyway to apply the formula so I can mass copy them through the spreadsheet.... also, I may be addition some more formula to the s/s so may need to do these same with this.

Thanks again for your quick reply, much appreciated.

Regards

[royUK]

You probably need to read up on Absolute & Relative references

[NBVC]

You would have to apply it to one cell, and copy the formula down/up/left/right, as long as there is consistency in adjacent formula... otherwise you can not really mass update.

If you attach actual workbook, instead of picture, it would be easier to see why the formula does not work in other cells as you claim.

[karobonas]

You would have to apply it to one cell, and copy the formula down/up/left/right, as long as there is consistency in adjacent formula... otherwise you can not really mass update.

If you attach actual workbook, instead of picture, it would be easier to see why the formula does not work in other cells as you claim.

Hi,

Please see attched work book... Please navigate to the 'Resource Planning'.

Thanks again for your help.

[NBVC]

Where is the issue?

[karobonas]

Where is the issue?

Hi,

apologies - Under the field "Duration of Project" I have a forumla that dispalys the duration of a project. Underneath that is a formula (not obvious) that displays a numeric value dependant on "day priority" and beneath that another formula that displays a numeric value based on number of weeks using the Start Data and End Date fields.

The data comes from a pivot table.... Dependent on the values in the data, the cells may expand or decrease meaning that the cell references in the formula may alter. I only want the formula's to display in the adjacent fields - Duration y,m,d next to the End Date, Numeric percentage next to Day Prority and no. of weeks Next to Sal Forecast Effort But all results are to be placed under the 'Duration of Project' column.... If I attempt to copy this format through all the fields.... It does not copy correctly due to the distance between the last entry and the next one either decreasing or increasing in cell's (i.e. 7 between the end date of Andy Otley and the next entry under Andy Otley and 8 cells between the last entry of Andy Otley and the next entry of Charles Plesh).

I would like the formula to be consistant with the data that it looks at and be placed in the corropsonding fields listed above, if this is possible?

[NBVC]

I am not sure if this is what you mean?

Try this formula in O7:

Please Login or Register  to view this content.

copied down the whole column.

Is that it?

[karobonas]

I am not sure if this is what you mean?

Try this formula in O7:

Please Login or Register  to view this content.

copied down the whole column.

Is that it?

Thats it!!! perfect. Thank you ever so much.

just for future reference, if I wanted to add more formula to the cells below... how would I encorporate this into your formula above?

Thank you again for your help.

[NBVC]

Add another Nested IF before the last double quote combo ( "" ).... but note that in Excel 2003 or earlier, you are only allowed up to 7 nested function combinations in a formula.

If you have more, you will need to look at alternative ways to construct the formula or move to XL2007.

[karobonas]

Add another Nested IF before the last double quote combo ( "" ).... but note that in Excel 2003 or earlier, you are only allowed up to 7 nested function combinations in a formula.

If you have more, you will need to look at alternative ways to construct the formula or move to XL2007.

Ok,

Thank you again.

Kind Regards,

KB