Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sumif *********************** Help
Hi everyone,
I was wondering if anyone can help with my problem. I have a workbook containing three worksheets; 'sheet1', 'sheet2' and 'sheet3'. And; 1. On 'sheet1' I have name of the months from B5 to AO5 - i.e. Jan - 05 to Apr - 08 : I simply typed Jan 05 in B5 then dragged to AO5. 2. 'sheet1' Cell A1 contains the name of the workbook "ABC123" 3. From 'sheet1" cell A6 till A25. I have codes in combination of number and alphabet structure. - i.e. OTO35, VOF05 etc ( 3 letters and 2 number at the end ) 4. On my directory 'C:\Documents\Database' Under the Database folder I have many different kinds of representative name folders such as "ABC123", "DEF456", "ABB768" and so on. 5. Inside of this each name folder, It has 4 different year folders called "2005", "2006", "2007" and "2008". Inside of these year folders, I have excel files name in month as this "Jan 05", "Feb 05" all the way to "Dec 05" and so on. It is same with other year folders except the last two number digit from the name where in folder "2007", it will have excel file name "Jan 07" ~"Dec 07". 6. On 'sheet1' cell B6 which will be underneath cell B5(Jan-05), I need to open 'C:\Documents\Database'\ABC123\2005\Jan 05.xls' file then do the sumif on cell A6 which contains the code "OTO35". === Here is the problem that I can't solve. What I can do with my small brain in VBA is to actually tell it to open up the file under directory 'C:\Documents\Database'\ABC123\2005\Jan 05.xls' then do sumif of A6 on Jan 05.xls Range A:J and sum data column J, then put it on B6. - Is there any possible way to program in VBA in such way that when msgbox pops up at the very start and I just type in the file name "ABC123" or refer to cell A1 or whatever, Open up the correct file on correct month then do a sumif automatically? i.e. A. if file name is ABC123 for this report then go to path 'C: \Documents \Database'\ABC123\ if file name is DEF456 then go to path 'C:\Documents \Database'\DEF456\ B. Then, if it is Jan-05 go to correct year folder under then open up correct xls file. i.e. 'C:\Documents\Database'\ABC123\2005\Jan 05.xls C. Do a sumif of A6 from Jan 05 file from Range A:J and sum data on column J. D. Repeat this step from Jan - 05 to Apr - 08 (B5 to AO5) then getting results cells underneath. I am pretty sure there will be alot better and efficient way of doing this. If anyone can help or give me an advice how this can be done, that would be wonderful! Thanks guys!! :D |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
SumIf | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |