Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If specific cell is not empty, copy (or enter) formulas and format
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If specific cell is not empty, copy (or enter) formulas andformat
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If specific cell is not empty, copy (or enter) formulas and fo
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 $10:D10)))} 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? "Wouter HM" wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If the cell is empty, copy the value from a specific cell. | Excel Programming | |||
Indirect formulas in cells not holding cell format after copy | Excel Worksheet Functions | |||
Enter data and press enter to move to specific cell | Excel Programming | |||
Enter data in next empty row after cell name | Excel Programming | |||
How do I format a cell so that only specific numbers can be enter. | Excel Discussion (Misc queries) |