Posts by Rowddawg

    Re: Recurring projects in a week


    I think this can be done with an array fairly easily, just don't have the time to give it right now. I can do it with if/isna/match statements, but that is a little longer for the input. If you want that I can give it. Not sure you need vba unless you just want it. Either way I'll check back later.

    Re: Hiding rows after the row number referenced in a specific cell


    Thank you. Wanted to make sure I understand clearly, like I noted earlier i'm self taught. By helper cell you mean I broke downt he formula and left it in multiple cells instead of putting everything back into one cell, correct? IF that's the case then absolutely, there are quite a few there. I need to get in the habit of combining everything into one cell, which I'm sure would lessen the columns I use.


    Thank you again.

    Re: Hiding rows after the row number referenced in a specific cell


    https://www.dropbox.com/s/3bg0…%20Master%20revised2.xlsm


    Not sure about the best way to link this. It's too large to add as an attachement. First two sheets can have info dropped in them, 3rd sheet pulls info from both and is hidden. 4th sheet 'data' is where they view/sort results.


    The sheet is normally protected, and each macro unlocks/locks after use. I've changed the password to blank, but you'll still have to unlock the sheet each time you press one of the buttons if you need to.


    The data sheet is what is referenced in the cells from my previous response.

    Re: Hiding rows after the row number referenced in a specific cell


    The worksheet sorts two separate lists, and I use a combination of cell, and vlookup to return what row is the last one used out of both lists. That result is listed in AI4. Anything I can do to clean it up or be more efficient I would love to know. I've basically self taught myself excel and am learning a great deal by watching these forums. Specifically, here's the code for one row, and it's copied down the list to row 300. AG4 =IF(AND(A4=0,T4=0),1,0) AH4 =CELL("row",AG4) AI4 =IF(VLOOKUP(1,AG4:AH300,2,FALSE)<38,38,(VLOOKUP(1,AG4:AH300,2,FALSE))) I set the number to be 38 at the smallest so an entire page fits normally and the rest is cut off when the results are only a few rows. Thanks for any help, and for the help already given. I can link the sheet if that's preferred.

    Re: Excel formula for project dates - kind of confused!!


    In another cell of your sheet, enter the following formula. (I used F5) =today() That enters today's date in that cell. Then you use that cell to figure your two needs, so for days completed it would be =IF(($F5-$A5)<0,0,$F5-$A5) For days remaining it's the same thing, only =IF(($B2-$F2)<0,0,$B2-$F2) That way both return 0's when needed.

    Re: Search spreadsheet for quantities of part numbers occurring multiple times


    Can you link a copy of the spreadsheet you're exporting from AutoCAD? Would help give more specific results. There's a post here http://www.ozgrid.com/forum/showthread.php?t=25239 that shows part of what I think the solution could be... since you'll have items that you will be looking up by number that will have multiple variables after the item #. Also this shows some solutions that are through vb and that are not...

    Re: Vlookup needs revision


    Also, may want to change the format on cells j:n since it drops all the way down to 43k. That may speed up your file if you've noticed it running slowly at all.

    Re: Vlookup needs revision


    =IF(K6="usd",J6,IF(K6="eur",J6*1.3194,IF(K6="mxn",J6*0.079,IF(K6="jpn",J6*0.01,0)))) That returns j6 modified by the rates you currently listed. You could also set up in your file 3 spots for eur, jpn, and mxn and reference those instead of the values in this formula, which would let you change the rates much easier in my opinion. Hope this helps.

    Re: Vlookup using indirect to determine which column to get data from


    This is what I'm understanding you want to do: Whatever value is inserted into column B for the vendor, you want column a to show the matching value in the table... If that's correct, use an hlookup instead of vlookup, and try this: =HLOOKUP(B2,$D$1:$L$11,2,FALSE) You will want to throw '$' in front of the letter and number of the selection so it doesn't change as you copy/paste it into the other cells in column a. Hope that's what you're looking for.

    Re: Hiding rows after the row number referenced in a specific cell


    It did until I tried to lock the sheet. It stopped working as soon as I did. Very new to VB, so not sure how to fix the issue. Worksheet is for someone else, and I don't want them to inadvertantly screw it up. Will look on forums as well, pretty sure this would be posted somewhere.

    Re: Retrieve data in a cell from value in another cell


    After that, if you wanted to sort the list, you could either manually sort it by selecting each column and sorting z-a (that way spaces are at the bottom), or you could even set something up in vba to do it for you with one click of a button. Can go that way if you want, just don't know how complex you want it.

    Re: Retrieve data in a cell from value in another cell


    First, if you want, go into options, Advanced, display options for this worksheet, and deselect "show a zero in cells that have a zero value". That way you don't have to have "" to show blank cells, you can leave them as zeroes, which in another sheet could cause problems. Not a must for this specific sheet, but good in general imo. Then, under e5 of your shopping list, enter: =IF('Inventory (3)'!E4="x",'Inventory (3)'!D4,0) Copy this to the remaining cells. That way, it will recognize the "x" and post the information in the slot before it, which I think is what you want, correct? Hope this helps.

    I have a workbook that has lists in two seperate sections of columns (a-q and s-aa) and that can be sorted via different macros to list different amounts in each column. I want to hide all results that are sorted that end up at the bottom of the list after the sort. When it sorts, all applicable info is listed at the top set of rows, there are 0's in between (hidden to look blank), and then whatever didn't hit the top of the list is sorted to the bottom.


    I have a cell that lists which is the last row that contains usable info based on the sort at AI4.


    How do I set up a macro to hide all rows after whatever row number shows up at the AI4?


    At the end of the day I want to add this to each of the macros that are used to sort the lists, so it automatically only shows relevant data.


    Thank you for any help.