If specific cell is not empty, copy (or enter) formulas and format

[Stacie2410]

I have a workbook that has several sheets that refer to each other with
formulas throughout.

The first sheet is a block of data where I enter data into columns E, G, H,
P, R, S, T, U and Y. Columns A, B, C, D, F, I, J, K, L, M, N, O, V, W and
X all have formulas in them (many are VLOOKUP and some are calculations).
These cells all have a certain format as well (that differs from column to
column), such as date format, number format (some are percentages, some are
decimals with 8 decimal points, etc), conditional formatting differs for each
row, etc.

That being said, I need all of the formulas and formats to continue down the
columns indefinitely so that when i go to enter data into my data entry
columns, the cells with formulas react properly. I did have the formulas
entered down about 100 rows, however, if there are formulas in rows where
there's no data yet, my COUNTA function from a different sheet within the
workbook doesn't work. The only thing I could do to make that function work
properly was to delete the next available rows data so that the next cell was
empty.

I'm wondering if there's a way to have excel automatically enter formulas in
the appropriate cells if column E of that row contains a value.

Any help is appreciated tremendously!

 

[Wouter HM]

Hi Stacie,

What yo u coulf try is making your formulas a bit clever, if you did
not do so.
Let us say you have in your A column a formula

=myFormula(E2,G2,H2)

Ic cound be

=IF(ISEMPTY(E2),"";myFormula(E2,G2,H2))

A COUNT on column A will not include those rows whithout a value in
the E column, since the COUNT formula only includes numeric values.


HTH,

Woute

 

[Stacie2410]

Actually, I apologize, my formula on the different sheet is not a COUNTA
function (it was previously, but I had to change it). It is an array
function. I've posted it below. When I have a formula in Column A on the
first sheet on the next available row, this formula (below, from the separate
sheet) no longer works.

{=INDEX('Marketing
Letters'!B:B,SMALL(IF(Data=$D$10,ROW(Data)),ROWS(D$1010)))}

Is there a way to make the first sheet respond as such:

When data is entered in the next available row in column E, that the correct
formulas and formatting are entered and applied to the cells in the rest of
the row?