If any cell in a range is blank question

Hello.

I have two sheets in a workbook. One is a Summary sheet, and the next is 
titled "Bldg 5".

What I am trying to make sure is that the range A1:K20 on the Bldg 5 sheet 
has had data entered to them (ie, they are not blank).

The question that corresponds to Summary sheet A1 is "Has the data for Bldg 
5 been updated?"

If ANY blank in Bldg 5's range A1: K20, then put a "NO" in Summary A1's 
cell. If ALL of the Bldg 5 range had data in it, then put a "YES" in Summary 
A1.

I also need A1 to update as data is entered into the Bldg 5 range 
(automatically refresh) so it does not erroneously show a NO when all data 
has indeed been entered.

I know that this is some combination of an IF statement, but something else 
has to happen to make sure that it updates as data is entered on the other 
sheet.

Appreciate any help or starting path.

VR/

John 


0
cpocpo (5)
3/29/2006 1:33:06 PM
excel 39879 articles. 2 followers. Follow

1 Replies
439 Views

Similar Articles

[PageSpeed] 49

One way:

    =IF(COUNTA(A1:K20)<220,"NO","YES")

In article <m0wWf.6655$4L1.1089@newssvr11.news.prodigy.com>,
 "John" <cpocpo@sbcglobal.net> wrote:

> Hello.
> 
> I have two sheets in a workbook. One is a Summary sheet, and the next is 
> titled "Bldg 5".
> 
> What I am trying to make sure is that the range A1:K20 on the Bldg 5 sheet 
> has had data entered to them (ie, they are not blank).
> 
> The question that corresponds to Summary sheet A1 is "Has the data for Bldg 
> 5 been updated?"
> 
> If ANY blank in Bldg 5's range A1: K20, then put a "NO" in Summary A1's 
> cell. If ALL of the Bldg 5 range had data in it, then put a "YES" in Summary 
> A1.
> 
> I also need A1 to update as data is entered into the Bldg 5 range 
> (automatically refresh) so it does not erroneously show a NO when all data 
> has indeed been entered.
> 
> I know that this is some combination of an IF statement, but something else 
> has to happen to make sure that it updates as data is entered on the other 
> sheet.
> 
> Appreciate any help or starting path.
> 
> VR/
> 
> John
0
jemcgimpsey (6723)
3/29/2006 1:57:03 PM
Reply:

Similar Artilces:

When I am in one cell highlight another
Hello, When I am in one cell highlight another. Lets say that if my cursor is in A1 I want D1 highlighted or with another cursor on it. Then if I move to A2, D2 should be the one with another cursor or highlighted. Thank you for your help, Jose Juan Diaz hi, Jose Juan ! > When I am in one cell highlight another > ... if my cursor is in A1... D1 highlighted or with another cursor on it. > ... if I move to A2, D2 should be the one with another cursor or highlighted. 'put' a cursor on non-active-cell... [I don't think it's possible] :( to highlight 'D' wh...

Cells print so small I cannot read numbers. How do I fix?
I have been working with page break. Now I have the grid on 1 page..but it is far to small to read. now when I try to spread it back to 2 pages, it just takes the same tiny microscopic type and spreads it into 2 pages. I am stuck printing tiny type. How can I get the grid cells back to a size that is readable. It sound like you have selected Fit to 1 page in File > Page setup > Page > Scaling. Either select to fit it to 2 pages or select Adjust to 100% size -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Repl...

Process all cells in a (user) selection
I am using this code to give me cell by cell access to a code defined selection; Dim aCell As Range For Each aCell In Sheet4.Range("A4:A34") ' Do Stuff Here Next aCell which processes every cell in the A4:A34 Range How do I do the same thing for a selection drawn by the user before pressing my 'Process' button ? Thanks On 05 May 2010 11:10:21 GMT, Isis <isissoft@NOSPAMbtinternet.com> wrote: >I am using this code to give me cell by cell access to a code defined >selection; > >Dim aCell As Range >For Each aCell In S...

think cell program
is anyone familiar with a program called think cell? Any thoughts? (powerpoint v 2003). Is this an add-in? thanks Sara It's very good. You can "try for free" from their website: http://www.think-cell.com/ Recent interview with one of the founders on Indezine: http://blog.indezine.com/2009/12/think-cell-conversation-with-markus.html -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/3...

Excel question #9
Is there a way that I can transpose the order of the values in a cell? For example I have the values of 10.200.13.1 in a cell and I want to transpose(not sure if that is the correct term or not) the order of the values in the cell so that they appear as 1.13.200.10. thanks for any help as I have literally 4 pages of these kind of values that I have to flip. -- Brian blanktree at hotmail dot com Hi try the following user defined function from John Walkenbach's book Excel 2000 formulas (great resource by the way): Option Explicit Function REVERSETEXT(text) As String ' R...

Newbie Cell Reference Question...
Hello, I apologize if this question has been answered before, but I'm no quite sure what to search for as I'm not very good with excel. I'm making a spreadsheet where one sheet references another. I'll tr to explain this as best as possible.... I'm creating a spreadsheet for a fantasy basketball league. I have "Data" sheet that contains data for all players. I have another sheet "Teams" that has all the players on each team. Column B contains th players name, and column C contains a number that corresponds to th row this player is on in the dat...

List box with available queries question
I have a list box control on a form and want the list box to display all the queries within the database. The following code is what I have so far but it does not work. Any help is appreciated. SELECT [Name] FROM MSysObjects WHERE [TYPE] = 5 and LEFT([Name],1) <> "-" ORDER BY [Name]; "Billy B" <BillyB@discussions.microsoft.com> wrote in message news:F47BB77B-7B66-4860-8954-F4FE32FE7C3C@microsoft.com... >I have a list box control on a form and want the list box to display all >the > queries within the database. The following code is what...

xsd question #5
I am doing a bulk load and an element name in the xsd and xml is named differently in the database. I can NOT change the database field or xml. Can I do anything with ehw XSD? The field name in the db is "CaseType" <xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xs:element name="Cases" sql:relation="PUCCASES"> <xs:complexType> <xs:sequence> <xs:element name="NbrOfCases" type="xs:decimal"/> <xs:ele...

Don't know where else to put q's. 2 questions. 11g Dongle and wire
What is and Acer WLAN 11g USB Dongle? It is some program that is on my computer and pops-up at start-up. I'v double clicked it, and nothing happens. I went on Acers site, and I can find nothing. Even some freeware mentions it, but they only talked about the freeware, not this WLAN 11g itself. And how can I tell if I have wireless or not. This seems like a nobrainer, you have it or not. But some documents on my computer mention wireless internet surfing in programs that are there. I use a high speed connection through an external modem that connects to my comuter and the w...

Cell background shading in Excel
Why is it that the background shading colors available under the Paintbucket Icon chjange from file to file? Also, how can I add some of the colors to the paintbucket that are availabe under the format/cells/pattern pulldown? Colors are a Workbook-level property, so you can change the color palette for any workbook. Choose Tools/Options/Colors... and modify the colors to your hearts' content. You can also import another workbook's color palette from that dialog. Note however, that there are only 56 bins in the color palette, so you can only use 56 colors at a time. The paint b...

compare two columns with different ranges in two worksheets
I need to compare two columns of data in two different worksheets and display a third one. Here it is an example: -(worksheet1!A1:A10), (worksheet1!B1:B10) and (whorksheet2!C1:C25) -this is my query, if C5 is already in (A1:A10) I want to display B5 in worksheet2!D5 I think it is tricky because you need to identity which row in the A1:A10 is equal to C5 to display B5 and the range are different. you could save my day chris90 In worksheet2!D1: =if(isna(vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)), "", vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)) HTH Kostis Vezerides brilliant, ma...

Sum every other cell?
I own a used-book shop. I have an Excel SS to track how many books pe day I sell in each of 28 categories and the $$$ I take in for eac category. So there's two columns for each day: #books & $$$. therefore want to add every odd-numbered cell in a row to get the tota number of books for a category in a given period of days and ever even-numbered cell in the same row for the the total $$$. Is there simple formula or function for this -- bookmanj ----------------------------------------------------------------------- bookmanjb's Profile: http://www.excelforum.com/member.php?acti...

NDR's from spam question
Just curious, I get several NDR's built up in my outbound queue due to many spam messages coming in to recipients that do not exist in my domain. Of course exchange tries to send an NDR to the sender in the "from" field of the email and can't since its spoofed junk mail. Is there any way to configure exchange to verify if the recipient is valid. I am not talking about relaying... the domain in the emails are correct, but the recipient on the left side of the @ symbol does not reside on my mail server... What do you all do? thanks, Todd "Todd" <anon...

Excel 2007 Question #4
I want to bring data into an excel 2007 worksheet that is stored in a sql database table. The idea is to parameterize the "where" clause in the sql select statement limiting the result set. There are lots of examples in the literature with regard to bringing in data stored in cubes --- but not so much from data in sql table. The basic issue is I want bring in descriptive information about say a contract (things like description, signing date, etc). Cubes which most (all) of the literature deals with are dealing with aggregations of amounts or "measures" and not so...

Question about Paste Special
Hi, I have a problem sometimes with the paste special options when goin from one excel workbook to another. For example, sometimes when I cop data from one workbook and then paste special into another, I get th options that include: All, formulas, values, formats, has the option t transpose the data and paste link among other options. And the sometimes I try and paste data to another workbook and I end up th paste special options: Bitmap Image Object, picture, bitmap, and I als lose the ability to paste link. Well you can do it, but it puts it i as an object. What I want is the first past...

MERGE CELLS
I have Name, PO Box, street address, city, state, zip across a row in 6 seperate cells/columns. I want to have this format in 1 cell: Name PO Box Street address City, State Zip STEVE wrote: > I have Name, PO Box, street address, city, state, zip across a row in 6 > seperate cells/columns. > > I want to have this format in 1 cell: > > Name > PO Box > Street address > City, State Zip > You do realize that this will screw up your ability to sort the data. A better solution would be to describe what you want to do with the data. You may not need it in the ...

How do I format a cell to auto date
I would like to have a date automatically enter itself each time I open the spreadsheet. Is there a way to do this? Any help would be appreciated. Thanks Hi George Use a function like this one for todays date =TODAY() -- Regards Ron de Bruin http://www.rondebruin.nl "George" <George@discussions.microsoft.com> wrote in message news:7EF21130-EBD9-41C2-8CB5-5723BE40CBB1@microsoft.com... >I would like to have a date automatically enter itself each time I open the > spreadsheet. Is there a way to do this? Any help would be appreciated. > Thanks ...

Repeating columns cuts off merged cells
Hi all, I hope you can help because I can't find a solution to this one. I've got a two page spreadsheet (side by side) and I'm trying to repeat columns A & B because their row headings on the left and should only appear when printed (split onto two pages) (as in print setup > columns to repeat at left > $A:$B), ...... but B57:I57 are merged and wrapped (another grief that merged wrapped cells don't auto height, but that's for another day) and so are B58:I58 and B59:I59 (footer things). ...... and when printed, B57:I57, etc. cuts off on the second page and...

IF THEN QUESTION???
Perhaps this has been discussed in the past. I have 30 worksheets in an EXCEL file. The #1 worksheet is the main informational sheet. I'm trying to obtain the formula that would allow me to retrieve a specific data from any of the given worksheet. Example: If J7 is 2, I would like for the system to go to worksheet #2 and obtain the number from cell d39. If tomorrow I change J7 to 25, I would like it to go to worksheet #25 and obtain the number from d39. Your assistance is greatly appreciated Thanks in advance, Mike Have a look at the INDIRECT function to create this formula ...

A question on vba
I have Excel 2000 installed in my computer, and probably need to do some vba programming. I have no experience at all. My first question is: does the vba come with Excel, or is it a separate software that I have to purchase and install? Thanks. Hi Dale, VBA comes with Excel. Change Security level from High to Medium Tools, Macros, Security, Medium Getting Started with Macros and User Defined Functions http://www.mvps.org/dmcritchie/excel/getstarted.htm Some Examples of Macros in (see my excel.htm page as well) http://www.mvps.org/dmcritchie/excel/proper.htm http://www.mvps...

Boot Disk Question
Hi, I have an SBS 2008 64 bit server that when it was delivered was pre-installed by the server supplier. When I completed the install I ran into a problem with some of the hardware and decided to reformat and start again as I wasn't happy with some of the errors I was getting. After installing again I discovered that for some reason there were two drives C: and D: that had operating systems on them. The drive that is C: is the current operating system and the D: drive has the old install. My problem is that the old install drive seems to have the boot files on it and t...

how to add a cell value to a letter to reference another cell
Is it possible to take the value of a cell (say the value of 83 that's in cell A1) and append it to a letter (Z) so that a subsequent formula can get a value that's in Z83. Hope this make's sense. Thanks in advance for the help! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ One way, =INDIRECT("Z"&A1) -- Regards, Peo Sjoblom "Mac Macaroo" <Mac.Macaroo.vo1rn@excelforum-nospam.com> wrote in message news:Mac.Macaroo.vo1rn...

Question about Notes in Outlook
How can I group different notes, in folders or with seperators? miscellaneousmedia@yahoo.com <miscellaneousmedia@yahoo.com> wrote: > How can I group different notes, in folders or with seperators? You can make as many Notes folders as you want with File>New>Folder, making sure the "Folder contains" drop-down says "Note Items". You can also assign categories to notes (right-click a note and choose Categories. Check the categories you want) and then change the Notes folder view to By Category. -- Brian Tillman Anyway to use a separator? So I can se...

How to locate differences between two sheets/ranges?
Well, here's my problem: each day here at work I receive a file with updated info on specific data. Form is always the same -specific number of columns with the same type of data, only the number of rows may change. However, every day I have to go through the data to see what has been changed and/or what has been added/dropped. Is there any way to do this easily? Kind regards Th Hi Have a look here for some ideas. I'm sure one of them will suit: http://www.cpearson.com/excel/duplicat.htm -- Andy. "Gryzor" <Gryzor@discussions.microsoft.com> wrote in message news:...

Conditional Cell Number Formatting
I've looked back on several thousand messages or so and haven't seen this so here goes. I need to be able to set the numerical format of a cell based upon the contents of another cell. In other words if C1 is equal to x than the numerical format of C2 would be "00000" and if C1 equals y then the C2 cell's format would need to be set as 000000000000. I hope I've been clear, thanks!! All i could find in the usual help channels was concerning the formatting of contents with respect to font, colors, etc. VBA? Option Explicit Private Sub Worksheet_Change(ByVal Target...