• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

First off , thanks to everyone....and a couple quick questions

R

rjwalters

New Member
First off let me say thank you to those who help out on here. You help us noobs learn something everyday. I have asked 4 questions, got many replies and I appreciate all the help. Tonight I was able to write my first formula without needing to ask for help. I was able to look at what I have done previously and adapt it to my current need, and it really worked. My wife thought I was nuts cause I was so excited.


Now my questions and I have researched them and not found any answers.


I have 3 sheets, all with formulas on them. Is there a way that when I hide the second sheet, the formulas on that sheet do not function. As if it did not exist?


The second is I have this formula, which I think I figured out by myself (insert pat on back...lol) =IF(A7=TODAY(),A1," "). What this does is if A7 is today, then it reads cell A1, else it is blank. All the cells will read A1 if they equal today. What I am trying to do is let the past days keep there data in the cells. What happens now is when the current date passes, the cell goes blank, and I want the data to stay. So I will keep trying and sorry for writing a book.
 
RJWalters


Right click on the Tab of the second sheet and select Hide


For the second question Goto the Google Custom Search box at the top and type Date stamp or Time stamp
 
I know how to hide a sheet, but when I hide the sheet, the formulas do not hide. My first sheet is still pulling values from the second sheet.


I also do not understand the time/date stamp. When i try to enter a date stamp, I get a 5 digit number. THoughts?
 
RJWalters

You can't stop that

Just because a sheet is hidden the cells still have values

Often you will setup a sheet with assumptions and hide it to stop people tampering with it


Can you explain what your trying to do?


The 5 Digit number is probably the date, format it as a Date, Ctrl 1, Number, select a date format
 
For the sheet issue, I have a counter set up for sheet 2 and 3. This counter will return the number of items in column A (on either sheet 2 or 3) to cell A1 on sheet 1. Sheet 2 and 3 are used for different reporting stats. They have the option to hide sheet 2 if working on sheet three and also for audit purposes. I just want to make sure that if by accident something is left in column A on the second sheet, it is not counted when the sheet is hidden. (Auditors will reject our findings if sheet 2 is not hidden).


For the next issue, The formula here is on the first sheet. I want it to read A1 if it is today, Down the left side I have running dates so this book can be used for several dates.

=IF(A9=TODAY(),A1," ") is the formula I have to accomplish what I want. But after the date passes, the cell goes blank. I want it to retain its data even after it passes.
 
Hi Walters ,


What I can suggest is that you rework your formulae on Sheet1 ( assuming that Sheet1 is your reporting sheet which will be checked by the auditors ) , using a flag for each of the two sheets ( Sheet2 and Sheet3 ) , which will be SET if the respective sheet is hidden , and RESET if it is visible ( or the other way around ).


This setting and resetting of these flags can be done by a Worksheet_Activate / Worksheet_Deactivate macro.


Narayan
 
Cool, I will look into that, any thoughts on my second issue..


For the next issue, The formula here is on the first sheet. I want it to read A1 if it is today, Down the left side I have running dates so this book can be used for several dates.

=IF(A9=TODAY(),A1," ") is the formula I have to accomplish what I want. But after the date passes, the cell goes blank. I want it to retain its data even after it passes.
 
Hi Walters ,


I think it is difficult to implement , since it will involve circular referencing ; the formula that you are looking for would be :


If A9 = TODAY() , then pull the value from A1 into this cell , otherwise let this cell retain its present value


where "this cell" is the cell where your formula will be entered.


A better way would use VBA , with a Worksheet_Change macro ; even this can be problematic ; it is better if , rather than linking it to a date which will keep changing automatically , you run a macro if the user clicks a button , so that any change done by the macro is under user control. Also , instead of entering today's date as =TODAY() , it would be better that the user enters a static date , so that you can be sure the date will not change on its own. Within the macro , you can still check for today's date , so that even if unintentionally the button is clicked , the macro does nothing , as long as the static date entered by the user does not match the system date.


Narayan
 
You must log in or register to reply here.
Back
Top