How to update an open Excel in C# using OLEDB?

Excel help

• Hi I need help with a formula that will calculate for every 8 hours=$500 and every hour after 8 = $50.00 per hour so if some one worked 9 hrs it would read 550. Thanks I had it once a few years ago and forgot the formula tough getting old. Thanks Hi I am looking for a formula that will do the following. 8 hours=62.5 + over 8hours =50.00 I am using vlookup function. the proublem is i am using left side vlookup ref codes are repeted. Pls find the attachment My email id [email protected] I am trying to pull data from several different worksheets in one workbook. I'm creating a meal planning spreadsheet that also populates a shopping list. I have one worksheet, "list" that lists different meals that I use to create drop down menus in another worksheet called "Meal Planning". I have another worksheet called "Ingrediants" that list all the ingrediants needed for each meal. What I would like the spreadsheet to be able to do is have the shopping list populate ingredients based on what is selected in meal planning. Specifics for the example.In the Meal Planning sheet I have Tacos listed in row 19 which corresponds with C3:C8 in the Ingredients worksheet. C3 is the heading "Tacos". If Tacos is selected in row 19 of Meal Planning how can I getto list the ingrediants necessary in another location? Once I figure this formula out I will be recreating it for the other items as well. I've spent hours trying to figure out this last piece. Thank you for your help! Following is the reason: Please be more clear and precise. EXACTLY what data is in EXACTLY which cells? EXACTLY what result do you want? Use preciseterminology. Don't include any extra information in your question. Focus on explaining to me a precise, concrete example. Make the example as simple as possible (why do you say the Tacos are listed in row 19? isn't it simpler to say row 1?) Expert: Stuart Resnick Reply-I'm sorry but I'm very confused by response. First you say to be precise and explain what data is in what field but then you ask why I would say tacos is in row 19 instead of row 1. I would answer becase tacos is in row 19 not row 1. Also I'm using the most preciseterminology that I know. I don't know how to explain this in a more precise way without including potentially unnecessary information. Can you please ask specific questions rather than giving general instructions? I'll try again...I basically need to find a formula that populates a list of items (each cell contains 1 item)from 1 worksheet based on a selection in another worksheet. So if Tacos is selected in the meal planning worksheet the formula goes to the ingredients worksheet and creates a list based on what's listed in the ingredients worksheet under the heading of Tacos because that is what selected in Meal planning. I am no expert in excel, thus I am reaching out to you. I am sure I will follow up with more questions. I have 4 Columns (Service Requests,Account #,User Name, Date). I am trying to get the percentage of service requests that have an account number. I also need that broken down by User Name, and then broken down by Month. I can find it long way using "count" functions, but it is taking forever. What functions can I use? i am working through some data analysis and need some help with Excel. Willing to pay, but I want someone who knows what they are doing. Due to your fantastic help recently I feel you're the best man for a quickquery. I have a formula that goes like this =ADDRESS(ROW($GC$7)+MATCH(StartDate,$GC$8:$GC$137,0),COLUMN(GD7))&":"&ADDRESS(ROW($GC$7)+MATCH(EndDate,$GC$8:$GC$137,0),COLUMN(GD7)) Where StartDate and EndDate are defined names referring to cells that the user inputs a start and end date for some charts I hoped to have update on the basis of these dates. Column GC contains dates which could go up to 2-5 years. GD:GD will contain one data series, GE:GE a second data series etc..... The formula above returns something like The above returns what looks like a range in the cell i.e. $GD$8:$GD$32, which looks good so far. I was hoping I might be able to define a name of ChartBehaviour1 with the above formula in the "refers to" field and then refer to that in the series values of the chart, but it doesn't do what I'd hoped. The chart is blank. What obvious tricks am I missing here? Thanks in advance for your advice Peter QUESTION: I have created a spreadsheet that includes multiple columns and rows. I one particular column, I have created a dropdown menu of options to choose from which represent our sales team employees. Nick,Mark,Greg,Robyn,Walt. In another column I have created drop down menu that includes dollar amounts of our products. $4990,$3990,$3795,$3490,2995. Lastly I have created a cell on my worksheet that I would like to calculate the total product revenue by employee. I am struggling with tryng to figure out how to formulate a calculation that is able to look at the name is one column and the revenue in another and relate them to give me a revenue number by employee. Hopefully this makes sense. Any help would be greatly appreciated. Dean ANSWER: Dean, You didn't give specific locations and I am not sure I totally understand your situation and desires, but here is an example I think may provide some insights: If you have employee names in column A and product name in column B and revenue amount in column C. (assume in rows 1 to 5000) then =Sumproduct(--(A1:A5000="Nick"),--(B1:B5000="Product1"),C1:C5000) will give the total revenue for Nick for product1. the name Nick and product name "Product1" can be replaced by cells that contain those values (possibly the cell that has the dropdown) Assume Nick is in M1 and Product1 is in N1 =Sumproduct(--($A$1:$A$5000=$M1),--($B$1:$B$5000=$N1),$C$1:$C$5000) I don't follow the need for this dropdown: >In another column I have created drop down menu that includes dollar amounts of our products. $4990,$3990,$3795,$3490,2995. so I may be missing what you want to do or what your data actually is or what you want to calculate. If so, perhaps you could clarify. ---------- FOLLOW-UP ---------- QUESTION: Sorry for the lack of clarity in my question. In Column D(D1-D175)my spreadsheet allows for me to select a sales person that sells a membership. In Column F (F1-F175) of my spreadsheet I created a dropdown based on the different membership programs that are availible. In cell 012 I am trying to for example add up the total membership sales revenue generated by "Nick". So at the end of the day I ant my calculation to take any membership sold by Nick in any cell range from D1-D175 and to map it against to any dollar figures related to Nick in F1-F175 and to add them up and total them in cell O12. Sorry and hope this clears what I am trying to accomplish up. Dean Austin Hi! good day sir. I am Kaye from the Philippines and would like to seek your help. I am currently working on a database project usingand i would like to consolidate allfiles with company databse and info on it into one mother database. How could i do it without having redundant and duplicate listed companies? Another question is i plan to organize them by industry. I have a list of companies by industry i would like to know how to filter companies so that i could just drag the name of industry to those companies? Thank you and looking forward for your helpful advice and tips. Hello I was wondering if its possible to take data from ansheet and transfer it to adocument (specifically a label template). I am trying to get data from a row in several columns into one part. theformat looks like this: name address phone# john 111 first St I'm trying to get that line of data and put it into one label spot in adocument. any help would be appreciated.

• Answer:

  That's a simple IF function. This tutorial should help explain how to do it: http://599cd.com/tips/excel/overtime/?key=AllExperts Suppose the # of hours in is cell A1: =IF(A1=8,62.5,IF(A1>8,50,"A1 is less than 8")) If the time is an actual time, then multiply A1 by 24 to convert to the number of hours (time is a fraction of a day in Excel): =IF(A1*24=8,62.5,IF(A1*24>8,50,"A1 is less than 8")) In your example, assume range A1:C3 is Code | Item | Qty 202 | reddy | 100 202 | manoj | 200 and range A11:B13 is code | item | Qty 202 | reddy 202 | manoj Note that cell A13 should have the "code" that relates to that row. If you want it to appear blank to the user, then use white font in A13. You don't say what version ofyou're using, so I'll guess it's2007, and answer based on this guess. To calculate the Qty column of the lower table, enter into cell C12: =SUMIFS($C$2:$C$7,$A$2:$A$7,A12,$B$2:$B$7,B12) then copy from C12 down col C as far as your table goes. Open a blankworkbook. On Sheet1, range A1:A3 enter Animal dog cat range B1:B4 Vegetable carrot peas spinach range C1:C5 Mineral rocks gold silver copper Select row 1 of Sheet2 and give it the range name firstRow. Select column A of Sheet2 and give it the range name firstCol. On Sheet1, select cell A1 and then choose Data Validation. On the Data Validation popup, under Settings, Source:, enter: =OFFSET(firstRow,0,0,1,COUNTA(firstRow)) On Sheet2 cell B1, again set up a Data Validation, using as the Source: =OFFSET(firstRow,1,MATCH($A$1,firstRow,0)-1,COUNTA(OFFSET(firstCol,0,MATCH($A$1,firstRow,0)-1))-1,1) Now, the dropdown in cell B1 of Sheet1 will reflect the current choice in cell A1. Review and understand these formulas, and then adapt them to whatever situation you have in which you need one incell dropdown to have options that change based on the selection in a different dropdown. This sounds like it would be a good job for a PivotTable. You can arrange data based on various criteria. If you've never used a PT before, I'd recommend finding a good tutorial first, such as: http://599cd.com/site/courselist/excel/excel223?key=allexperts I'm sure you can also Google for good instructions on using PivotTables. What kind of help, and how do you propose doing this? Telephone? GoToMyPc.com? dimdim.com? Other? My rates are $100/hour. Peter, the chart would be looking for a reference to a range - not a text string such as Address Returns. You can convert that string to a range reference by using indirect =ADDRESS(ROW($GC$7)+MATCH(StartDate,$GC$8:$GC$137,0),COLUMN(GD7))&":"&ADDRESS(ROW($GC$7)+MATCH(EndDate,$GC$8:$GC$137,0),COLUMN(GD7)) becomes =Indirect("Sheet1!"&ADDRESS(ROW($GC$7)+MATCH(StartDate,Sheet1!$GC$8:$GC$137,0),COLUMN(Sheet1!$GD$7))&":"&ADDRESS(sheet1!ROW($GC$7)+MATCH(EndDate,Sheet1!$GC$8:$GC$137,0),COLUMN(sheet1!GD7))) in the refers to portion of the name. That is what I would try. If you have a problem, it might be cause by using named ranges inside a named range. Named ranges sometimes have problems with nested complexity - I have never seen any definitive rules on what they can resolve or not, so if the above doesn't work, then you might try hardcoding those. Also, notice that I have put in representative sheet names in your references and made all references absolute. While there are instances where the refers to can use non-absolute addresses, I don't think this is one of them. Also, I assume your function is returning a valid address such as $GD$8:$GD$32 (I also prepend it to produce: Indirect("Sheet1!$GD$8:$GD$32") I am not sure why you are using Column(GD7). why not just replace that with a number 186 (you may have a reason). Similar for your use of row. For another approach, yo also might build your addresses in a cell and have the named range just refer to that" sheet5!A1 produces the Sheet1!$GD$8:$GD$32 and the refers to in the nameed range is =Indirect(Sheet5!$A$1) as an example. Just some things to try. Dean Austin, as I understand it you want to sum all the dollar amounts in F1-F175 that have the name Nick in the same row in D1:D175 =sumif(D1:D175,"Nick",F1:F175) this will sum up the value in column F for any row that has Nick in column D for the rows 1:175 Again, if I have misunderstood, please post a followup with where I have misunderstood. To find duplicate companies in yourfile, assuming that you have one column of companies name in column A. UseAdvanced Filter and filter for unique records only. http://www.contextures.com/xladvfilter01.html#FilterURhttp://www.contextures.com/xladvfilter01.html#FilterUR If you have a list of companies by industry located in a separate worksheet/workbook, simply use VLOOKUP to lookup the company name in your current worksheet against the list (company - industry) to get the corresponding industry for the company. Should you need any further assistance, don¡¯t hesitate to contact me. Well, this sounds like a mail merge - that would be the usual way to do this - IF it's only ONE record, then copy and paste special, link would be another way (link allows the data to be updated - if no need to update, then just paste. My email if it helps with any follow-ups is [email protected]