Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
procedural help lease
i am just looking for a short description, no code, on how you would proceed
with this task. i have an idea, but just wanted some other input before i really got into it too far. looking to summarize spice blends. every blend is in a separate workbook whose name starts with a unique number then has some text description then .xls. the range of cells for the spices needed for that blend is always the same in each workbook. so in the summary workbook, if the user wanted to schedule a particular blend, they would just enter the numerical part of the filename then i would just find and open it. then i would need to populate the columns with the spices and the number of pounds needed. with multiple blends scheduled, there would be some unique spices and some that are reused, like salt, for example. so i would need to add to the number in that column, but if the spice hasn't been used yet, i would need to add a column for that spice. would you import all of the blends for the week in a "work" sheet, sort, then delete the dupes and then populate the actual schedule sheet? just looking for some ideas here, i know it's not much to go on. thanks for reading. -- Gary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
procedural help lease
i guess i should ask one other thing.
what would be the best way to open a file based on a partial filename? the known partial part is always the beginning of the filename. -- Gary "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... i am just looking for a short description, no code, on how you would proceed with this task. i have an idea, but just wanted some other input before i really got into it too far. looking to summarize spice blends. every blend is in a separate workbook whose name starts with a unique number then has some text description then .xls. the range of cells for the spices needed for that blend is always the same in each workbook. so in the summary workbook, if the user wanted to schedule a particular blend, they would just enter the numerical part of the filename then i would just find and open it. then i would need to populate the columns with the spices and the number of pounds needed. with multiple blends scheduled, there would be some unique spices and some that are reused, like salt, for example. so i would need to add to the number in that column, but if the spice hasn't been used yet, i would need to add a column for that spice. would you import all of the blends for the week in a "work" sheet, sort, then delete the dupes and then populate the actual schedule sheet? just looking for some ideas here, i know it's not much to go on. thanks for reading. -- Gary |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
procedural help lease
I would do it something like this pseudo code I would think
dim spcRng as Range, spNum as Long dim rcpeRng as Range, cell as Range dim bk as workbook worksheets("DataEntry") set spcRng = .rows(1).cells spNum =.Range("B9").Value end with set bk = workbooks.Open(spNum & ".xls") with bk.Worksheets(1) set rcpeRng = .Range("B1",.Cells(1,256).End(xltoLeft)) end with for each cell in rcpeRng res = application.match(cell,spcRng,0) if iserror(res) then res = spcrng.cells(1,"IV").End(xltoLeft)(1,2).column spcRng.Parent.Cells(1,res).Value = cell.value spcRng.Parent.Cells(2,res).Value = cell.offset(1,0).value else spcRng.Parent.Cells(2,res).Value = spcRng.Parent( _ 2,res).Value + cell.offset(1,0).value end if Next --- Regards, Tom Ogilvy "Gary Keramidas" wrote: i am just looking for a short description, no code, on how you would proceed with this task. i have an idea, but just wanted some other input before i really got into it too far. looking to summarize spice blends. every blend is in a separate workbook whose name starts with a unique number then has some text description then .xls. the range of cells for the spices needed for that blend is always the same in each workbook. so in the summary workbook, if the user wanted to schedule a particular blend, they would just enter the numerical part of the filename then i would just find and open it. then i would need to populate the columns with the spices and the number of pounds needed. with multiple blends scheduled, there would be some unique spices and some that are reused, like salt, for example. so i would need to add to the number in that column, but if the spice hasn't been used yet, i would need to add a column for that spice. would you import all of the blends for the week in a "work" sheet, sort, then delete the dupes and then populate the actual schedule sheet? just looking for some ideas here, i know it's not much to go on. thanks for reading. -- Gary |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
procedural help lease
You might want to try the Consolidate feature from the Data menu
It allows you to bring together sheets and sum like fields so your salt fields will be added and any unique fields will be added to the list of spices. it can be done manually or programatically hope this helps David "Gary Keramidas" wrote: i am just looking for a short description, no code, on how you would proceed with this task. i have an idea, but just wanted some other input before i really got into it too far. looking to summarize spice blends. every blend is in a separate workbook whose name starts with a unique number then has some text description then .xls. the range of cells for the spices needed for that blend is always the same in each workbook. so in the summary workbook, if the user wanted to schedule a particular blend, they would just enter the numerical part of the filename then i would just find and open it. then i would need to populate the columns with the spices and the number of pounds needed. with multiple blends scheduled, there would be some unique spices and some that are reused, like salt, for example. so i would need to add to the number in that column, but if the spice hasn't been used yet, i would need to add a column for that spice. would you import all of the blends for the week in a "work" sheet, sort, then delete the dupes and then populate the actual schedule sheet? just looking for some ideas here, i know it's not much to go on. thanks for reading. -- Gary |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
procedural help lease
thanks for your time tom. i'll check it out. -- Gary "Tom Ogilvy" wrote in message ... I would do it something like this pseudo code I would think dim spcRng as Range, spNum as Long dim rcpeRng as Range, cell as Range dim bk as workbook worksheets("DataEntry") set spcRng = .rows(1).cells spNum =.Range("B9").Value end with set bk = workbooks.Open(spNum & ".xls") with bk.Worksheets(1) set rcpeRng = .Range("B1",.Cells(1,256).End(xltoLeft)) end with for each cell in rcpeRng res = application.match(cell,spcRng,0) if iserror(res) then res = spcrng.cells(1,"IV").End(xltoLeft)(1,2).column spcRng.Parent.Cells(1,res).Value = cell.value spcRng.Parent.Cells(2,res).Value = cell.offset(1,0).value else spcRng.Parent.Cells(2,res).Value = spcRng.Parent( _ 2,res).Value + cell.offset(1,0).value end if Next --- Regards, Tom Ogilvy "Gary Keramidas" wrote: i am just looking for a short description, no code, on how you would proceed with this task. i have an idea, but just wanted some other input before i really got into it too far. looking to summarize spice blends. every blend is in a separate workbook whose name starts with a unique number then has some text description then .xls. the range of cells for the spices needed for that blend is always the same in each workbook. so in the summary workbook, if the user wanted to schedule a particular blend, they would just enter the numerical part of the filename then i would just find and open it. then i would need to populate the columns with the spices and the number of pounds needed. with multiple blends scheduled, there would be some unique spices and some that are reused, like salt, for example. so i would need to add to the number in that column, but if the spice hasn't been used yet, i would need to add a column for that spice. would you import all of the blends for the week in a "work" sheet, sort, then delete the dupes and then populate the actual schedule sheet? just looking for some ideas here, i know it's not much to go on. thanks for reading. -- Gary |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
procedural help lease
thanks for the idea
-- Gary "dkinn" wrote in message ... You might want to try the Consolidate feature from the Data menu It allows you to bring together sheets and sum like fields so your salt fields will be added and any unique fields will be added to the list of spices. it can be done manually or programatically hope this helps David "Gary Keramidas" wrote: i am just looking for a short description, no code, on how you would proceed with this task. i have an idea, but just wanted some other input before i really got into it too far. looking to summarize spice blends. every blend is in a separate workbook whose name starts with a unique number then has some text description then .xls. the range of cells for the spices needed for that blend is always the same in each workbook. so in the summary workbook, if the user wanted to schedule a particular blend, they would just enter the numerical part of the filename then i would just find and open it. then i would need to populate the columns with the spices and the number of pounds needed. with multiple blends scheduled, there would be some unique spices and some that are reused, like salt, for example. so i would need to add to the number in that column, but if the spice hasn't been used yet, i would need to add a column for that spice. would you import all of the blends for the week in a "work" sheet, sort, then delete the dupes and then populate the actual schedule sheet? just looking for some ideas here, i know it's not much to go on. thanks for reading. -- Gary |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
procedural help lease
ended up just using this to get the file name to open:
FileToOpen = Dir(fPath & fName & "*.xls") -- Gary "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... i guess i should ask one other thing. what would be the best way to open a file based on a partial filename? the known partial part is always the beginning of the filename. -- Gary "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... i am just looking for a short description, no code, on how you would proceed with this task. i have an idea, but just wanted some other input before i really got into it too far. looking to summarize spice blends. every blend is in a separate workbook whose name starts with a unique number then has some text description then .xls. the range of cells for the spices needed for that blend is always the same in each workbook. so in the summary workbook, if the user wanted to schedule a particular blend, they would just enter the numerical part of the filename then i would just find and open it. then i would need to populate the columns with the spices and the number of pounds needed. with multiple blends scheduled, there would be some unique spices and some that are reused, like salt, for example. so i would need to add to the number in that column, but if the spice hasn't been used yet, i would need to add a column for that spice. would you import all of the blends for the week in a "work" sheet, sort, then delete the dupes and then populate the actual schedule sheet? just looking for some ideas here, i know it's not much to go on. thanks for reading. -- Gary |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lease commitments | Excel Worksheet Functions | |||
How do I calculate a lease? | Excel Discussion (Misc queries) | |||
sale or lease? | New Users to Excel | |||
print procedural question | Excel Programming | |||
IRR&NPV Financial lease | Excel Discussion (Misc queries) |