Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create filename based on cell contents
Interesting opportunity, running Excel 2000 on Windows 2000 and Excel XP on
Windows XP. Need to update a number of cells in approximately 50 workbooks with 4 to 8 worksheets in each! Each workbook contains records for 1 calendar week. Need to pull last weeks ending numbers into this weeks beginning number. The formula I used would require a lot of additioanl data input into each workbook, so I think I am looking for a custom function. CAn anyone suggest a way to accomplish the following particulars: Beginning in row 4 Column "A" contains serial numbers of equipment Column "C" holds last week's ending reading Column "D" holds this weeks ending reading cell C3 contains last week's date and D3 contains this week's date Want to generate a filename using the day, month, and year from cell C3. the filename is in the format sampleDDmonYYYY.xls where mon is teh 3 letter name of teh month, for example "sample07Jan2007.xls" I plan to use the results of this routine in a Vlookup function to import(link) the ending reading for each serial number in the previous week's worksheet into the current worksheet. I am a VBA newbie, so any suggetsion swill be greatly appreciated. Thanks in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create filename based on cell contents
filename = "sample" & Format(Range("A1"), "ddmmmyyyy") & ".xls"
Regards, Stefi JR Hester ezt *rta: Interesting opportunity, running Excel 2000 on Windows 2000 and Excel XP on Windows XP. Need to update a number of cells in approximately 50 workbooks with 4 to 8 worksheets in each! Each workbook contains records for 1 calendar week. Need to pull last weeks ending numbers into this weeks beginning number. The formula I used would require a lot of additioanl data input into each workbook, so I think I am looking for a custom function. CAn anyone suggest a way to accomplish the following particulars: Beginning in row 4 Column "A" contains serial numbers of equipment Column "C" holds last week's ending reading Column "D" holds this weeks ending reading cell C3 contains last week's date and D3 contains this week's date Want to generate a filename using the day, month, and year from cell C3. the filename is in the format sampleDDmonYYYY.xls where mon is teh 3 letter name of teh month, for example "sample07Jan2007.xls" I plan to use the results of this routine in a Vlookup function to import(link) the ending reading for each serial number in the previous week's worksheet into the current worksheet. I am a VBA newbie, so any suggetsion swill be greatly appreciated. Thanks in advance |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create filename based on cell contents
Sorry, with C3
x = "sample" & Format(Range("C3"), "ddmmmyyyy") & ".xls" Stefi JR Hester ezt *rta: Interesting opportunity, running Excel 2000 on Windows 2000 and Excel XP on Windows XP. Need to update a number of cells in approximately 50 workbooks with 4 to 8 worksheets in each! Each workbook contains records for 1 calendar week. Need to pull last weeks ending numbers into this weeks beginning number. The formula I used would require a lot of additioanl data input into each workbook, so I think I am looking for a custom function. CAn anyone suggest a way to accomplish the following particulars: Beginning in row 4 Column "A" contains serial numbers of equipment Column "C" holds last week's ending reading Column "D" holds this weeks ending reading cell C3 contains last week's date and D3 contains this week's date Want to generate a filename using the day, month, and year from cell C3. the filename is in the format sampleDDmonYYYY.xls where mon is teh 3 letter name of teh month, for example "sample07Jan2007.xls" I plan to use the results of this routine in a Vlookup function to import(link) the ending reading for each serial number in the previous week's worksheet into the current worksheet. I am a VBA newbie, so any suggetsion swill be greatly appreciated. Thanks in advance |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create filename based on cell contents
I received a #Name error when entering that formula. My version of Excel has
no "Format" function to call. Is that part of an addin function set? If yes, which one? "Stefi" wrote: Sorry, with C3 x = "sample" & Format(Range("C3"), "ddmmmyyyy") & ".xls" Stefi JR Hester ezt *rta: Interesting opportunity, running Excel 2000 on Windows 2000 and Excel XP on Windows XP. Need to update a number of cells in approximately 50 workbooks with 4 to 8 worksheets in each! Each workbook contains records for 1 calendar week. Need to pull last weeks ending numbers into this weeks beginning number. The formula I used would require a lot of additioanl data input into each workbook, so I think I am looking for a custom function. CAn anyone suggest a way to accomplish the following particulars: Beginning in row 4 Column "A" contains serial numbers of equipment Column "C" holds last week's ending reading Column "D" holds this weeks ending reading cell C3 contains last week's date and D3 contains this week's date Want to generate a filename using the day, month, and year from cell C3. the filename is in the format sampleDDmonYYYY.xls where mon is teh 3 letter name of teh month, for example "sample07Jan2007.xls" I plan to use the results of this routine in a Vlookup function to import(link) the ending reading for each serial number in the previous week's worksheet into the current worksheet. I am a VBA newbie, so any suggetsion swill be greatly appreciated. Thanks in advance |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create filename based on cell contents
It's not a formula, it is VBA code. You can't name any files using formulas
http://www.mvps.org/dmcritchie/excel/install.htm -- Regards, Peo Sjoblom "JR Hester" wrote in message ... I received a #Name error when entering that formula. My version of Excel has no "Format" function to call. Is that part of an addin function set? If yes, which one? "Stefi" wrote: Sorry, with C3 x = "sample" & Format(Range("C3"), "ddmmmyyyy") & ".xls" Stefi "JR Hester" ezt rta: Interesting opportunity, running Excel 2000 on Windows 2000 and Excel XP on Windows XP. Need to update a number of cells in approximately 50 workbooks with 4 to 8 worksheets in each! Each workbook contains records for 1 calendar week. Need to pull last weeks ending numbers into this weeks beginning number. The formula I used would require a lot of additioanl data input into each workbook, so I think I am looking for a custom function. CAn anyone suggest a way to accomplish the following particulars: Beginning in row 4 Column "A" contains serial numbers of equipment Column "C" holds last week's ending reading Column "D" holds this weeks ending reading cell C3 contains last week's date and D3 contains this week's date Want to generate a filename using the day, month, and year from cell C3. the filename is in the format sampleDDmonYYYY.xls where mon is teh 3 letter name of teh month, for example "sample07Jan2007.xls" I plan to use the results of this routine in a Vlookup function to import(link) the ending reading for each serial number in the previous week's worksheet into the current worksheet. I am a VBA newbie, so any suggetsion swill be greatly appreciated. Thanks in advance |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create filename based on cell contents
Thanks for clarifying that for me. Guess I will have to find some other way
to solve this, because I don't understand how to properly set this up in VBA. Thanks again for the clarification "Peo Sjoblom" wrote: It's not a formula, it is VBA code. You can't name any files using formulas http://www.mvps.org/dmcritchie/excel/install.htm -- Regards, Peo Sjoblom "JR Hester" wrote in message ... I received a #Name error when entering that formula. My version of Excel has no "Format" function to call. Is that part of an addin function set? If yes, which one? "Stefi" wrote: Sorry, with C3 x = "sample" & Format(Range("C3"), "ddmmmyyyy") & ".xls" Stefi "JR Hester" ezt *rta: Interesting opportunity, running Excel 2000 on Windows 2000 and Excel XP on Windows XP. Need to update a number of cells in approximately 50 workbooks with 4 to 8 worksheets in each! Each workbook contains records for 1 calendar week. Need to pull last weeks ending numbers into this weeks beginning number. The formula I used would require a lot of additioanl data input into each workbook, so I think I am looking for a custom function. CAn anyone suggest a way to accomplish the following particulars: Beginning in row 4 Column "A" contains serial numbers of equipment Column "C" holds last week's ending reading Column "D" holds this weeks ending reading cell C3 contains last week's date and D3 contains this week's date Want to generate a filename using the day, month, and year from cell C3. the filename is in the format sampleDDmonYYYY.xls where mon is teh 3 letter name of teh month, for example "sample07Jan2007.xls" I plan to use the results of this routine in a Vlookup function to import(link) the ending reading for each serial number in the previous week's worksheet into the current worksheet. I am a VBA newbie, so any suggetsion swill be greatly appreciated. Thanks in advance |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create filename based on cell contents
After some additional research, reading, and examples I managed to get the
following code to work in one spreadsheet: Function LWT(lwd) LWT="[sample"&Format(lwd, "ddmmmyyyy")&"more test here" End Function Here is where my inexperience with VBA really shows up. This works great in the worksheet where I first tried it. I then copied and pasted it into the VBA code of the spreadsheet where I need it to perform; alas it does not work. Thinking it might be in the copy&paste methodology, I deleted it from the destination and manually typed it in. Both methods return the #Name error. What should I look for in the worksheet/workbook to correct this? Thanks again! "Stefi" wrote: Sorry, with C3 x = "sample" & Format(Range("C3"), "ddmmmyyyy") & ".xls" Stefi JR Hester ezt *rta: Interesting opportunity, running Excel 2000 on Windows 2000 and Excel XP on Windows XP. Need to update a number of cells in approximately 50 workbooks with 4 to 8 worksheets in each! Each workbook contains records for 1 calendar week. Need to pull last weeks ending numbers into this weeks beginning number. The formula I used would require a lot of additioanl data input into each workbook, so I think I am looking for a custom function. CAn anyone suggest a way to accomplish the following particulars: Beginning in row 4 Column "A" contains serial numbers of equipment Column "C" holds last week's ending reading Column "D" holds this weeks ending reading cell C3 contains last week's date and D3 contains this week's date Want to generate a filename using the day, month, and year from cell C3. the filename is in the format sampleDDmonYYYY.xls where mon is teh 3 letter name of teh month, for example "sample07Jan2007.xls" I plan to use the results of this routine in a Vlookup function to import(link) the ending reading for each serial number in the previous week's worksheet into the current worksheet. I am a VBA newbie, so any suggetsion swill be greatly appreciated. Thanks in advance |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create filename based on cell contents
Found my error!
I had failed to switch to MODULE mode, when I entered the code into the VBA window. Hopefully I won't make that mistake again! Thanks to all who've helped me in this endeavor. "JR Hester" wrote: After some additional research, reading, and examples I managed to get the following code to work in one spreadsheet: Function LWT(lwd) LWT="[sample"&Format(lwd, "ddmmmyyyy")&"more test here" End Function Here is where my inexperience with VBA really shows up. This works great in the worksheet where I first tried it. I then copied and pasted it into the VBA code of the spreadsheet where I need it to perform; alas it does not work. Thinking it might be in the copy&paste methodology, I deleted it from the destination and manually typed it in. Both methods return the #Name error. What should I look for in the worksheet/workbook to correct this? Thanks again! "Stefi" wrote: Sorry, with C3 x = "sample" & Format(Range("C3"), "ddmmmyyyy") & ".xls" Stefi JR Hester ezt *rta: Interesting opportunity, running Excel 2000 on Windows 2000 and Excel XP on Windows XP. Need to update a number of cells in approximately 50 workbooks with 4 to 8 worksheets in each! Each workbook contains records for 1 calendar week. Need to pull last weeks ending numbers into this weeks beginning number. The formula I used would require a lot of additioanl data input into each workbook, so I think I am looking for a custom function. CAn anyone suggest a way to accomplish the following particulars: Beginning in row 4 Column "A" contains serial numbers of equipment Column "C" holds last week's ending reading Column "D" holds this weeks ending reading cell C3 contains last week's date and D3 contains this week's date Want to generate a filename using the day, month, and year from cell C3. the filename is in the format sampleDDmonYYYY.xls where mon is teh 3 letter name of teh month, for example "sample07Jan2007.xls" I plan to use the results of this routine in a Vlookup function to import(link) the ending reading for each serial number in the previous week's worksheet into the current worksheet. I am a VBA newbie, so any suggetsion swill be greatly appreciated. Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup Cell Contents Filename | Excel Worksheet Functions | |||
How do I add a cell based on another cells contents? | Excel Discussion (Misc queries) | |||
Cell Contents to identify source Filename | Excel Discussion (Misc queries) | |||
How do i create a macro that saves the filename that is equal to a cell in the sheet? | Excel Discussion (Misc queries) | |||
Delete row based on contents of cell | Excel Discussion (Misc queries) |