Posts by rem1224

    Colin,


    I don't remember if you had other workbooks open or not.... the way the code you posted is written,


    ActiveWorkbook.Save
    ActiveWorkbook.Close


    will close INTAKEstock.xls - -


    then the next line
    ActiveWorkbook.Close SaveChanges:=True
    will close the current workbook (the one that contains the Workbook_Open macro - -
    so it never gets to the Excel.Application.Quit line.


    Change that line to ActiveWorkbook.Save if you have to save that workbook too, then try shutting down the app.


    BTW, the ScreenUpdating=True line is not needed - since it is after the Application.Quit, it will never be executed either.


    .....Ralph

    Hi sdj,


    I had a bit of time to play around with the code I spoke about. Result is attached.


    Run the macro called "Summarize". I only coded the macro to process January (because it was the first sheet) and July (because it was the only sheet with data). You'll have to define the Dynamic Named Ranges (under Insert / Name / Define) for the other months and add the code to the macro where indicated.


    Wasn't sure which field you want to sort and which to total, so I took a guess. You can mod as needed.


    Each time you run the macro it clears the old data from the Summary worksheet - don't change / delete rows 1 or 2 - I put the Criteria for the Advance Filter way out in IV1:IV2.


    Post back with any questions or if you're looking for a different approach.


    .....Ralph

    Hi Doug,


    If there are a limited number of ways the users would want to split or freeze the frames, you could set up some Custom Views in those configurations. Those seem to work in the browser for turning freeze panes on and off.


    .....Ralph

    Hi Colin,


    I assume this is a followup to last weeks thread http://www.ozgrid.com/forum/viewthread.php?tid=6802


    I tried similar code running from the Task Scheduler and everything shut down as it was supposed to. I did notice 1 thing - in the Task Scheduler window, the "Status" column would show "running" for several seconds after the Excel Window closed down. Sometimes I even had to click on View / Refresh before the "running" would change back to a blank. Could that be the problem - or is Excel really still open in its own window?


    .....Ralph

    Hi sdj,


    (I only saw the O/S column in July Sheet - assume all sheets will be the same eventually)


    My initial thoughts would be to set up Dynamic Named Ranges for each month worksheet. Example the range for July would be - -


    =OFFSET(JUL!$A$1,0,0,COUNTA(JUL!$A:$A),28)


    This would expand the range as users add rows whenever there is anything in Col A - - you'd just have to keep stuff out of Col A in your formula section below the dotted line.


    Then you could use the Advanced Filter function - set up a criteria in 2 cells somewhere in the workbook - - O/S is cell 1 and "Y" in cell 2. When you run advanced filter from the menus you can copy the results to another location - but it must be on the same sheet as the source data - if you use the same code in a macro, you can modify it to write to a different sheet. Do 1 advanced filter line in the macro for each month.


    This may get you started - - I'll have a try at some code later when I get some free time.


    Hope this helps


    .....Ralph

    Hi Thomas - Welcome back.


    I modified a few entries in your file as a partial solution - Formulas in H22:H25 and I22:25 for the second section. Also showed a way to convert the OT time in the third section to a decimal number so you can use it in the Col M overtime calc (I just put it in the Client Order # column temporarily as an example - you could build that conversion right into the OT calc formula to avoid using a new column if you want.


    I didn't understand what you were going for with the last question about if D over 8hours....look at P .... divide by 480 etc. Maybe you could explain it more or post a sample of what the results should look like.


    The "Scroll Box" on top of the page is really an ActiveX Combo Box. You use it to select a value from a dropdown list of values and put the selection in a cell. Is that what you were looking to do?


    .....Ralph

    Excel has you covered - -
    You can refer to the worksheets by their index number instead of the name - that way if the name changes, the macro will still work.


    Code
    Sub Macro1()
      Dim fmat As String
      fmat = Worksheets(1).Range("A1").Value
      Worksheets(2).Range("B:B").NumberFormat = Chr(34) & fmat & Chr(34) & "@"
    End Sub


    Worksheets(1) refers to the leftmost worksheet tab, Worksheet(2) the 2nd from the left, etc... As long as you don't rearrange the order of the worksheets the macro will give the same results regardless of how many times you change the name on the tab.


    .....Ralph

    Hi c_butta,


    You can mod the code to add references to the particular worksheet where the value comes from and the worksheet and range you want to format - - something like - -


    Code
    Sub Macro1()
      Dim fmat As String
      fmat = Worksheets("Sheet1").Range("A1").Value
      Worksheets("Sheet2").Range("B:B").NumberFormat = Chr(34) & fmat & Chr(34) & "@"
    End Sub


    You can use as many "NumberFormat" lines as you need with different worksheets/ranges to accomplish what you want.


    .....Ralph

    Hi Julian,

    I did not completely review you code yet, but as to the last line in your post - about one sub seeing another subs variables....


    The variables that you want to share between subs must be declared "Public" at the beginning of the module in the general section before the actual code of the subs starts - - if not declared Public, variables are only visible within the sub that creates them.


    Look up the Public statement in the VBA help files for more info.


    Hope this helps


    .....Ralph

    Try this in your code:



    Code
    Sub Macro1()
      Dim fmat As String
      fmat = Range("A1").Value
      Range("B:B").NumberFormat = Chr(34) & fmat & Chr(34) & "@"
    End Sub


    If A1 contains the string Q1/03- then that format will be applied to column B with whatever text is already in B coming after the -. If you have numeric data in Col B you might have to change it to "text" format first or change the @ symbol in the macro to a #.


    The CHR(34)'s are quote marks - needed because you have a zero as part of the format string - without the quotes, the zero in the "03" part of your string becomes a format place holder and any digit you type in the cell is placed in that position of the format string.


    (Thanks Neale, for reminding me about the @ in the formatting...don't know where my mind was this morning :wink1: )


    ......Ralph

    Hi,


    You can't hide the VBA Editor, but you can password protect the project which if effect hides you code and prevents modification.


    In the VBA Editor, in the project pane, right click on any of the sheets or workbook in your project, select VBAProject Properties.... then on the Protection Tab, check "Lock project for viewing" and add a password.
    Save the Excel File.


    Hope this helps


    .....Ralph

    Hi Colin,


    If you have more than one instance of Excel running, the code should only close all the workbooks running in the set where the macro is located. (Worked that way on mine).


    If you are running the Excel file from the Task Scheduler, make sure you are opening up a new version of Excel for the task and not just opening the excel file in the current Excel task (if excel is already running). In your "Run" box in the task scheduler, enter the full program name plus the excel file name - -


    "C:/Program Files/Microsoft Office/Office10/EXCEL.EXE" c:/test.xls


    (adjust for where your excel program file are, of course)


    .....Ralph

    Hi Steve,


    A lookup table would work for your application.


    Hope this helps


    .....Ralph


    <SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"&gt;</SCRIPT&gt;<CENTER&gt;<TABLE cellSpacing=0 cellPadding=0 align=center&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=6&gt;<TABLE width="100%" align=center border=0&gt;<TBODY&gt;<TR&gt;<TD align=left&gt;<FONT color=white&gt;Microsoft Excel - Book1</FONT&gt;</TD&gt;<TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right&gt;___Running: xl2002 XP : OS = Windows XP </FONT&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=6&gt;<TABLE width="100%" align=center border=0 VALIGN="MIDDLE"&gt;<TBODY&gt;<TR&gt;<TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"&gt;(<U&gt;F</U&gt;)ile (<U&gt;E</U&gt;)dit (<U&gt;V</U&gt;)iew (<U&gt;I</U&gt;)nsert (<U&gt;O</U&gt;)ptions (<U&gt;T</U&gt;)ools (<U&gt;D</U&gt;)ata (<U&gt;W</U&gt;)indow (<U&gt;H</U&gt;)elp <A onclick=show_popup(); href="#javascript:void(0)"&gt;(<U&gt;A</U&gt;)bout</A&gt;</TD&gt;<TD vAlign=center align=right&gt;<FORM name=formCb755237&gt;<INPUT onclick='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);' type=button value="Copy Formula" name=btCb873980&gt;</FORM&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=6&gt;<TABLE border=0&gt;<TBODY&gt;<TR&gt;<FORM name=formFb078704&gt;<TD style="WIDTH: 60px" align=middle bgColor=white&gt;<SELECT onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name=sltNb935705&gt;<OPTION value==VLOOKUP(A1,$D$1:$E$4,2,FALSE) selected&gt;B1</OPTION&gt;</SELECT&gt;</TD&gt;<TD align=right width="3%" bgColor=#d4d0c8&gt;<B>=</B&gt;</TD&gt;<TD align=left bgColor=white&gt;<INPUT size=80 value==VLOOKUP(A1,$D$1:$E$4,2,FALSE) name=txbFb426622&gt;</TD&gt;</FORM&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<BR&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;A</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;B</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;C</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;D</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;E</CENTER&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;1</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;budgie</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;<A onclick="document.formFb078704.sltNb935705.options[0].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);"&gt;cage</FONT&gt;</A&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left"&gt;Value</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left"&gt;Result</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;2</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;cat</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;basket</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;3</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;dog</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;kennel</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;4</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;budgie</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;cage</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;5</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;6</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=6&gt;<TABLE width="100%" align=left VALIGN="TOP"&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left&gt;<U&gt;Sheet1</U&gt;</TD&gt;<TD&gt;&nbsp;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;<BR&gt;<FONT color=#339966 size=1&gt;[HtmlMaker 2.41] </FONT&gt;<FONT color=#339966 size=1&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT color=red size=1&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT&gt;</CENTER&gt;

    Hi,
    If you were typing numbers in the cell, the custom format would work - -


    Format / Cells / Number / Custom , in the 'Type' box, enter "Q1/03-"# (be sure to enter the quote marks)


    If you then type a 4 into the cell, you would get Q1/03-4 in the cell. However, custom number formats have no effect if you are entering text (like the A in your example)


    For that you could use the concantenate function - but you'd need 2 cells - enter "A" in A1 and A2 would be =CONCATENATE("Q1/03-",A1) - - which probably doesn't help much.


    .....Ralph

    Hi dk,


    I think Neale is right - a bit of testing is required, there is no easy formula to enter to test that many dependent conditions.


    have a look at the attachment and see if it gets you started.


    I used the MATCH function to test for C1, D1, and E1 in the 3 ranges and assigned values (1 for Q range, 10 for R range and 100 for S range) if a match was found. Next created a lookup table with the sum of the 1's, 10's and 100's as the lookup value and the associated values in T1, U1, and V1 in the table. Used the Vlookup function to populate the 3 cells.


    A bit cumbersome, I admit, but it does give the result.


    Hope this helps


    .....Ralph