Rusty_Shackleford
New Member
- Joined
- May 28, 2015
- Messages
- 1
So here's a little challenge I'm having that I don't think excel's default tool set is equipped to handle. If anyone can get this to work it would be very helpful to me. While I know a little computer logic, I don't know the language excel uses, and I haven't played around with the macro coding at all yet. I'm sure I could learn a lot from your solutions. 
What I have:
http://www.dropbox.com/sh/2jubbthflerhbh2/AADPsNOTlM51op2zXy2UhIgua?dl=0
What I need to do:
-Cells under collumn labeled "Total Drawings In Folder" need to be equipped with a formula that will count the files in a folder when provided with that folder's path. The path will be assembled with data from the same row as explained below:
1. Reference the "File Path" cell for that row
2. Add "\"
3. Reference the "Sharepoint Folder Name" cell for that row
4. Add "\PDFs"
Example:
=M7&"\"&I7&"\PDFs"
You can find code to perform the file counting function here:
www.vbaexpress.com/kb/getarticle.php?kb_id=238
However this code did not allow me to use cell references (I7, A3, etc.) within the new formula it creates. It will have to be modified to allow for this, and I don't really know where to begin with that. Cells under collumn Labeled "Total Drawings In Folder" Should automatically update at least every session.
-"Sharepoint Folder Name" collumn should sort its content alphanumerically, and update whenever a new item is added to the collumn
Example:
536 should come before A36
150B140 should come before 150C120
There might be a way to do this with excel's default tool set, but once again, I don't really know where to begin to look.
-Ideally, the collumns should be able to be extended indefinitely by simple copy-pasting of cells without requiring any rewriting of formulae. If this condition can't be satisfied, the formulae can can be set to function down to row 2000.
What I have:
http://www.dropbox.com/sh/2jubbthflerhbh2/AADPsNOTlM51op2zXy2UhIgua?dl=0
What I need to do:
-Cells under collumn labeled "Total Drawings In Folder" need to be equipped with a formula that will count the files in a folder when provided with that folder's path. The path will be assembled with data from the same row as explained below:
1. Reference the "File Path" cell for that row
2. Add "\"
3. Reference the "Sharepoint Folder Name" cell for that row
4. Add "\PDFs"
Example:
=M7&"\"&I7&"\PDFs"
You can find code to perform the file counting function here:
www.vbaexpress.com/kb/getarticle.php?kb_id=238
However this code did not allow me to use cell references (I7, A3, etc.) within the new formula it creates. It will have to be modified to allow for this, and I don't really know where to begin with that. Cells under collumn Labeled "Total Drawings In Folder" Should automatically update at least every session.
-"Sharepoint Folder Name" collumn should sort its content alphanumerically, and update whenever a new item is added to the collumn
Example:
536 should come before A36
150B140 should come before 150C120
There might be a way to do this with excel's default tool set, but once again, I don't really know where to begin to look.
-Ideally, the collumns should be able to be extended indefinitely by simple copy-pasting of cells without requiring any rewriting of formulae. If this condition can't be satisfied, the formulae can can be set to function down to row 2000.
