Hide Cell Content if no data in previous columns

I've made this work many years ago but now can't remember 
how.  I have a job invoice to where the rate is 
40.00/hour. This 40.00 is used in calculation to 
determine the total amount of pay owed. (20 hours worked 
* 40.00/hour).  The boss requires that the hourly rate be 
shown for each job on the invoice, so that means it is in 
a column to where the amount is repeated all the way 
down. All I want is for the contents of that particular 
column NOT to show (show as blank), if no job has been 
entered. In other words, if there is no data entered on 
that particular row, hide the contents of the cell the 
hourly rate is shown in. Does anybody know how to do 
this? Thanks!
0
sakins (16)
2/21/2005 5:00:39 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
396 Views

Similar Articles

[PageSpeed] 3

Try this one: suppose the hourly rate cell is in cell C10.  Suppose the
job description cell is B10.  One way to do this is enter this formula
in C10:
=IF(B10="","",40)

This enters a blank value if B10 is blank, and populates it with 40 if
the job is filled in.  Does that do it?

0
CycleZen (674)
2/21/2005 6:28:17 PM
hi,
very confusing.
some relevent date would have been helpful but i'll get 
out the dart board.....
assuming that the rate in in column D and some type of job 
id is in column A.....
=if(A2="","",hourlyRate)

>-----Original Message-----
>I've made this work many years ago but now can't remember 
>how.  I have a job invoice to where the rate is 
>40.00/hour. This 40.00 is used in calculation to 
>determine the total amount of pay owed. (20 hours worked 
>* 40.00/hour).  The boss requires that the hourly rate be 
>shown for each job on the invoice, so that means it is in 
>a column to where the amount is repeated all the way 
>down. All I want is for the contents of that particular 
>column NOT to show (show as blank), if no job has been 
>entered. In other words, if there is no data entered on 
>that particular row, hide the contents of the cell the 
>hourly rate is shown in. Does anybody know how to do 
>this? Thanks!
>.
>
0
anonymous (74722)
2/21/2005 6:29:45 PM
Well, that almost works and would if the hourly rate 
(C10) were not part of a calculation itself.  Here's what 
I've got basically:



JobName HoursWorked HourlyRate Subtotal Total

Consider those Columns A, B, C, D, E and then rows 1, 2, 
etc.

Cell C1 would show 40.00 only IF columns A or B are 
empty. If empty it should show blank. Your formula works 
for that. However, Cell D1 is =C1*b1, so now, unless I 
have something in Cell C1(the hourly rate)I get #VALUE! 
in the subtotal and total fields. 

If I then add a number of hours in cell B1, it posts the 
40.00 in C1, then calculates D1 and E1 correctly.

If I don't use the IF statement and just put the 40.00 
in, it calculates that fine, except then I have to fill 
the series all the way to the bottom, so it shows the 
40.00 on every row without other input.  

Actually, this worksheet is a lot more complicated than 
that, but that is the basics of what I'm trying to 
accomplish at this point. 

In yet another cell (in a hidden column) I have this 
formula: =roundup(C1/60,2)*b1 to get the actual per 
minute rounded up (which at the rate of 40.00 would 
be .67/minute).

In addition to that, the actual amount that should appear 
in C1 (the hourly rate) actually comes from a link in 
another worksheet, since we use a 2nd sheet to customize 
the invoice per employee and different employees are paid 
different rates.

Ugh...complicated a bit, I know, but I know I can somehow 
make this work.

Thanks for your help!

>-----Original Message-----
>Try this one: suppose the hourly rate cell is in cell 
C10.  Suppose the
>job description cell is B10.  One way to do this is 
enter this formula
>in C10:
>=IF(B10="","",40)
>
>This enters a blank value if B10 is blank, and populates 
it with 40 if
>the job is filled in.  Does that do it?
>
>.
>
0
anonymous (74722)
2/21/2005 6:58:25 PM
Alright, thanks for the input everybody, but I have the 
answer.  =IF(C1=0,0,40.00)

(Well basically, the 40.00 actually comes from a link in 
another sheet).  Anyway, if I use the zeroes in the 
formula rather than the " ", everything works perfectly!

Thanks again!
>-----Original Message-----
>I've made this work many years ago but now can't 
remember 
>how.  I have a job invoice to where the rate is 
>40.00/hour. This 40.00 is used in calculation to 
>determine the total amount of pay owed. (20 hours worked 
>* 40.00/hour).  The boss requires that the hourly rate 
be 
>shown for each job on the invoice, so that means it is 
in 
>a column to where the amount is repeated all the way 
>down. All I want is for the contents of that particular 
>column NOT to show (show as blank), if no job has been 
>entered. In other words, if there is no data entered on 
>that particular row, hide the contents of the cell the 
>hourly rate is shown in. Does anybody know how to do 
>this? Thanks!
>.
>
0
anonymous (74722)
2/21/2005 7:27:21 PM
Reply:

Similar Artilces:

hide CEO mailbox
Is there is away to hide a mailbox from the GAL, not by using Hide from Exchange address lists “ exchange advanced tab” in exchange 2003 ? Our CEO would like to hide his name from general public in the GAL but at the same time, he wanted to make sure some of dept ( lawyers dept or VPs) can find him in the GAL. he only want certain people can view his name in the GAL. so i was wondering is there any ways we can modify in the Security to restrict users to view his information in the GAL ? you could create a "restricted" address list, perhaps, or perhaps instead, set delivery...

Replace a comma with a period in a cell containing a lastname, first name, middle i
Hello - I am trying to clean some data and need to change all of my names from McLaughlin, Victor, (i.e, comma) W to McLaughlin, Victor.(i.e., period) W Is there an extract and replace formula or method of som sort (in excel or access) that will allow me to pull the first comma from the right and replace it with a period. Thanks for any suggestions! Select the cells you want to change and run this tiny macro: Sub comma_tose() For Each r In Selection v = StrReverse(r.Value) r.Value = StrReverse(Replace(v, ",", ".", 1, 1)) Next End Sub For example: a,b,c,d wi...

Getting Data Shapes to Work with Custom Stencils
I imported a picture and saved it as a shape. Then I linked data from a SQL database. I can get the data to show up in the Shape Date, but I can't get a Data Graphic to appear. Note that I can get Data Graphics to work with out of the box shapes in the same drawing - just not the custom picture. Tried in both Visio 2007 and 2010 Beta. What am I doing wrong? Thanks in advance. ...

Combine multiple rows into one row with multiple columns
Hi, I have a table set up so that there are three columns: StudyID, DrawDate, and Value. StudyID and DrawDate are the primary key. I want to create a table from this one that has only one row for each StudyID so that it would go from: StudyID DrawDate Value to StudyID DrawDate1 Value1 DrawDate2 Value2 DrawDate3 Value3 etc. Is there a way to do this? Thanks, Elysia "Elysia Larson" <elysia.larson@gmail.com> wrote in message news:832e952f-174f-489f-ab7a-e2189f660a92@f6g2000vbp.googlegroups.com... > Hi, > > I have a...

Problems with re-setting the last active cell in an Excel workshee
I am trying to re-set the last active cell on an Excel 2002 worksheet (in this particular sheet it should be cell DA197). I have used both the methods described in the Knowledge Base article (deleting rows and columns and re-saving; and the Excess Format Cleaner add-in). Deleting the rows and columns does not work; using the Excess Format Cleaner does not work either and it then also hides the rows from 198 to 65536 - but does not do the same for the columns. I have checked that there is no protection on the worksheet. Has anyone else come across this problem and if so can you please ...

Column to Rows
I want to convert my data from one column into rows. I have my data set up now as follows: John Smith $3200 555 Main St. 95111 Jane Jones $5500 345 Happy Dr. 93434 Jack Clark $2300 354 Oak Pl. 95343 I want it to be displayed into 4 separate columns as follows: John Smith $3200 555 Main St. 95111 Jane Jones $5500 345 Happy Dr. 93434 Jack Clark $2300 354 Oak Pl. 95343 Please advise, thanks, Don -- Don D. ------------------------------------------------------------------------ Don D.'s Profile: http://www...

Charts switch from 'Series in Rows' to 'Series in Columns'
I use VBA to create charts in Excel 2003, but find that sometimes the Charts switch 'Series in Rows' (intended) to 'Series in Columns' (not intended), even if I have specified 'Series in Rows'. This happens intermittently, and I am not sure what I am doing wrong. I do save the workbook as Microsoft Excel 97 so that a user with Excel 2000 or Excel 2003 can use the workbook. Thank you for any suggestions. Hard to tell if you keep the code secret. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Peac...

Right column spilling onto 2nd page
I bought a template from a vendor and saved it as a new template with my version of excel. The link to that file is: http://www.utahhousevalues.com/lgfiles.cfm. The vendor is not able to help me figure out why the right column is spilling onto a second page. There are 49 total pages and about 25 of them spill the right column onto a second page. They claim each page is identical and it should not do that. I'm using Small Business version of Excel 2000 with XP Professional SP-1 THANKS MUCH Darrell Catmull http://www.utahhousevalues.com Hi Darrell, If the vendor can't tell you w...

How to Arrange Data for Chart
I charted the following data in a clustered column chart. Level 1 Level 2 Level 3 Level 4 FY07 - Total 167 23 20 43 81 FY08 - Total 178 22 40 26 90 These are total department errors segregated by levels for each fiscal year. There are 3 different departments involved. What I did was just total the errors for each FY and each level. Now my boss wants to see how many errors were performed within each level by Dept A, Dept B and Dept C and compare the 2 fiscal years. I hope I’m explaining...

tasks to cell phone
How can I use exchange to send tasks to cell phone. I want to do when f.e. this phone is away from the office. This is PDA phone. On 14 Dec 2005 11:38:11 -0800, "Filip - beginner" <fwitkowski@gmail.com> wrote: >How can I use exchange to send tasks to cell phone. I want to do when >f.e. >this phone is away from the office. This is PDA phone. If you're using a Smartphone you can sync tasks already with ActiveSync. "Mark Arnold [MVP]" <mark@mvps.org> wrote: >On 14 Dec 2005 11:38:11 -0800, "Filip - beginner" ><fwitkowski@gmail...

Replace Cells with Column names in functions?
I have a # of fairly long/complex cell functions that get hard to debug because there are also a lot of rows. Is there anyway to change display so it replaces the column name e.g. If(BT1204="X". BA="Y" to If(CustomerName="X", CustomerCode="Y") ? CustomerName is a defined name range for BT1204 Find & Replace Find what: BT1204 Replace with: CustomerName "msnyc07" wrote: > I have a # of fairly long/complex cell functions that get hard to debug > because there are also a lot of rows. > > Is t...

Users and Asset Data linked on Form
Hello everyone, I have two tables, one a Staff list and the other an Asset list extracted from seperate databases, the information in the tables has absolutely no common data, I have a form with the user name and other bits and pieces on from the staff list and also a drop down list of all asset numbers which I can then select the appropiate asset number from then, that asset number and other details I would like to be associated to that user. I have tried, I have done this in the past about 4 years ago but I cannot remember any of it! Any help would most appreciated Cheers David David, T...

Calendar in Cell Validation
I want to implement a cell validation such that when the user attempts to input a date, a "list" box-like functionality pops up that has a calendar and the user may then choose the date by picking with the mouse How would I implement this? Thanks Jerry Try the following Web site. http://www.fontstuff.com/vba/vbatut07.htm This site's author covers this in a tutorial, but also provides downloads. Mark <jerry.ranch@pioneer.com> wrote in message news:2r9t51pjmumjk7rjpopo7fuamg81gqkljq@4ax.com... >I want to implement a cell validation such that when the user attempts &g...

extract info from cell, then count
I have a 2-part question: (i) I have 1000's of e-mail addresses but want to extract the countr from the e-mail i.e. abc@def.de, where de (Germany) is needed. How d I isolate the ".de" (and others eg .fr, .edu, .com etc etc) (ii) Having done the above, I then need to do a count. Rather than us COUNTIF and include the code for every country in the world, is ther any other way of counting? I guess a Pivot table? thanks, cathal..... -- Message posted from http://www.ExcelForum.com One way: =MID(A1,FIND("^",SUBSTITUTE(A1,".","^",LEN(A1)-LE...

Can I use VBA to add cells (over blanks) then do multiplication
I have a Word table in which the last column contains numbers (3 and 4) and some bank cells and I want it add them and put the total into the second last row (7 in this case). The last row contains a multiplier (3) which when applied to the total results in 21. Below is the table. | | | 3 | | | | | | | | 4 | | | | 7 | | | 3 |21| How can I achieve this in VBA (under Word 2003 and 2007) remembering that the user can add rows to the table and the last column can contain blank cells. Thanks in advance for any assistance, Peter Evans Sub ScratchMaco(...

How do I put Excel data into a US map format?
I want to feed Excel data about population and trends into a map format instead of a bar graph or pie chart. Is there a plug-in or some such thing that I can use that works with Excel? Ultimately, I want to have each state depicted by a color code for a range of population or an amount of certain data. I am using Excel 2002 in a Windows XP environment. ...

Conditional Formatting
Is it possible to format a portion of a text string within a cell (as opposed to the entire cell). For example, I would like to format the word 'gift' in red font anywhere it a appears in range C2:C417 but only that word, not the entire cell. Not with conditional formatting. But you could change the actual format for that word (or group of characters)... Saved from a previous post (or two!): If you want to change the color of just the characters, you need VBA in all versions. You want a macro???? Option Explicit Option Compare Text Sub testme() Application.ScreenUpdating ...

VBA to add and remove text within cells
Hi, I have a field named "Postal" at the top of column F that always include a number with 5 digits then a city name then a region name, such as "11090 CARCASSONNE Linguadoca-Rossiglione". I need to create a program to have this field changed as following : "F-11090", then copying "CARCASSONNE" into the City field which is empty (column G). The city name is always starting just one space character after the postcode, same thing for the region name, it always starts one space character after the city name. The region has to be removed completely. ...

how do I sum only visible data in a column
I have some rows manually hidden, Please is there a formula I can apply to return the sum of the unhidden data contain in a column? Hi Afolabi, If you don't want to use VBA (create a UDF), you could review the following pages: http://www.jkp-ads.com/Articles/ExcelNames08.htm http://www.jkp-ads.com/Articles/ExcelNames09.htm In any case, there is a fundamental issue with both approaches: since hiding/unhiding rows doesn't trigger any event, such event will have to be forced from time to time, or maybe you can leave with the formulae updating their result in the next recalculation...

Can Import Email Data Filed from Office XP to Office 2003
My HD failed but I had a backup of my Office XP Pro personal file folder with a lot of emails I needed. The file is about 128MB in size. I put a new HD in and installed Office 2003 Pro. When I go to open the old file Outlook 2003 says that the file "is not a personal folders file". (It was backed up to a CD ROM prior to the crash so the source file should be good.) I really don't want to uninstall Office 2003 and put back Office XP. Any idea why 2003 won't open the file and how to work around that? Many thanks in advance to anyone who knows the solution. Alan. The only pro...

Finding Cells that Total a Value
Hello Friends, I need some assistance in solving a problem. I have a spreadsheet with over five hundred lines of transactions. The sum of these transactions are creating a balance on the account. Is there any formula/macro that will help me find the transactions creating the balance? The sum of the account should be zero. To clarify, if we owe client money, there would be a transaction setting up that postive balance then a payment on the account taking it back to zero. There could be multiple transactions and then one net payment. Or we could be due to receive. So at the end of the day, th...

Importing External Data
I keep trying to import data from an Access database and I continually get the same error. "ODBC Microsoft Access Driver The text file specification "Inserts" does not exist You cannot import., export or link using this specification." Well the spec does exist, the queries run just fine in Access. The data source are linked text files using the "Inserts" specification, which definitely does exist. Sumpens wrong, but danged if I know what it is. Any ideas? TIA Paul Hammond ...

go to cell with date equaling TODAY()
Hello! I have a spreadsheet with January 01, 2006 thru December 31, 2006 in ascending order in column A, each date in a different cell (A1, A2, A3, etc.). I don't enter data into this sheet everyday..in fact, months could go by before having to enter an occurance for say, March 31. Is there a way to have excel, upon opening the spreadsheet, advance the cursor to the cell with that day's date in it? -- Thank you all for your help! Using function Date rather than Today() Works for me. Private Sub Workbook_Open() Dim r As Long Dim T As Long T = Date r = Application.Matc...

Adding up numbers from columns.
How can I sum up multiple columns while not letting each cell "associated values" sum to a number greater than one specified. For example, if I have sets of colums: 14 3 12 2 11 2 11 1 10 1 and 20 5 19 4 18 3 17 2 and 25 6 22 4 20 3 18 2 I want to know what 3 combination of numbers in the left 3 colums giv me the highest value...WITHOUT letting the correspoding numbers in th right hand column be greater than, say 10. Thanks, Larr -- Message posted from http://www.ExcelForum.com ...

Data Validation date field
I don't understand why this isn't working. I just want to make sure that they don't put a future date in the polydate field. Is me.polydate > now() an invalid expression? code: _________________________________________________________ Private Sub Form_BeforeInsert(Cancel As Integer) If Me.PolyDate > Now() Then Cancel = True Me.PolyDate.SetFocus MsgBox "Please enter a date that falls prior to today's date" Exit Sub End If End Sub It does not appear to be invalid. It is always helpful to post the error you are getting and if it is a runti...