hide formula in cells till data is available for calc

I recall being able to copy a formula in a column and hiding it until data 
becomes available to complete the calculation.  This way you don't have a 
series of #value# in the work sheet.
The formula contains an absolute reference as well.

Thanks, 

Kiwi
0
Utf
4/3/2010 10:02:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1562 Views

Similar Articles

[PageSpeed] 23

A look at your formula would have been nice.

=IF(cellref="","",do something)     traps for cellref having no data.


Gord Dibben  MS Excel MVP

On Sat, 3 Apr 2010 15:02:01 -0700, kiwi <kiwi@discussions.microsoft.com>
wrote:

>I recall being able to copy a formula in a column and hiding it until data 
>becomes available to complete the calculation.  This way you don't have a 
>series of #value# in the work sheet.
>The formula contains an absolute reference as well.
>
>Thanks, 
>
>Kiwi

0
Gord
4/3/2010 11:52:28 PM
Reply:

Similar Artilces:

Copy Cells #2
Hi, I have 2 workbooks: # 1 Workbook.("TBD.xls").worksheet("Sheet1") #2 is a workbook that I open with this VBA macro: fName = Application.GetOpenFilename Workbooks.Open filename:=fName I also have this VBA Macro for fname blastrow = Cells(Rows.Count, "B").End(xlUp).Row isum = Application.WorksheetFunction.Sum(Range("b1:b10000")) Cells(blastrow + 2, "B").Value = isum My Question is: How do I copy the value of "Isum" in the Workbook.("TBD.xls").worksheet("Sheet1") in cell "F34" Thanks, Workbooks(&q...

deleting blank rows for up to 60000 rows of data
I have worksheets with up to 60000 rows in one column I have tried using the following macro from this site and it does not do anything. Any ideas?: Sub Sonic() Dim i As Long With Application .Calculation = xlCalculationManual .ScreenUpdating = False Lastrow = ActiveSheet.UsedRange.Rows.Count For i = Lastrow To 1 Step -1 If WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub Hi, The code works f...

Problem with Data Types
hi to all I am writing a function whose parameters are the form that is active when the function is called (frmFROM), and the form that the function then opens (frmTO). My problem is that the frmTO is used as a string in line 5 and 8, but as a form in line 6. Whichever way I Dim frmTO, I get a crash. How can reslove this problem? 1 Function ReturnToMenu(frmFROM As Form, frmTO As String) 2 frmFROM.Visible = False 3 Dim prj As Object 4 Set prj = Application.CurrentProject 5 If prj.AllForms(frmTO).IsLoaded Then 6 Forms!frmTO.Visible = True 7 Else 8 ...

Replace cell letter
Not sure how to explain this, but im looking to save a lot of manual entry. ie. sheet1 is fetching its data from sheet2 A1:A1000 and displays the data all over sheet1 to many different cell positions in different columns/rows. How do I point to new data from sheet2 B1:B1000 by replacing all the cells "=+Sheet2!A####" to "=+Sheet2!B####" in sheet1 while keeping the same cell positions??? Hope it makes sense...please HELP! John Try Ctrl+H to launch Find and Replace dialog..Make sure you select 'Formulas' in 'LookIn' -- Jacob &qu...

Jump to formula destination ? ? ?
Hi All If I have a very large workbook, and say on Sheet1 a formula "=Sheet12!a1" is there a shortcut way to jump directly to sheet 12 cell A1 ? ? ? Thanks in Advance Dodge It depends on what your definition of "jump" is. -- Don Guillett SalesAid Software donaldb@281.com "Dodge Brown" <dodge_brown@hotmail.com> wrote in message news:3fcf8ee1$0$25664$cc9e4d1f@news.dial.pipex.com... > Hi All > > > If I have a very large workbook, and say on Sheet1 a formula "=Sheet12!a1" > is there a shortcut way to jump directly to sheet 12 ...

Hiding "0" values in pivot table
In a worksheet I have a listing of stock records with ItemNumber, Date, StockLocation and NumberOnStock. When I make simple Pivot Table with ItemNumber and StockLocation as rows and select a Sum of "NumberOnStock" I would like to hide rows where the sum is "0". But this does not seem to be possible. Tried to follow the guidelines in the Microsoft article: http://office.microsoft.com/en-us/assistance/HP051998791033.aspx#Hide%20zero%20values%20in%20a%20PivotTable%20report But no luck :-( Mogens You could use conditional formatting on these cells - highlight the cells,...

Sharepoint services, Infopath and Excel formulas
I created a form in Infopath and pulled the information into a list in Sharepoint. when i import becasue of the design of the form I am getting multiple reponses which are being stored in one cell. I need to count the number of responses. e.g. Column 1 Column 2 Column 3 Not applicable Yes Not applicable Row 1 Yes No Not applicable No Not applicable Not applicable Not applicable...

Check Book Formula
I'm a real green horn when it comes to excel, I've searched the data base and am pretty baffled by some of the questions...Wow....anyways, I feel kind of silly asking as this is probably very simple. I just don't know how i would go about setting up a check book style spreadsheet. The last three colums would be Income, Expense, Balance. I understand how to add or subtract, what I don't know is how to I tell the spreadsheet to SUBTRACT the expense to get the Balance or and ADD the income to get the balance in each row. Do I have to type a formula in each row to do ...

Vertical Scroll Includes Empty Cells
On one of my Excel spreadsheets, all of the empty cells are being included in the vertical scroll (which increases the document size immensely, making opening, closing and saving an extremely tedious process). How can I change the view so that only the cells containing information was included in the vertical scroll? Hi You need to reset your used range. Have a look here for info: http://www.contextures.com/xlfaqApp.html#Unused -- Andy. "Diana" <anonymous@discussions.microsoft.com> wrote in message news:356a01c42942$d35993e0$a501280a@phx.gbl... > On one of my E...

Hiding formuals in cells question
Hi. I was wondering if there was a way to hide the formulas in cells without protecting the sheet. I am making something for other teachers at my school and I want to hide the formulas so they won't mess anything up on the spreadsheet. Any help would be greatly appreciated. Thanks! Hi not without protecting the sheet but why is this a problem for you? -- Regards Frank Kabel Frankfurt, Germany "JP" <JP@discussions.microsoft.com> schrieb im Newsbeitrag news:ABFE07BF-6087-4E24-814A-03AF8863A633@microsoft.com... > Hi. I was wondering if there was a way to hide the fo...

HELP: big problem with Excel, source cells, crashing and general madness
I have been on microsoft.public.excel.crashesgpfs but so far just the one reply (from someone who has exactly the same problem as we do and has not yet solved it either). Anyone here can help??? My boss has this massive Excel spreadsheet with a number of worksheets linked with other spreadsheets etc. He is using Excel 2000 on Office 2000. His computer was crashing with the above whenever he tried to do work on it. We bought him a brand new computer, 1 gig of RAM, 80 GB of HDD. We're a small company - a charity - so this was as powerful as we could afford. This was a week ago and it&#...

Printing Shaded Cells
I suddenly can't print out the cells that I have shaded with grey with my black and white printer. It shows it in Print Preview but then it doesn't print the grey. HELP! Usually it will print what shows in preview. Probably related to printer setting? Did you try it before? "Teladria" wrote: > I suddenly can't print out the cells that I have shaded with grey with my > black and white printer. It shows it in Print Preview but then it doesn't > print the grey. HELP! Yes, I have lots of experience with Excel. It turns out I have to shade the cell w...

Cells Locking up
Hello For some reason on all Excel documents that I open or create, when I just select a cell and move my cursor the sheet highlights wherever my cursor goes. I am unable to stop this and unable to close Excel or select anything from the Tool bars. The only way can close is through Task Manager. Is there some sort of setting that I have selected or is my Excel corrupt? Cheers This might seem an odd solution, but try clicking 1) your middle mouse button (we have the same at work with drive problems) 2) at the bottom; near the sheets tabs (I've seen this before when i loads the ...

Using IF in formula
I am trying to build up a YTD value in a cell on a worksheet named YTD. The YTD value is the sum of cell ref "A17" on each worksheet named JAN, FEB, MAR...etc upto DEC. The value of "A17" is only taken into consideration if the value of cell "R27" on the respective worksheets is greater than "0". eg. if cell "R27" is value 0 on w/sheet MAR then the YTD value is the sum of cells "A17" on w/sheets JAN and FEB, if the value is 0 on w/sheet APR then the YTD is the sum of cells "A17" on w/sheets JAN, FEB and MAR...and...

How do I stop cells from expanding?
Hey all. Just got Excel 2003, and have a real basic question. I'm working in large files with very long cells, and everytime i click into one to edit it, it expands to take up almost the entire screen. Problem is, i need to look at cells in other columns, and this is a huge pain. How do i get it to stop? Thanks in advance! If it is the formula bar that bothers you can turn it off temporarily under view>formula bar -- Regards, Peo Sjoblom "frodomojo" <frodomojo@discussions.microsoft.com> wrote in message news:FD91C628-9456-454D-9E2A-EF42F6F7F494@microsoft.co...

importing bin data
Hello: Since Integration Manager does not offer the ability to import Inventory items along with their bin locationsand quantities, is it worthwhile to try to import bin quantities into GP via Table Import? childofthe1980s Table Import certainly offers that advantage over IM and any other integration product. Take a look at my article on Table Import at http://dynamicsgpblogster.blogspot.com/2008/10/often-overlooked-yet-powerful-table.html for a complete overview. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The D...

hide my name
When replying to an email or creating one from scratch how do i hide my name in the "from" field...thx fred <fdbjrgetridofthis@wowway.com> wrote: > When replying to an email or creating one from scratch how do i hide > my name in the "from" field...thx Are you saying you want the reply to be anonymous? -- Brian Tillman [MVP-Outlook] no, I don't mind if my email is revealed.. just my name. I have tried by removing it from the "User Name:" field in the email account but it continues to show up. I have also removed from the header informati...

Need Help with a Formula #4
I am new to excel. Trying to set up a formula for the following 2 examples 1. Semi Annual payments of $440,000 for 20 years, or 8Million dollars now, what is the interest rate that was used to arrive at the 8 million dollars 2. Semi annual payments of $440,000 for 20 years, an interest rate of 7%, how much should the payment be in a lump sum today? Thank you for your help, if you could provide me the formula so that I can play around with various amounts or interest rates that would be helpful. =RATE(40,-440000,0,8000000)*2 = -9.3945% =PV(0.07/2,40,440000) = ($9,396,231.83...

Hiding and Un-Hiding Data fields
I am using a form for people to select data via dropdown menus and then having it run through a query to filter results. Is there a way that I can have certain feilds hidden until a toggle button or something of the like is clicked? Basically I want have three choices of buttons: Year, Quarter, and Month. What I ideally want to happen is when someone selects the button for year, a hidden drop down menu appears where they can then choose from the available years to filter through the query. I would say I am above average with using access, but I have no idea how to hide fields and then...

Available Resources
I have a user with W2K and Office 2003. She runs macros in Excel in a spreadsheet that takes data and then creates a new spreadsheet with that data in it. It is very large. Other users with similar resources can run this just fine. (She has more memory, speed and space than anyone could ever use..) 1/2 way through the routine Excel locks up. Then it says, "Excel cannot complete this task with available resources. Choose less data or close other applications." Then she gets a Microsoft Visual Basic Window that says 400. Then she gets two further windows, one winmgmt.exe and ...

Put Active Worksheet's Name in a Cell #3
Is there a way to put the active worksheet's name in the cell? Not the file name of the whole workbook, just the name of the currently selected sheet. Thanks for your help. Hi Corey, There are a few ways this can be done using either macros or functions, here's one of each: *=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1, LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))) (sourced from Chip Pearson's site, http://www.cpearson.com/excel/excelF.htm) I recommend checking this page out as it also shows ot...

How do I hide gaps in an Excel 2007 chart?
I have a bar chart that is showing gaps and i don't want them. The select data > hidden and empty cell button does not give an option to hide gaps. I think by default they are hidden but if you even click on this to see what it does the gaps are shown and no way to turn off. Hi, If you hide the actual rows then with the Plot visible cells only setting on the gaps will be removed. In xl2007 the setting is on the Select Data dialog, Hidden/empty cells button. Cheers Andy Waynesch wrote: > I have a bar chart that is showing gaps and i don't want them. The s...

How to enable auto height with merged cells?
I previously asked a question regarding auto height with merged cells and was given a code to enter into visual basic. I entered the code and copied & pasted a narrative into the row. It originally seemed to work. But after closing the workbook and re-opening it another day the 'referenced' row is back to the original height. Is there a way to for the row height to expand and remain sized so anyone accessing the workbook can read the narrative? Additionall, I need the row to remain at the full height when printing the worksheeet. ...

Hiding a worksheet #3
I want to hide a worksheet. So, I went to the format menu and the "worksheet" option is grayed out. Why? And, how do I "ungray" it. Hi is your worksheet protected ? -- Regards Frank Kabel Frankfurt, Germany llong wrote: > I want to hide a worksheet. So, I went to the format menu and the > "worksheet" option is grayed out. Why? And, how do I "ungray" it. I think Frank meant to ask about the Workbook--not the worksheet. (Check under Tools|protection|Protect workbook or Unprotect workbook) My question is: Is there another sheet in the w...

Sumif where source values are assigned by "if" formula
I am trying to sum the values in a column (Q20:Q300) based on an assigned designation in another column (N20:N300). The destination cell is Q11. The criteria value is found in cell N11. Values in cells Q20:Q300 are assigned via an "if" condition dependent on a value assigned in column "O". When I place the placed the following formula in my destination cell (Q11), it returned a value of "0" which is incorrect. My assumption is that the formula is recognizing the numeric value in column Q as text and is not adding properly. formula in Q11 ... =sumif(N20...