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:/...Help #4
When you go into, Sales Batch Entry, select the batch, transactions, lookup
by document number, the view I am getting now is green and white. I am seeing
all the documents in all the batches.
I don't want this view, I want to be able to view only what is in the batch
What controls this feature?
what you can do is Go to Sales Transaction Entry, Enter your Batch ID, then
if you see the lookup, it'll only have the transactions entered for that
> When you go...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 >" <<firstname.lastname@example.org> wrote in message
| I have a data entry sheet which receives one record per row. Some of the
| columns con...validation list #4
i have created a validation list with more then 500 name, but when i try to
find a particual name i have to go through all of them . is it possblie to
write the first alphabitic letter in the list and the drow down list will
point to that particual name e.g if the name in the list is Mark S. Mark can
i write only Ma to find all Names start with Ma
You can do this if you use a combobox from the control toolbox instead of
"osaka78" <email@example.com> wrote in message
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...No return on basic search of activities, CRM 4.0
If I go into activities and view all of my activities I get the full list of
phone calls and emails. However if I try to search for an activity that I've
carried out with a contact i.e. John Smith, The Company. I get no results
returned if I search on either term, even though an activity on this account
is in the list.
If I go to Advanced Find, I can search for John Smith and/or The Company and
the contact is then returned in the search.
Why is the basic search not working?
(I'm using the web client)
Thanks in advance!
Have you configured your required columns to be ...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...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...HELP: Outlook downloads 3,4, now 5 copies of each email
I make Outlook 2000 use a Outlook.pst file on a separate hard disk instead
of the usual C:\Documents & Settings\ folder.
Everything is fine. I also TICK "Leave copy of message on server" so I
always have a backup copy.
Now when i format my PC and reinstall Windows & Outlook, I point to my
D:\Outlook.pst and it starts using it. Then all of a sudden it starts
downloading ALL my emails again. If I format my PC once again and reinstall
Windows & Outlook, then Outlook starts downloading ALL my emails TWICE
(there's 2 copies of each email downloaded, plus the copy I...VLookUp and DropDown List in the same cell
I know that this is possible but I'm not sure how to accomplish this.
In column E, I have a list of zip code.
In column I, I have the "Priority" category.
Priority will be based on the zip code in column E. This will be
accomplished by VLookUp formula, where I will have a separate sheet store all
the zip codes and the priorities (ie. Priority #1, Priority #2 and so on).
The challenge part is that column I "Priority" column needs to be able to
use as a Drop Down List as well, in case that the VLookUp formula needs to be
overridden for so...= 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 ...VLOOKUP with partial match
How can I get a match with VLOOKUP("TBS";a1:c15;2)
if in the Table_array the data is "TBS XYZ" ?
...i want to mail merge 1 address per postcard 4 to a page,
I have postcards preprinted on one side. I have made a publication using
mail merge to add addresses. But I have 4 postcards on a page when I do the
mail merge I am getting 4 postcards with the same address. How do I get one
address per card but do 4 to a page?
What version Publisher? In some earlier versions the print preview showed all
the addresses the same, it was/is a Publisher bug.
In Publisher 2007, once you have your postcard created --- File, print merge, on
this screen, select multiple copies per sheet and landscape. There is a print
preview on this screen.
Mary Sauer ...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=...Outlook Profiles #4
Does anyone know how to auto-configure Outlook profiles
(exchange server, alias, .pst, etc) at logon based on
Create a prf-file with the variable %username%. Instructions on the prf-file
can be found in the Office Resource Kit
Roady [MVP] www.sparnaaij.net
Microsoft Office and Microsoft Office related News
Also Outlook FAQ, How To's, Downloads and more...
Tips of the month:
-Save Embedded Pictures in Their Original Format
-Create an Office XP CD slipstreamed with Service Pack 3
"MessageQuestor" <firstname.lastname@example.org> wrote in message
news:05790...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...Subtotal #4
I have a list of records as follows
I want to count the number of unique item above...ie, the
end result should show 4 (apple,orange,grape,watermelon).
Is there a formula to do this?
I suggest you look here:
for a solution by Chip Pearson
> I have a list of records as follows
> I want to count the number of unique item ...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
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 ...inventory aging report #4
What is a good report in GP 9.0 to use if you want an inventory aging
report? The stock status and historical stock status do not offer what I'm
looking for. I want to see transactions in detail and their effect on
oh, and the report needs to also show the valuation affected by inevntory
adjustments. So, the Purchase Receipts report is not good enough either.
> What is a good report in GP 9.0 to use if you want an inventory aging
> report? The stock...CRM 4 Client and offline sync error
I recently upgraded our CRM to version 4. I have the offline client
installed on my machine. When I try to go offline, I get an error during
Propagating Customizations. It says: An entity with the specified name
already exists. Please specify a unique name.
I've tried disabling all of my local data groups in an effort to try and
narrow down the issue, but even with them all decactivated I still get the
error message. I also tried unchecking all of the sync boxes under on the
options screen, doesn't seem to effect it.
Anyone have any ideas on this one?
I had the same e...