How to refer to current sheet in a formula
I want to have a named formula which always refers to a cell on the
sheet it is on. When I define the formula it is automatically changed
to refer to the sheet active when I defined it. E.g. =$B$2*3 becomes
=sheet1!$B$2*3. This is no good for use on sheet 2 where I want the
formula to refer to that sheet's $B$2 (equivalent of sheet2!$B$2). I
have tried entering =!$B$2*3 which works initially but recalculates
using the $B$2 on the sheet active when the recalculation is done.
Poxypig's Profile: http:/...Formula for current month minus one = Quarter number in a macro.
I have a macro that does several things. The last step should be to save the
file to a network drive with a name and quarter number appended to it.
The file save command I have got is: -
ActiveWorkbook.SaveAs Filename:= _
"Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
The ? in the file name should be substituted by the formula as described
The macro is to be run in the month following a quarter (i.e. run macro in
April to get Quar...Automatically fill down formulas when adding a record
I have a data entry sheet which receives one record per row. Some of th
columns contain calculated fields and will be hidden. Is there a way t
have the formulas automatically fill down when new records are added
Message posted from http://www.ExcelForum.com
One way is to use Excels built-in dataform.
johnf 202 at hotmail dot com
"steveski >" <<email@example.com> wrote in message
| I have a data entry sheet which receives one record per row. Some of the
| columns con...date formulas
Hi, I have two problems that need help.
Cell A1 has a date of birth. Cell A2 calculates the age. If there isn't a
dob of birth, cell A2 returns 110.
Another one is having cell C1 with a date or NA. C2 is based on cell C1 and
adds 3 months to the date in C1 but if C1 has NA, the return is #value!.
Describing the problem is only the first step, Kathleen. You also need to
identify the solution you are looking for.
As a guess, for #1 try,
=if(c1="NA","",date(y...sum if formula
=SUM(IF('Day 1'!$C$4:$C$37=A22,'Day 1'!$D$4:$M$37))+SUM(IF('Day
2'!$C$4:$C$37=A22,'Day 2'!$D$4:$M$37))+SUM(IF('Day 3'!$C$4:$C$37=A22,'Day
...Formula Bar missing
I need help reappearing my formula bar. I must have pressed something that
made it disappear. If I go to the “View”menu, Formula Bar has a check next
to it, as if it should be showing, but I can’t see it. I tried checking and
un-checking, but no where to be seen. I have two other tool bars and my
status bar, that are working fine.
Could anyone give me a hand with this?
I am currently using Office v. X.
You may have switched "Full screen" on. To uncheck, see the View menu
Microsoft MVP - Excel
"JorgeH" <JorgeH@d...View one record at a time in a report.
I am new to access.
I created a report from a table using the wizard.
When I view the report, I only want to see one given record at a time - not
How do I do this?
The easiest way is to start from a form, where you bring up the record you
want to see. Then put a command button on the form, to open the report to
just that one record.
Here's the code you need for the command button:
Print the record in the form
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne...Add to Formula
I have created a drop down list and added a formula to show me the cost of an
item eg; pink slippers when that is picked from the list the price appears in
the next column..that is all working fine.....=IF(C3="","",VLOOKUP(C3,'Sheet3
I've been trying to add another formula to this for inventory. So actually
what my question is.
How do I create a list along with a formula so when I pick the item out it
will put in the price, and minus 1 or however many sold, to give me a count
of what I have left in stock, and say I started with 25 of...Case Billable Time and Total Time in workflow
Case Billable Time and Total Time fields don't accessible in workflow!
How can I calculate Case duration in workflow or access aforesaid fields?
IR-0912 *** ****
...formatting cells to show time format
I wonder if you can help me with the following. I use
Office 97 Professional, and Excel 97 to do the following.
I have created a spreadsheet that will be used to
calculate times & results for Car Rallies. I have sorted
out the formatting of the cells so they show hours,
minutes, seconds, and down to thousands of a second,
using this time format hh:mm:ss.000. I have done the
formulas to work out the elapsed times and then total
times, by taking the finish time from the start time etc,
then adding elapsed times together to get total times.
The thing I am trying to sort out is wh...= Text formula problem
I am using an excel spreadsheet as a linked table in an access database for
an update query. I had it working very nicely for a period of time. I am
not sure if some Microsoft update caused a problem or what. The field I use
t o update my database didn't work until I used this formula to make a new
I have a column of numbers which was retrieved from a data miner program
it's called Acct. For some reason Access doesn't like the formatting so I
insert a blank column to the right and call that PtNum. In the first cell
of this column which is C2, I type ...Report Taking a Long Time to Print
I have a main report with 2 subreports in it. One of the subreports seems to
be holding up the report when it comes to running and printing. When I run
the query and/or report for the problem subreport it returns data within 1
minute. When I run the main report, it takes anywhere up to 5 minutes to run
and about 12 minutes to print. When this subreport is removed from the main
report, running and printing is under 1 minute, so I know this subreport is
I had seen a post similar to this question and I tried the suggestions
(removing page breaks, turning force new page ...SSN printed with extra dashes 1 time
I have a database storing soc sec numbers and names. The soc sec numbers are
entered on a form with a picture clause of 000\-00\-0000;0;_
I have a report (complicated format) that grabs information from the form
and prints out the Soc Sec Number. It has a picture clause of @@@-@@-@@@@
For one client and one client only, the SSN prints out 111-2-3--5555
All others print out 111-23-5555
Does anyone know why this happens. Win XP SP2 and Access 2003 system running
Thanks in advance
It is very probable that the SSN that has the problem already has the dashes
in it or is ot...Why Does This Formula Return an Error??
:confused: Hi, what am I doing wrong with the following formula:
FOR some reason this formula fails when I use the (sheet1:sheet31) wit
the column range (E6:E35).
Is it possible to include a multiple sheet count and column range i
the same Formula? Thanks for any helpful responses..
By the way, the formula works ok just as long as I'm not trying to rea
multiple sheets.. Please help on this
Mhz's Profile: http://www.excelforum.com/member.php?action=...receiveing the same email three times #2
My outlook express died on me, therefore I had to start
using outlook 2000, which was fine bit of a pain to get
used to but, anyway since using outlook 2000, I am getting
the same email from everyone three times. Why is this?
It is soing my head in as my outlook is very full and it
is taking me some time to get rid of the ones that are
duplicated. PLEASE HELP ANYONE!!!!!!!!!!!!!!!!!!!!!
I have some doubt in Date & Time Format.
I have some (time) appearing in a column in this format 7/17/2006
I need to have a conditional formatting for this in that column.
Ie., if the time in the column is less than 20 minutes to the current
then it should change to some color.
Any help in this regard.
Eager to learn more about Excel
Rajkumar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2159
View this thread: http:/...Formula not deleted when pressing delete???
Is it possible to protect Formula's in a cell from being deleted whe
user pressing delete?
Only the cells who containt formula's may not be cleared but th
Thanks in @dvance for trying to help me.
GreetZ from Stif
Message posted from http://www.ExcelForum.com
Select the cells that are allowed to be changed. Format - Cells -
Protection - Unlock. Now protect the sheet: Tools - Protection - Protect
mvpearl omitthisword at verizon period net
"Stift >" <<Stift.18ct...Drag Formula cell to decrement the cells #2
I do thank you for your prompt assistance.
The solution you gave me works fine but with limited No. of cell
is there a way to do it in an unlimited No. of cells (e.g. 50 or 6
cells - BI till DP to be the mirror of A till BH )
Asher Zur's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1610
View this thread: http://www.excelforum.com/showthread.php?threadid=27554
Sure, just follow instructions as before and in BI1 put =A1 and copy across to
DP...OWA Calendar Time
In my OWA there is a discrepancy between looking diary entry and when
actually double-clicking on it. The appointment times are an hour behind in
the details of the appointment.elay. This does not happen on the Outlook
The time zone, daylight saving settings are correct on the Exchange server,
Outlook client, OWA Options settings, client PC as well.
Any suggestions will greatly appreciated.
Issue resolved. For anyone inetrested in knowing the solution....
Went into the Time Zone settings on the Exchange server and set it to Hobart
time zone (we are in Sydney by the way)...Setting Retention times for Mailboxes
Using Exchange 2000 SP3. Have a user that claims some email are missing
from Outlook inbox from over a year ago. Is there a default setting
within Exchange in which mailbox items get deleted after a certain
period of time? (I am not talking about deleted item retention periods)
On 18 Jan 2006 06:27:27 -0800, firstname.lastname@example.org wrote:
>Using Exchange 2000 SP3. Have a user that claims some email are missing
>from Outlook inbox from over a year ago. Is there a default setting
>within Exchange in which mailbox items get deleted after a certain
>period of time? (I am not talk...Protect: Formulas?
I'm protecting a sheet via VBA.
There seem tb quite a few parms to control what gets allowed/disallowed, but I
can't find anything that applies to formulas.
I want the user to be able to put formulas behind some cells.
Right now, with the sheet protected, the user can type data into cells, but the
Sigma icon that allows formulas tb put behind them is disabled.
Unprotecting the sheet enables the icon... but I want various things to remain
Is there a way to allow formulas, but still have Protected=True?
You have to have unlocked the cells the use...Need Help Nesting a Formula inside MAX formula
I have Stock Market quotes that gets stored in Excel intra-day
When every 30 minutes passes, the data is automatically updated and
archived in the next row up and the new update begins.
C2 9:30 D2 25
C3 10:00 D3 25.5
C4 10:30 D4 25.10
C5 11:00 D5 26
After 30 mins the data looks like this:
C2 10:00 D2 25.5
C3 10:30 D3 25.10
C4 11:00 D4 26
C5 11:30 D5 26.25
This process keeps going on.
I can archive as many periods I want, in any timeframe.
I currently store 100 rows of 30 minute periods.
Everytime a new peiod begins al...Strange Problem with Formulas
I was confronted with a very strange problem. Can anybody help?
Basically, I have 3 worksheets:
No. 1 contains all data
No. 2 contains all filtered data (macro special filter from No. 1)
No. 3 is the output, graphically optimized sheet, which has fixed
formulas to No. 2 (e.g. =Event1!A1)
Imagine, I had only these 3 sheets. Actually, I have many of them, but
there are all paired as No. 1 and 2.
No. 3 is the main sheets, which links too all No. 2's.
To update the file, I have created a macro, which compares one excel
file with the actual one. All worksheets named similarily ...Two formulas in one cell?
Is it possible to have two formulas in one cell? For example, here's what
I'm trying to do:
If cell A1 has the word HORSE in it, I want the formula in A2 to be x + z
If cell A1 has the word PIG in it, I want the formula in A2 to be x * z
Anyone know if that's possible?
If the lower upper case does not matter
if it matters
=IF(ISNUMBER(FIND("HORSE",A1)),x+z,IF(ISNUMBER(F...Not having to wait for routine to finsh / run three events at the same time
I developed a simple app, it goes out to an access db and populates a
list box and pings as server. Pretty much how can I do both events at
the same time without haveing to wait for one to complete before
moving on to the next one. It acts like an application monitor where I
ping the servers every 15mins and see if they are up, they are totally
seperate Public routines, sound simpel but it is not. I just want to
ping all three severs and get the ping times. I have everything coded
it is just the waiting that has me stuck! Thanks
Your problem is a typical example where a backgroundwork...