Hi, I have this worksheet that transfers folders to a new directory based on cell value (Folder Copier). In that worksheet, there is a vlookup(D7,MinMax,2) formula in cell G7. Cell G7 will change according to what is keyed in D7. Example: key 101 in D7, G7 will change to 200. key 2950 in D7, G7 will change to 3000. The problem appears when i key something more than 3000. G7 will not change higher than 3000, can anyone see what is the problem?
Attached is the file
C2:
Please Login or Register to view this content.
Ben Van Johnson
I looked at your workbook, and I think the problem is in your lookup table (Named range : MinMax = Sheet3!$B$2:$C$31), the last value in the table is 3000, so that is the last number it can use, to go higher you would have to add more values to your look-up table and expand the named range accordingly
Hope this helps
Edit-
Or, if as your table suggest, you want it to always be a multiple of 100, use protonLeah's formula
Last edited by dredwolf; 10-25-2013 at 12:55 AM.
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
Hi, thanks for your quick replies!! It's working fine now
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Hi, sorry for digging this thread up, but i found a problem.
=100*(int(D7/100)+1) works fine with any number except for multiples of 100. If i key in 100 in cell D7, it will give me a range of 101 to 200 instead of 001 to 100.
Can anyone help me with this problem? I have uploaded the file which im using now
maybe try it this way then :
=IF(MOD(D7,100),100*(INT(D7/100)+1),D7)
hope this helps
Hi dredwolf, thank you for helping. I keyed in your formula and I get a error that looks like this:
Min Max
-99 FALSE
No matter what number i key into D7, the min / max doesnt change accordingly
Not sure what to say, it works fine for me with the sample you supplied
Easy Folders 2014 (1).xls
Hi dredwolf, Thank you for your help. Maybe I didnt key in the formula correctly. I used the file that you sent back so as not to waste time figuring out which part I didnt key correctly. Thank you again
You are welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks