I am running excel 2003 and I am looking for a function that will display the filename or part of the file name in a cell. I know you can do this as part of a header or footer but is there anyway to have an individual cell display the file name of the document?
View 9 RepliesExcell 2003
I put = cell("filename") in cell A1 on every sheet to display the full pathname of that sheet.
it seems that the path does not always display the correct sheet name- rather it seems
that when changing from one sheet to another the path is not always updated.
Is there a switch that needs to be activated to ensure that the path is updated correctly when changing sheets on the same worksheet?
The worksheet perfectly performs as expected, big thanks to JBeaucaire again. Now, as am looking around of the file and doing some exploration on the array, as I was trying to make this applicable to real life application, I noticed the following mysteries...
1. When I tried to delete some rows, The Members Sheet output went wrong and displaying #N/A. I noticed it's because the formula range have change. But it would be hard for a new user to re-arrange it over and over again everytime when a row is deleted.
2. I've been trying to solve this problem to no avail. I found this link that gives me an idea how my worksheet should work, this was already solved but I can't apply it on my worksheet...
http://www.excelforum.com/excel-prog...nd-resort.html
3. I would like to add another column on A to put the 0 and 1 on, but I don't need to delete this column after the resort so that next time if I need to delete another row existing row i'll just replace the existing 0 with 1 on column A.
Example:....
i m wanting Cell B1 to display true if any of the values in column C match otherwise I want to display false.
A 1
B 0
C 0
D 0
But I can't determine how to get this done.
I need two formulas for my workbook. This is what the formula for Cell B6 needs to accomplish: I need Cell B6 to display what I have entered in Cell A6 but only if Cell A1 is greater than 1. This is what the formula for Cell C6 needs to accomplish: I need Cell C6 to display what I have entered in Cell A6 but only if Cell A1 is greater than 2.
View 2 Replies View RelatedI have the following code and I it works except it gives the full file path rather than just the name of the file.
I have been racking my brain for ages to try and figure how to just get the filename to show but I have been unsuccessful.
Code:
Private Sub CheckBox1_Click()
Range("K43").Select
' Set up list of file filters
[Code].....
I'm trying to replace Forecast12.xls to SEPFCAST Forecast12.xls but I keep geting error message that says "the name you entered is not valid" and gives me 3 reasons. Both the location of the file is the same and I have spelt it right. Im not sure what is causing this error as it let me change in a different sheet to the file name as above.
My other option is to link manually about 500 cells which i dont think is an option.
I'm currently using this formula:
=INDEX(B4:B64,MATCH(I5,A4:A64,0))
If I5's value is displayed more than 1 time in Column A, I want the information in Column B to appear in the same cell the formula is in in Column J
Im currently studying for an ICT A level and im in my first year doing AS. Im working the AQA board and i have a project that i have a target for an A! I need to create a system for a business and write a report on everything i did...wont go into detail. Anyway. i am having trouble with an IF FUNCTION!!! I have a cell (E35) that contains a percentage.
I want the IF function to display a message depending on what the percentage in cell E35 shows;
IF, 0>> Display "Bad"
40>> Display "OK"
70>> "Good"
90>> Display "Very Good"
E35>=100 >>>> Display "Excellent"
I have tried so many things and I was so sure i entered it correctly, but it keeps coming up with an error.
The following are on a sheet:
A1 = 5700
B1 = 235
C1 = 17:14
D1 = 5922
$E$1 = 09:01
$F$1= 3
$E$1 and $F$1 are (the only) absolutes/constants.
A2 contains the following:
=IF(A1>D1,B1,IF(A1+B1<D1,"d",IF(AND(C1<$E$1,B1<$F$1),"",B1))))
This translates as:
=IF(5700>5922,235,IF(5700+235<5922,"d",IF(AND(7:14<09:01,235<3),"",235))))
The result is a variable/number (235), "d" or a blank cell ("").
This formula is in a column and works fine where there are numbers in corresponding cells. The problem arises with corresponding cells which appear blank (show no values) but contain references to other cells: they result in a "d" when nothing should be displayed.
So while a corresponding blank cell is correct if it shows no values, it gives me this problem - I don't want "d" or anything.
I am trying to display the Filename in a cell on my worksheet. However, when I enter the =Cell("filename"), it provides the full path (C:/Documents......Cost Summary.xls) How can I display only the filename without the path?
View 3 Replies View RelatedI am trying to get a MsgBox to appear if the cell value within a given range exceeds a certain number value in my case "8". The only issue I am experiencing is that the code seems to take into account any input from any cell, not just the range specified, and additionally since there are 7 columns in the range the MSgBox pops up 7 times, and I need it only once and if the cell value exceeds "8" only when input.
ideally i would like to do a named range as there as there is a block of data where this will need to repeat.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
With ActiveSheet
For Each cell In Range("J40:P40")
If cell.Value [A35].Value Then
MsgBox "Red Cell cannot exceed 8 hours per shift. Please correct this."
End If
Else
End If
Next
End With
End Sub
Is it possible to do the following?
if cell A1 = 6 and cell A2 =5.8 (0.2 below A1) colour cell A2 orange
if cell A1 = 6 and cell A2 =5.5 or below (0.5 or more below A1) colour cell A2 red
if cell A1 = 6 and cell A2 =6.2 / 6.5 / 6.8 (0.2/0.5/0.8 above A1) colour cell A2 green
if cell A1 = 6 and cell A2 =7.2 or above (1.2 or above A1) colour cell A2 green
Hi, sorry for posting this twice but i wasn't sure which category it fell under!
I have a slight problem with excel and and was wondering if anyone can help.
I have 3 columns, A, B, C: .....
I have accounts that I need to compare to see if they exist on my system the account that has a listed date, exist on my system then if I can fill the dates in the accounts the match then I will be able to delete the other accounts that don’t have a date see attach file for more understanding.
View 2 Replies View RelatedI'm only starting to get into the Macro side of excel, and I've created a couple of macros to automatically format cell contents and the like.
However I'm having trouble trying to divide some cell contents by 100.
I have 2 files I'm working with, one contains information regarding cd's and percentages, however the percentages in this file cannot be formatted to percentages (so the powers that be say) for whatever reason.
I copy all this information into another file which does have the percentages formatted as percentages, the result is that the values get multiplied by 100.
So values that read:
100
50
50.25
for example, appear on the new sheet as:
10000%
50000%
5025%
Is it possible to write a macro that will divide these percentages by 100 so the read correctly as:
100%
50%
50.25%
the macro will have to work on selected ranges.
Is there anyone who can suggest a solution to my problem below ?
Basically, what I need is a macro which would ideally work like this:
if D32 = 1, then the background colour of D4 should be red
if D32 = 2, then the background colour of D4 should be orange
if D32 = 3, then the background colour of D4 should be yellow
if D32 = 4, then the background colour of D4 should be green
And then I plan to use it for columns E, F, G
I have created a standard input box for the date (UK)
datereceived = InputBox("Please enter the DATE the text message was received, please enter in format DD/MM/YY", , Date)
Sheet1. Range("e" & x).Value = datereceived
I have formatted the cells for row 'E' for 'Date' and DD/MM/YYYY in English UK, But when I have inputted the date into the input box ( e.g. 01/05/2008 ), it inputs into the spreadsheet 05/01/2008 (American Obviously),
Is there a way to take filename and place value in a cell
View 9 Replies View RelatedCELL("filename") gives you the name of the current workbook but if you switch to another workbook and then come back, the name has switched to the other workbook. You have to refresh to make the name switch back to that of the workbook you used the formula in.
What alternatives exist, short of writing some VBA, which is an option, to fetching the name of the workbook via a formula and having it remain the name of that workbook when you go away and come back again?
I would like to modify the below so that the contents of cell D2 of the current file becomes the file name.
View 2 Replies View RelatedGOAL: Have an Excel file renamed automatically using the contents of a cell in the first sheet. DETAILS: The cell with the new file name is always D8. The cell is blank initially (although it doesn't have to be). OTHER INFO: OS= WIN 7 Excel= 2007
EXAMPLE: If I input "Mike T" into cell D8, the name of the file will be Mike T.xls
Is it possible to insert the filename of another excel file based on the information in a cell within your current file?
I have a file named 12-07.xlsx, which has a sheet Data 12-07, and that sheet has information all through it. I want to populate a cell in a new file with inform from the 12-07.xlsx cell B3.
I know the Formula is ='[12-07.xlsx]Data 12-07'!$B$3
Is there a way that I can use a cell within the current file, say cell B1 (which has a value of 12-07 - which is the same as the file I want) to populate the filename? Something like
='["=B1".xlxs]Data 12-07'!$B$3 (whereas "=B1" would be 12-07)
I know that will not work as is, but is this concept doable?
How can I have the filename stored in a certain cell of the worksheet?
View 4 Replies View RelatedWhen the user goes to File, Save As, I would like the filename to be populated from the contents of cells B2 and B3, i.e. 11/18/09-23. Is this possible? Also in the same code, can I specify that I want the destination folder to be K:Sheets?
View 11 Replies View RelatedHow to print sheet1 into a PDF file with the file-name given in cell A1.
View 9 Replies View RelatedI would like code to automatically save my spreadsheet with the name that is present in cell F4 of the activesheet. For example, I would like to save my file to desktop and cell F4 contains the text 'hitec 348'
View 5 Replies View RelatedOne folder on the drive contains excel files, which are created daily from a template. I use these files, the 20 most recent days' worth, to create an analysis file. This new file is organized in rows, starting with the date. The files I am using are all saved in the form "Stats yyyy mm-dd.xls" The first line of each row of the analysis spreadsheet starts with the date, in the format "yyyy mm-dd". Is it possible to create a macro, or in another way, for the analysis spreadsheet to automate the reading-in of data from these different files?
View 2 Replies View RelatedI have tried =INDIRECT but that will not work.
I have a file name typed into cell C3. I want the formula in cell C4 (C5,C6,C7......) to include the name of the file typed into cell C3. The formula in cell C5 shows what the content should look like.
I want the users to be able to type the file name in Row 3 (C3,D3,E3.......) and have the formulas below them insert the file name and access the information from the files (yes the files can/will be open when we do this).
I need to to have at least twenty different formulas (rows) referencing specific cells in these files.
The formulas would look like this ='["File name from C3"]Audit Information'!$D$35.
I am hoping it may only be a syntax thing and someone here can "show me the way" I have tried &$C$3& with and without quotations with no luck.
Here is the test file. Test3.xlsm‎
I have a formula that looks like this : ='G:STOCKS[1001 Stock.xls]Matl%'!$I$32
There are hundreds of similar formulas, all referencing different cells in the same file. The “1001” bit refers to the date, and next month it will need to say “1002” to pick up the new month's file. I would like to hold the “1002” bit in a cell (say A1), so that at month end, I just change that one cell, and all the formulas will point to the new file, but I’m not sure how to work that into the formula.