Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Make Formula Cell Appear Empty Until Data Is Entered

I'm creating a "universal-fluctuating" vendor inventory return worksheet for a auto parts store that consist of one criteria (cores, warranties, or N/R ) and will return one or two results of core cost and/or unit cost. This part of the task I have accomplished by using a drop down list for my criteria and my results will appear in two different columns using a Vlookup table. The problem is due to inventory fluctuating from cores and waranties on a month by month basis, vendor requiremnts differ for the number of units returned, and last make the boss happy on ink and papers supplies :D I was wondering if it is possible loop my code in a given column where it will move my code to the next row untill I reach a grand total?

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Make Formula Cells Empty Rather Than Empty Text
Is it possible to make a cell "really" blank/empty based on an If statement? For instance:


Has a value_if_false of "". But Excel interprets this a bit differently than a cell that never had anything typed into it.

So if you have a column full of this formula copied down, and hit <control+down arrow>, you will go straight to the bottom and skip over all rows. Whereas if you have a column with values and empty cells alternating and hit <control+down arrow>, you will only skip the empty cells and go to the next value. Excel treats the conditionally empty cells as if they have a value, when it comes to this type of navigation. This holds even if you copy and paste "Values" for the cells over the formulas.

Is there any way to tell Excel to make the cells truly empty?

View Replies!   View Related
How To Make Sure That Only Positive Numbers Are Entered In A Cell
I am trying to restrict data input to a cell for only positive numbers. Currently, I have used data validation to accept decimals and numbers greater than zero. The cell is formatted for percentage with 2 decimal places. The "error message" alerts you that you have to have positive numbers only. The catch is that when the error message comes up and you use the 'retry' button, the entry is multiplied by 100.

View Replies!   View Related
Make Vlookup Move On From An Empty Cell?
Hello there,

I am making a spreadsheet that tracks children's progress in school through the year.

I have a vlookup that turns their levels (2c,2b,2a,3c,3b,3a etc) into numbers so that the number of sublevels they have progressed during the year can be reported.


However, when children join through the year, they will not have data to go in the 'starting grade' box, so I was wondering if it would be possible to make vlookup see that cell E5 is empty, and so look in cell F5 instead. It might even, perhaps, have to look in cell G5 if they join the class after Christmas.

View Replies!   View Related
Is A Cell With A Formula Considered True Or Is It Empty If The Criteria Is Empty
Is a Cell with a formula (like shown below) considered true, or is it empty?

If Scorecard!$B$13 was False...
Would a cell with the above formula be considered?
True or Empty?

If Scorecard!$B$13 was True...
A cell with the above formula would be True.

View Replies!   View Related
Add Formula Dynamically Up To The End Of Data Entered
Below is the code. It seems to be creating 50 thousand rows below the data already entered in the worksheet "DELPHI DATA". What I need to do is change it to only add those formulae or pasted values to as many rows as already have data entered in them (which may eventually approach 50000, but may remain at only a few thousand.)

Sub Refresh_Current_Month() ...

View Replies!   View Related
Change Formula When Data Entered
There are two worksheets: Finances and Summary. On Finances, there is data input for years, quarterly. There is a cell on Summary that depends on which year is input first, in which case the cells in the formula SUM("cell1:cell4"))/4 is currently changed manually by the user by just checking to see which year data is input first on Finances. I need a macro or a formula function where the workbook finds which year is being used on Finances first, then changes the cells in the Summary formula accordingly.

View Replies!   View Related
Formula: Show Nothing Until Data Entered
I am trying to create a simple IF/THEN statement to display profit margin for an order form. I currently have the margin formula set at (1-H14/I14). How should I structure my IF/THEN, to where it displays nothing in the cells, that are empty?

View Replies!   View Related
Copy Formula From Previous Row When Data Is Entered
This is just a sample worksheet. I have got a worksheet with having 3 coloumns A, B & C. Column A contains E Code, while Column B is of time which user will enter. Column C contains the time in Hours.
I have entered one record for example. Now, whenever user enters the value in B3, then formula from C2 should be copied to C3 i.e it should be =B3/60.

I want this to be done using VBA. Pls help me out. I want to use this feature in one of my another files which requries this feature.

View Replies!   View Related
Extend Formula Automatically As Data Entered
I found the following code here.

For data entered into column A, it copies the formulas from columns B:E in the row above to the current row.

It works great except fot the first row (A9) where it copies the header row (B8:E8).

How can I get it to not copy when data is entered into A9?

Private Sub Worksheet_Change(ByVal Target As Excel. Range)
Dim c As Range, i As Long
On Error Resume Next
Set c = Intersect(Target, Columns(1))
If c Is Nothing Then Exit Sub
If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then Exit Sub
i = c.Row
Application.EnableEvents = False
Range("B" & i - 1 & ":E" & i - 1).Copy Range("B" & i & ":E" & i)
Application.EnableEvents = True
On Error Goto 0
End Sub

View Replies!   View Related
Combo Box With A Yes Or No Drop But How Do You Make The Y A Default Always Entered
How do you make a Yes No combo box always have the default answer as Yes appearing on a user form but still allow the user to change it to NO?

View Replies!   View Related
Fields That Containa Formula Need To Appear Blank Until Data Entered
i have two date date requested eg 02/06/09 (cell C10)...another date completed eg 03/06/09 (cell R10)

a third field (cell S10) contains the formula: =IF(R10-C10=0,"less than a day",R10-C10)

so if a request was actioned on the day then it shows as less than a day, otherwise will show how many days it took

but when this formula is draggeddown all other cells show - less than a day

how can i make these cells blank whilst still holding the formula?

also - is there a quick formula to add to show only the amount of wrking days a request took to complete?

View Replies!   View Related
Make Positive Entered Numbers Appear As Negative For Budget
I am trying to create a budget in excel and want to type my numbers (in certain cells only) without a - sign but I want them to appear, and calculate, as negative numbers. Basically, I am lazy and don't want to have to remember to use a - for all of my expenses when I am inputting the data.

View Replies!   View Related
Referencing A Sheet Name Entered In A Cell For Formula
I am working on a sheet called 2005, with "Jan" entered into cell C4. I want to reference cells on the sheet called Jan2005 using a function that points to C4 and adds "2005".

View Replies!   View Related
Automatically Lock / Protect Formula Cell When Entered
I need to create a macro to where once a cell has had a formula or data inputed that it locks afterwards and cannot be edited without unportecting the sheet or not at all. The problem is I have no idea how to program in VBA. I can get there and select the worksheet but after that I am stumped. How would I enter the macro and what should the macro look like?

View Replies!   View Related
Locking Cell Dependent On Data Entered..
In cell D10, there is a drop down menu with choices A, B, C and D. Cells D13, 16, 19 and 22 also have a drop down menu. What I would like the code to do, is when option D is chosen from D10's menu, cells D13, 16, 19 and 22 are locked so that the user cannot choose an option from any of those cells.

So basically, if D10="OptionD" then [whatever the value is to lock cells D13, 16, 19 and 22 from drop down use)

If possible, I'd like this as an IF function, and not a VBA macro. I'm not completely familiar with all of the IF, so forgive me if I ask the impossible. But if the IF cannot do it, VBA code is fine.

View Replies!   View Related
Locking Cell Dependent On Data Entered
I'm working in MS Office Excel 2003. I have a spreadsheet with numerous drop down boxes and references to named ranges. Here's my current quandary:

In cell I12, I have a drop down box with listed choices A, B, C and D. In cells I15, I18, I21 and I24, I also have drop downs.

What I'm trying to do is when choice D is picked from I12 it locks cells I15, I18, I21 and I24 so that the choices in the drop down cannot be picked. Also, a popup message is displayed.

I was able to receive help in getting this pretty much taken care of. But the problem I'm having with the code is that the popup message is also being displayed whenever I make a choice from other drop down boxes. So if I choose option D from I12, it locks the other 4 cells and displays the message just fine, but then if I choose anything else from say K12 or L21 then the message continues to come up. Here's what I have...

View Replies!   View Related
VB To Activate List Drop Down Boxes When Data Is Entered In A Cell
I have a Validation drop down box (column B) and a defined range drop down box (column E linked to the Totals tab with A2-A31 defined as Products) that I need to activate everytime data is added to a new line in column 'A' and cells in column 'B' or 'E' are selected. Additionally, they need to activate if the cells in column 'B' or 'E' are selected for any of the old data so changes can be made. This will help reduce the overall file size and calculation time for a year's worth of data.

There is already some code on the worksheet that looks like it can be adapted if someone knows how to code in the drop down boxes.

View Replies!   View Related
Make A Cell's Values Become A Formula
I have on my Sheet named "Data" in Cell K4


The values of that cell become a formula.

I try to make a macro that pastes 'Data'!K4 into 'Data'!L3 and then have that formula functioning in cell 'Sheet1'!A31.

The problem is when I try to make a macro to do this it will always paste the values that were recorded during the macro rather than the unique formula that is created via cell 'Data'!K4 at the time.

Is there any way to have A31 actively using the values that are created with 'Data'!K4? at all times?

View Replies!   View Related
Formula To Make Cell Blank Instead Of Zero
I am looking for a formula that will work with my current formula, and check that cells a1 and b1 contains information. If they do contain info then the original formula should carry on as usual and leave the answer in cell c1. I also need it so when no information has been entered in cells A1 and B1, C1 will be blank instead of containing a zero.

This is the formula I have at the moment:
=IF(SUM(A1=0,B1>0),B1-A1,"No Charge?")


View Replies!   View Related
Make This If Formula Return A Cell Value
Heres my current formula but its justreturning text i would like it to return the cells value


View Replies!   View Related
Can I Make A Formula That Will Highlight A Cell
I have a large spreadsheet with many pieces of equipment in it. There are a few piece that I would like to highlight should thier computer license go out of date, or even better 30 days prior. I have a Feild with the Expire dates but it is at the end of the spreadsheet not in plain sight. Is there a way to make my Equipment description column highlight when the date in the other column reaches the current date (or 30 Days before). I have cut and pasted the part of the spreadsheet into a new file to make it easier to read and hid all the none important feilds.

View Replies!   View Related
Nested IF AND Formula: If Cells K8 And L8 And R8 Are Empty, Then No Data Should Show
i am having trouble putting together an IF Formula together with and/or. i need to do the following

if cells k8 and l8 and r8 are empty, then no data should show.
if cells k8 and l8 and r8 is zero, then show zero.
otherwise add all three cells.
i thought i should use if(and... that is all 3 cells must be empty or zero.

=IF(OR(ISBLANK(K8),ISBLANK(L8),ISBLANK(R8)), "no data", IF(OR(K8=0, L8=0, R8=0),"ZERO", K8+L8+R8))

i have tried if(and) and if(or) and no matter what i have tried it doesnt work

View Replies!   View Related
Macro Create A Hyper Link Once Data Is Entered Into Specific Cell
Is it possible to have a macro create a hyper link once data is entered into specific cell, i have a multi sheet work book used as a stock card system.

Each stock item has its own worksheet and the product code is entered in to cell E3
(which in turn has a macro in it to change the sheet name to match the product code).

the macro also inserts a row of info into row 5 of another sheet used as a summary sheet ( CONTROL.SHEET or SHEET 4) using copy and paste link.

i need a macro to generate a hyperlink in cell D4 of the control sheet between the row info and the matching product code sheet ( the product code is displayed in cell E4 of the control sheet.

I only input one item at a time and then manually cut and insert the row info into the stock list on control sheet. so every time i input a new item, the row info is always in row 5 of the control sheet.

View Replies!   View Related
Having An Empty Cell When No Formula Is Being Used
I am learning how to create functions. All is going well, except when I auto fill for future input, the cells fill will all kinds of N/A or Null stuff etc.. I was trying to figure out how to do an IF statement like in my example below, that when the cell of reference is empty, keep it empty...but it is not working. take a look at it and tell me what I am doing wrong? I am using a grading system example...when a grade is put in, the corresponding letter grade fills, but when no grade value is entered, I want to keep the cells empty.


View Replies!   View Related
Make Part Of A Cell Bold Using A Formula??
I have a formula that combines the the text of various cells.

Is there a way to automatically make part of the cell Bold - so in this case I want the Cell B13 to appear in bold.

See formula below:

View Replies!   View Related
Make Formula Pick Up Filename From A Different Cell...
I have a formula that looks like this : ='G:STOCKS[1001 Stock.xls]Matl%'!$I$32

There are hundreds of similar formulas, all referencing different cells in the same file. The 1001 bit refers to the date, and next month it will need to say 1002 to pick up the new month's file. I would like to hold the 1002 bit in a cell (say A1), so that at month end, I just change that one cell, and all the formulas will point to the new file, but Im not sure how to work that into the formula.

View Replies!   View Related
Make A Formula Output A Blank Cell?
I want my formula to output a blank cell if the answer is 0. Ex. [B1=IF(A1=0, ???, A1)]. I want B1 = A1 if A1 is not 0. I want B1 to output a blank cell if A1 = 0.

View Replies!   View Related
Running Totals In The Cell As The Previous Week And So On Until New Data Is Entered And Updates The Total
I have to keep a record of the running totals of school house points for each week. The problem is that teachers are very lazy and don't record data every week so I have many blank cells which my current formula can't cope with. I've tried using N/A but it doesnt seem to work? (Have thought about threats of violence but would probably lose my job) Im if there is no data (blank or 0) then I need it to keep the same total in the cell as the previous week and so on until new data is entered and updates the total. I have attached a simplified copy: Teachers enter points in the HP sheet, the Running Totals sheet (TAB) contains the formula.

View Replies!   View Related
Run-time Error 1004 (founds An Empty Cell The Value For One Cell Is Copied To The Empty Cell)
I have a workbook with over 900 worksheets.

The macro I have is looping all sheets looking for empty cells in a specific column, and when it founds an empty cell the value for one cell is copied to the empty cell.

But in one worksheet it stops with the error:

Run-time error '1004'
Application-defined or object-defined error

View Replies!   View Related
Leave Cell Empty Even When There Is A Formula In It
Its probably very simple question, but i can not work it out.

I am trying to design a simple worksheet so I can keep track of how much money my room mate has paid me and what she owes. She pays $142 a week rent plus $23 a month for internet.

Please open the attached workbook. You can see in E7 and 8 there is a red negative number. This is because my tenant hasn't paid me yet for those weeks (D7 & 8).

How can i get these cells to read $0 or leave the cell empty?

The reason why I need this to read $0 or nothing is because it is messing up my next calculation in D3 where it works out how much she has paid for our internet bill.

Thanking you in advance. And sorry if this is a really daft question. Its probably something really simple i have missed.

View Replies!   View Related
Find Empty Cell And Add Formula
I have a spreadsheet that is pulled automatically from an access database.
The information that is pulled is invoices that have been paid for each of the temps working for our company.
Each month I have to give a figure per cost code on how much they are accruing so that they can see how much is still outstanding.

Right so what I am trying to do is have a macro take the temps charge rate then * 40 hours and place the amount into the empty cell.
As you will see in the attachement some of the cells are coloured. The purpose of the colouring is show that a invoice has been processed and paid for that week so no value is needed in that cell.

Then total all the cells at the end.

View Replies!   View Related
Making A Cell With A Formula Appear Empty
I have a chart with blank values in some cases. I have set up the chart to show those values as gaps, which relies on the blank cell being empty. However, my blank cells have a formula along the lines of: if (condition,value,"")

So the chart does not consider them blank, presumably either because the formula is in there, or because "" is not the same as blank. How can I make this cell appear blank for the purposes of the chart?

View Replies!   View Related
Convert Blank Formula Cell To Empty Cell
I am using a IF(ISNA(Vlookup##,##,##)),"",(Vlookup##,##,##)) function, in order to remove N/A errors. Is there anyway to convert the "" values to back to an empty cell without anything in it (i.e not text "", but empty as it was originally). I find that the file sizes are very large, when using this function, as data (although blank) is stored in each cell (i know this, because if I use cntr+arrow, it sees it as data, and not an empty area). I don't want to manually go through each cell and delete them as there is quite a bit of data (10000 rows , 2 columns, 30 tabs)

View Replies!   View Related
Ending Loop When Cell Is Empty But Has Formula In It
I have a do while loop that runs until the reference cell is empty. This has been working but now I have changed the reference cells somewhat so that the cells contain a formula that returns nothing if a function returns 0 or an error. So the cell has no value but it does have a formula in it. The do while loop keeps running because it thinks that the cell has something it it.

Do While Cells(i, 2) ""

Is there something else that i can put for "" to get the loop to stop when cell contains no value but a formula.

View Replies!   View Related
Hiding Answer To Formula If One Cell Is Empty
Is there a way to have the answer to a formula display nothing if one of the cells have nothing. Example cell A3= A1*A2 ... it will naturally show 0.00 but even if both A1 and A2 are completely blank. Is it possible for A3 to show nothing if A1 or A2 or both contain nothing. I still want it to show 0.00 but only if thats what is in the other cells.

View Replies!   View Related
Formula Is Entering A Default Time When It Comes Across An Empty Cell
I'm using a formula to copy a time from one cell to another
across sheets. The format of the time is h:mm AM/PM.

However, when the formula references an empty cell, it puts in a
default value of 12:00 AM and I need it to remain blank, (just as
the referenced cell)
It's such a simple copy formula. ie:


e-mail... howard<dot}coakleyatcoakley<dot].codotuk
Skype ID: howie10 (get skype from

View Replies!   View Related
How Do I Make One Master Cell For Data Entry
How do I setup one "master cell" to be used as the permanent entry point for dollar values of all entries performed as needed....and have that value be automatically added to the cell of the month reflecting the day the data was entered?

I am looking to keep track of purchases on a monthly basis. (e.g. I have 35 receipts for today...I just want to grab one at a time, enter the total for each receipt in one cell and have it add the value to which ever month's value based on the day I enter it.

View Replies!   View Related
Make Button Inactive Until There Is Data In A Certain Cell
I have a button that has a macro asigned to it. i don't want it to be active
or able to run unless it sees data in cell D25.

View Replies!   View Related
Sum Empty Cell Will The Above Data
vba macro, I've a s/s where column "W" is named "Proceeds" column and i would like to sum empty cell with above data. see below raw data:





Now i would like to sum empty cell & colour background with yellow and borders with top and thick bottom border.

View Replies!   View Related
VBA Deleting Data Until Empty Cell
I have searched around and cannot find exactly what im looking for. I have a table of data and want to delete it all by clicking a button. I only want it to delete the rows which have data in.

A1 = Apple
A2 = Apple1
A3 = Apple2
A4 = Apple3
A5 = *Empty*

So it would delete A1-A4 and not delete A5.

View Replies!   View Related
If No Data, Then Show Empty Cell
I'm using Excel 2003 and have a main workbook which stores the quotes, invoice, worksheet and contact details. In order to email only the invoice to a customer I have created another workbook which replicates whatever is shown in the open invoice tab of the main workbook. A macro from the main workbook then opens outlook.

I attach a sample of both for information. My problem is this - You will note that on the email invoice that any entries where there is no cost in the main workbook, that it shows " -". how to get it to show an empty cell if no value in the main workbook?

View Replies!   View Related
If A Cell Is Empty Then Enter Data From Another?
I have an excel sheet with a few columns, Column (1) has data entered for ever row (with a name.) Column (2) has a few random cells with data (names) about 30% and the rest are left blank. What I need, is to have a formula in a new column (3) that will put in the the data (names) of column (2) and then if there isnt data in column (2) then it will put the Data (names) from column (1). (ps, If i cant get this figured out, we have to buy a custom interface and it will cost around $7000,)

View Replies!   View Related
Fill An Empty Cell With First Data Cell In The Same Column, Next Row(s) Down
I am looking for advice on how to move cells containing data (columns D to K in EXAMPLE A), upwards so that the information lines up with data already existing in columns A to C (to be included as part of an overall VBA routine).

In the upper example data in cell D2 needs to move upto D1, E3 to E1, F4 to F1, G7 to G1 etc and K9 to K1. Now, data on following rows is to move upto row 2 eg: E5 to E2, F6 to F2, G8 to G2.

The first 'block' of data starts at row 1 and finishes in this case at row 9.
The next 'block' of data starts at row 10 and finishes at row 18.
Row 19 shown is the start of the next 'block'. These 'blocks' may have upto 20 rows ....

View Replies!   View Related
If No Data, Then Show Empty Cell Using Conditions
I'm using Excel 2002 and am having trouble with what I thought was a simple conditional command. On part of the spreadsheet I have 3 columns: Hours (D5), Cost Per Hour (E5) and Total Cost (F5). Here's what I'm trying to do.....

If there is no value in the Hours then the Total Cost should show an empty cell and conversly if there is a value in Hours then calculate Hours*Cost Per Hour in the Total Cost cell. My condition for cell F5 is as follows: =IF(D5="","",SUM(D5*E5)). Whilst it works if there is a value in D5, when empty F5 shows #VALUE!

View Replies!   View Related
Prevent Empty Cell With Data Validation
I'm looking for a formula which will enable typing (in cell A1) positive numbers between 0-1000000 but will prevent the user from leaving that cell in an empty state. Empty cell means: cell with space(s) or deleting the legitimate value that is, already, in it).

I must emphasize that I know how to handle this by VBA code and/or by using a "named" pre-defined list of values + un-checking the "ignore empty cell" option - but I would like to know, once and for all, the solution WITHOUT using Macro and/or 'List' (if any..)

I tried that, without success:


View Replies!   View Related
Loop Checking And Moving Data If Cell Is Not Empty
in writing a loop that will check a number of cells to see if it is emtpy, if it is not, then run the macro. If the cells are not empty it will copy the data in that row and paste it to another sheet and delete that line. If it hits a cell that is empty, i want it to skip that row and move on to the next row.

here is the macro that moves the data.
Sub movedata()

Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Selection.Insert Shift:=xlDown
End Sub

View Replies!   View Related
Find First Empty Cell In Range And Copy Data
I am looking for a formula function or a vba code where:

- In workbook1 find the first cell that is empty between range A7 -
- In workbook2, in Range G10- G13: find the word "Day1".
- If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy
the particular cell or cells where "Day1" exists to the first found
empty cell or cells in range A7-A10 in workbook1.

Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means
A8 is the first empty cell.
And G10,G11,G12, G13 (workbook2) have the word "Day1"
Copy cell G10 into cell A8
Copy cell G11 into cell A9
Copy cell G12 into cell A10

View Replies!   View Related
Paste Data To Next Empty Row Based On Cell Contents
I am trying to write a macro to do the following: Loop through a range of cells on a worksheet Sheet1 M17:M46 and if there is text in the cell then Copy all the data to the left Of Coumun M in the same row and paste the values to the next open row of a range A17:L46 on another worksheet (sheet2.) Basically the text in column M is an idicator to add the data to an order sheet. If there is no text don't add the data , look in the next row etc.
I'm not sure if this is the way to go

Sub CopyRows()
FinalRow = Range("M65536").End(xlUp).Row
' Find the last row of data
' Loop through each row
For x = 2 To FinalRow
' Decide if to copy based on column M
ThisValue = Range("M" & x).Value
' could be any value
If ThisValue = "yes" Then
Worksheets("sheet2").Range("A" & x & ":L" & x).Copy
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.PasteSpecial = xlValues
End If
Next x
End Sub

View Replies!   View Related
Returning The Contents Of A Non-empty Cell In A Range Of Empty Cells
I have a long range of cells (U3:AX3), all of which are empty save one. Is there a way to search through the range of cells, and return the contents of the one cell that contains text?

I would do this with a series of nested IF statements if there weren't more than 30 of them!

View Replies!   View Related
Find The End Value In A Row (right Side) And Select The Empty Cell To Its Right To Paste Data In
i need a method to find the end value in a row (right side) and select the empty cell to its right to paste data in.

problem is the row where this data is may change so using

limit = (row, col).end(XlRight).Col

View Replies!   View Related
Copyright 2005-08, All rights reserved