Help with formula #30
This formula works fine as long as there is a match in the "Customer
=IF(A3="","",INDEX('Customer Record'!B:B,MATCH(A3,'Customer Record'!
But... is there a way to modify it to allow me to enter my own value
in Column B if there is no match.
Thanks in advance!!
=IF(A3="","",IF(ISERROR(MATCH(A3,'Customer Record'!A:A,0)),"Your Value",INDEX('Customer Record'!B:B,MATCH(A3,'Customer Record'!A:A,0),1)))
...Opening multiple txt files into one workbook as separate sheets
I have seen posts about opening multiple txt files from a folder and
saving each of them as a separate .xls.
Is there a macro that would open all the .txt or .dat files in the
directory that the .xls resides and put them as a new sheet in that
IE. Folder1 data1.dat data2.dat Excel.xls
1.After running the macro in Excel.xls in Folder1 it has sheets
data1.dat and data2.dat
2. Then if I move Excel.xls to Folder 2 with data3.dat and data4.dat
and ran the macro again... Excel.xls would have data1.dat data2.dat
Maybe this page will help
http://www.rondebr...Password excel 2007
How does one set up up a password for the excel file I have
currentley have as a short cut'
Open the file (Office Button | Open ... ).
To password-protect the workbook, choose Review (ribbon) | (Changes section)
Protect Workbook. Enter a password. Click OK. Save the file.
To encrypt the file using a password, choose Office Button | Prepare |
Encrypt Document ...
"bill" <firstname.lastname@example.org> wrote in message
How does one ...deleting duplicate files from Excell
Can anyone tell me how to delete multiple files in an
Excell worksheet. It is a very large database with many
multiple files and was wondering if there was a macro i
Gloria at Habitat for Humanity
You can select the whole lot, then do data>filter advanced filter,
copy to another location (click a cell somewhere), and finally check
unique records only
"Habitat for Humanity" <email@example.com> wrote in
> Can anyone tell me how to delete multiple...How can I expose the actual column & row headers in excel?
We have a timesheet program an employee created for our company. She hid all
the formulas and calculations. I have unprotected the sheet and am trying to
adjust the code but the acutal headers are hidden and I cannot highlight them
to unhide them. (ex: the actual column headers, ABCD, ets & the row headers,
> We have a timesheet program an employee created for our company. She
> hid all
> the formulas and calculations. I have unprotected the sheet and am
> trying to
> adjust the code but the acutal headers are hidden and I cannot
> highlight...Excell defaults e-mai to expressl
In excel spreadsheet, the embedded e-mails are defaulting to Outlook Express
which is not my e-mail server.
What version of Outlook do you have installed? Is it set as your default
email client? Do other email links (such as on web pages) bring up outlook?
Diane Poremsky [MVP - Outlook]
Outlook Tips: http://www.outlook-tips.net/
Outlook & Exchange Solutions Center: http://www.slipstick.com/
Outlook Tips by email:
EMO - a weekly newsletter about Outlook and Exchange:
mailto:EMO-NEWSLETTER-SUBSCRI...Is Excel unreliable ?
Pressing F9 / Shift+F9 doesn't update my calculated cells reliably, i.e.
it just doesn't do it or gives wrongs answers. The only way to get the
right answers is to drag down the calculated cells again and then press F9
/ Shift+F9. Before going into more detail I just wanted to know if I am
encountering a common problem ?
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
I don't understand what you mean by 'drag down the calculated cell'
Also could we see the particular formula that Excel is getting wrong.
I'm not aware that ...Difference between basing pivot on excel list or other pivot table
What is the difference between basing the pivot table on excel list of other
pivot table report? thanks
The big pro for basing off another Pivot Table is the reservation of
memory. The big con is that both are linked. This means much of what you
do to one, effects the other. Perphaps the one that most get caught with
is the 'grouping' of data. Group one Pivot Table and any others based
off it will also be grouped.
I will often base one PT off another to save the user from having to
drag fields about.
** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins ...MS Excel 2000 Cell Capacity and Data Display
I'm working with a large Excel 2000 spreadsheet in which most of the cell
entries are text. Some text cell entries exceed 300 words. I'm using the
wrap text and row/column autosizing features. All of the text displays
correctly when I'm in the cell or in the formula box. When I move to
anyother cell, however, the text in the cells is cropped. How do I get all
of the text to display and print properly?
If you add alt-enters every 80-100 characters, you can see lots of characters.
You may have to manually adjust the rowheight, though.
&g...Query sorting help
I am in need of assistance with a Query. I have a query that assembles a
daily production schedule. The shedule consists of many fields however for
the sake of this question i wish to sort the query by a series of four
fields. the first fiedld in the query is "DueDate" then by field "press1"
then by "Press2" then Press3". The data in the field Due date is the actual
date due. The data in "press1" will only be "6c" or nothing. The data in
"Press2" will be one of three Strings: "25", "29", or "2...need help adding across and down
On a sheet named data I have the following
Mon Tue Wed Thu
A3 = BILL 1 1 1
A4 = FRED 1 1
A5 =TOM 1
A6 = BILL 1
A7 = TOM 1 1
I want to have a grid on a different sheet (named results) which looks at
the above data and then returns the totals for Bill on each day, the totals
for Fred each day etc. I need to see how many each did on each day
So not only does it need to 'add the totals across the sheet, it also needs
to...How can I show values, not formulas in Excel?
When I add a VLOOKUP function and fill down, the formula, not the resultant
value, is displayed in the cells. The DISPLAY FORMULA checkbox in OPTIONS in
Could be that your cells are formatted as text, format as General.
> When I add a VLOOKUP function and fill down, the formula, not the resultant
> value, is displayed in the cells. The DISPLAY FORMULA checkbox in OPTIONS in
> not checked.
Make sure it is not formatted as text
Can't get the date calculated based on other table...
It just return me the value of calDate...
Private Sub Form_Current()Dim calDate As DatecalDate = CDate(Nz(Me.
cboCalibrationDate))Me!txtShelflifeExpiry = DateAdd("d", Nz(DLookup("Days",
"Inventory", "PartNumber=" & cboPartNumber), 0), calDate)End Sub
Message posted via AccessMonster.com
Is PartNumber a text field? If so, you need quotes around the value you're
Nz(DLookup("Days", "I...how do i use the excel symbol toolbar?
Excel 2003 has a Symbol Toolbar that looks like it is designed to allow the
easy insertion of commonly used symbols. The problem is, I can't figure out
how to use it. I have tried dragging the symbols from the toolbar onto a
worksheet, copying them, double clicking them, etc... Nothing works. Does
anyone have any idea how to do this? It's not even in Excel Help.
Select the symbol and click insert button
> Excel 2003 has a Symbol Toolbar that looks like it is designed to allow the
> easy insertion of commonly used sy...Excel 2003 math error
I have a cell that evaluates to 318.43. When I multiply that cell by
156, Excel gives me 49674.51, but the correct result, when I do the
math by hand (and by Google), is 49675.08.
What the ___ is going on?
Try extending the number of places of decimal on the first cell, and you
will probably see that it is not 318.43 but something smaller (318.4263)
which has been rounded to 318.43
With your evaluation formula that results in a displayed value of 318.43 in
say C1, try
<firstname.lastname@example.org> wrote in message
news:8801150f-5557-412d-9143-9...Excel document properties insert into a cell
I would like to find a way to insert the data that is stored on the
"Properties" page of an excel document into the cell. In work and Visio I can
do it easily but I can't figure out how to do it in Excel. I know, it's
probably simple, but I just can't think that simple right now...
You will need a UDF
Function DocProps(prop As String)
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
DocProps = CVErr(xlErrValue)
and enter in a cell suc...Help with Code
Your help will be greatly appreciated. Here is what I am trying to accomplish.
I would like to give User's of the workbook an ability to "sort and filter"
while maintaining protection of the worksheet and workbook and ability to
utilize the Userform, but am running into some problems.
1. When I save, close then reopen workbook, engage the Userform and once I
click the commandbutton am promted for the password. Needless to say, the
other users won't have the password.
2.Also, have been unable to figure out vba "sort and filter", intergration
with # 1.
Below...bug in Excel?
I created a worksheet which is linked to two different "source" workbooks to
consolidate data. I am using this workbook for several consolidations (same
format and i just save the changed data under a file "source 1" and "source
2" for then automatically obtaining the sums in my CONSOLIDATION workbook)
and that works fine.
But after consolidating different workbooks and obtaining 3 different
CONSOLIDATED files (which i save under another name after consolidation) for
year1, year2 and year3, the values in those files change as soon as i open
another one!...How do I enter a decimal in a string of numbers in Excel? Example.
Please post your question in the BODY of the message, not the subject line,
and clarify with an example of the initial data and what you want to end up
with. If it's to translate 123 to 1.23, you divide by 100.
On Wed, 9 Feb 2005 15:03:07 -0800, Joy <Joy@discussions.microsoft.com> wrote:
...Please Help! How to set color for Tree Control chield items
I am using VC++ 6.0
In the project I create tree control in the window using the following code:
hwndLVt = CreateWindowEx(WS_EX_STATICEDGE,WC_TREEVIEW, "",
WS_VISIBLE | WS_CHILD |
TVS_HASLINES | TVS_HASBUTTONS |
TVS_FULLROWSELECT | TVS_INFOTIP |
...Having trouble printing selected sheets in Excel
I am trying to print my document, and I select the content.
I go to the File, and select "Print Area", then I select "set print area".
I go into the print preview, and the selected pages are at 21 %. I adjust
it back to 100 %, then select ok. I look at the document, and the same
page is repeated in the print preview. I go back through the process, of
selecting print area, setting the print area, and print preview, and the
pages are at 21 %.
I have tried right clicking, and resetting all page breaks, clearing the print
area. I have also closed the document with saving ...How Change default "look in" location in EXCEL for Importing Exte.
I am importing data contained in a text file. When I get to Import Data, the
"Look In" folder location always defaults to "My Data Sources". How do I
change that to the folder where my data is? (files are on my computer).
...Creating thousands of hyperlinks in Excel
I'm creating a huge directory and I'd like to include internal
hyperlinks. Is there a FAST way to do this? i.e. I need to create 2000
hyperlinks, leading from one cell to a cell on another worksheet.
I'd use the =hyperlink() worksheet function.
David McRitchie posted this and it might help you:
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)
>...excel formula needed
I need a formula to
1) look in colunm a, gather any numbers present in that colunm, even though
most wil be empty
2) then i want them to take that list, get rid of the empty cells and put
that data either into a new table or on another sheet.
Sounds like a sort would work. If you want a function, try this:
Enter with Ctrl+Shift+Enter, not just enter...
If this information was helpful, please indica...Too many Excel applications running at the same time
I have MS Excel 2010 installed on my computer. Every time I open up an
Excel file it opens a new Excel application in my Windows dock at the
bottom of the screen. If I open 5 Excel docs I have 5 separate Excel
applications running. Before I had Excel 2010 installed I would only
have one Excel app. running and I would see teh separate files in the
Switch Window command at the top of the screen.
What actions do I need to take so that I don't have numerous Excel
apps running, instead of only one?
I doubt if you have multiple Excel applications open.
What you probably have is a button for e...