Fill In The 3 Criteria Fields, Calculate The Result, Copy The Result, And Place It Into The Correct Place On The Table
I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.
| ID X
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
| ID Y
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria:
>= First Day of the Month
<= Last Day of the Month
= ID #
The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Macro: Place Result Value Instead Of Formula
I am testing the code below... Sub applyCombination() Dim shtP As Worksheet Dim j, k As Integer 'j = row; k = col On Error GoTo errorhandler Set shtP = Sheets("Sys-Year")........ k = k + 1 Next k j = j + 2 Next j Exit Sub errorhandler: Exit Sub End Sub and, as it is a bit slow to paste a formula in each cell, I was wandering: Is there any way I can place the resulting value ONLY? (I don't want to do copy and paste special just values as it takes even more time)
View Replies!
View Related
Macro To Copy And Place Data To Specific Place
I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.
View Replies!
View Related
Copy Formula Result & Paste Value/Result Only
I created a simple auto numbering function whereby Cell A7 contained =Row()-6, and Cell A8 contained =(A7+1). I then shift, and pasted the contents of cell A8 until cell A600. My aim is to simply copy the increments of 1 - 600 into another column. However when i copy and paste i'm also copying the initial underlying formula ie: =( A?+1), Is there a way to copy the results, not the formula?
View Replies!
View Related
Place / Pull Html Table Into Worksheet
I need to copy/cut and paste an Html table from a website into and Excel sheet without the data being transposed. See link for the table I need to get into Excel: http://www.voedingswaardetabel.nl/voedingswaarde/?vw=vv Then cut and paste it to Excel, you see the data is now vertical. I need the table horizontal as it appears on the website.
View Replies!
View Related
Formula To Lookup Data In A Vertical Format And Place In A Table
I have a RAW DATA work sheet that has data of electricity consumption for a given week but it is in a vrtical table. I have many other work work sheets in the workbook that I require to look at the RAW data and the return the correct information in the specified cells I need the store number that is in cell F1 of each sheet and the Date on each sheet that are on Row4 of each sheet to Look up and match the information in ROW1 for the store number and columnA for the dates. then in columnB of RAW DATA I have time intervals of 30mins which need to match up with the time intervals on the sheets and display the readings from the RAW data on the sheets. ******** ******************** src="http://www.interq.or.jp/sun/puremis/...<CENTER><TABLE cellSpacing=0 cellPadding=0 align=center>Microsoft Excel - Energy Analysis WE15-03-09.xls___Running: 11.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA2A3A4A5A6A7A8=ABCDEFGHIJKLMNO1Reading DateReading Time8912116617118519682296710191119125612571292209/03/200900:0012.5926.74929.69668.728.6487.526.5616.2312.6416.3818.08317.02719.569309/03/200900:3011.8467.211.49610.1245.8726.821.817.9811.3216.711.96214.65619.243409/03/200901:0010.7368.11211.19811.286.27.415.2330.3412.0416.269.5527.26429.02509/03/200901:3010.78767.612810.68510.40725.6966.814.888.936.8416.618.53448.72645.4432609/03/200902:0011.0727.235213.01310.3235.9288.814.757.875.9218.059.38247.09445.3136709/03/200902:3011.2996.819210.26210.1765.70410.414.758.135.0916.489.0566.88325.1984809/03/200903:0011.8116.18248.952411.3695.88.314.697.774.9916.87.20964.71046.2496RAW DATA [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Rows Copy Over To A Different Sheet And Place The Next Available Row
I have is 3 seperate jobs listed, what i want to do is have each one of these rows copy over to a different sheet, and place the next available row. What I would like to drive this is the number in column "n", for example, "n3" is 1, so I would like that row to copy to the sheet named "1", and entered in the next available row under any previous lines that have been copied over. ******** ******************** ************************************************************************>Microsoft Excel - BETA SCHEDULE.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEFGHIJKLMNOP1Date EnteredTime Ent'dCUSTOMERJOB #LINE #MATERIALMATERIALMAT'L QTYPAPERPAPER QTYFinish QNTYSIZEMach.RUN DATEDue Date233/318:58 AMCUST. A2585271HSS5260 15 SH 15 / 1530X65 .02013/313/3143/318:58 AMCUST. B2585241PC 8256 7 FT 3 / 624X26 .01023/313/3153/318:58 AMCUST. C2585161TFS1010 3178 FT 615 / 61532X61.5 .00833/314/1MASTER [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Result Based On Existance: If Coulmn Contains A Anywhere Then The Result Should Be A
I have 4 categories A, B, C & D. These are in desending importance, means A is most important and D is least important. Now there could be many A, B, C & Ds listed in a column. The challange is if coulmn contains A anywhere then the result should be A. If A is absent, then search for B, if present anywhere then display the result as B. It doesn't matter how many times A or any character is listed in column. I am attaching a sheet for better explanation.
View Replies!
View Related
If Cell Not Blank, Calculate & Round Off Result
I would like to be able to round off the decimals resulted in the POV field on my worksheet and have a running total of the entries listed below. This occurs sometimes when the mileage units have decimals. In this example, the POV values display $1.00, 99 cents, and 93 cents. Their resulting total should be 2.92, but Excel calculates them as $2.93 due to the additional decimals. The problem is that if I apply an =ROUND function to the formula in the POV field, while it will display a correct answer if all the date and mileage fields are filled in for which the formula has been applied, it will not give a running total in instances where there are empty date/mileage fields, reporting "VALUE!". Is there a way to force Excel to display the running total? I have attached the worksheet, with the example above entered in. I have included the formula I've been trying to fix on my worksheet on the bottom as well.
View Replies!
View Related
Creating A Formula To Calculate A Result That Depends On 3/4 Criterias Been Met
Firstly its probably best you download the file from below and open it up. http://www.mediafire.com/?un3smhmdyzt I need a formula to put in cells in column S on sheet March. It needs to match up the correct price from sheet 'Container Price List'. The price that should be displayed depends firstly on the account number, container and waste stream all matching to decipher which line the price is on. Then the price displayed should be the price in the column called 'exchange charge' however if this is 0 it should be the price in the column called 'rental charge'. So for example, if you look at row 27 in sheet 'March' the price displayed would be £80.39. The formula will look at the account number 'c028' on sheet 'March' and match it to the any of 'c028' on sheet 'Container Price List'. It will then look at the container in sheet 'March' which is RL14 and match it with the ones that are RL14 in sheet 'Container Price List'. Then it will look at the Waste Stream in sheet 'March' which is GEN.C and match it with the GEN.C in sheet 'Container Price List'. The result given should be the exchange charge in sheet 'Container Price List' which in this case is £80.39. If for example the exchange charge displayed £0.00 then the result given should be the rental charge in sheet 'Container Price List' instead.
View Replies!
View Related
Formula To Calculate Result Whilst Avoiding Errors Is Too Long!
I have a formula I've been using for a long time which uses VLOOKUP to find results based on various reference cells, and then adds them up. To avoid errors caused by VLOOKUP not finding anything for one of the references I have also used ISERROR. The formula returns a blank if the calculation returns a zero. I now need to bring further references in to the calculation but, using the format I have been, the formula is now too long. Here is my extensive formula:
View Replies!
View Related
Return Result Based On Criteria
if Cell F42 contains 50%, then my fomula returns "wrong" =IF(AND(0%<F42,F42<50%),"F",IF(AND(50%<F42,F42<60%),"D",IF(AND(60%<F42,F42<70%),"C",IF(AND(70%<F42,F42<80%),"B",IF(AND(80%<F42,F42<100%),"A","wrong"))))) how do I get the formula calculate correctly?
View Replies!
View Related
Result Based On Two Criteria Lookup
See the attached file. I think index and match may help me having done a search several times in these forums, but having been trying to do this for several hours I am just struggling to understand each part of the formula unfortunately. I have a list of data (cellsA1 to C33) which I need to use as my source for the information that is displayed in cell I2 based on the two selections made in cells F2 and G2. I can't use filter and data sort in this situation - I am creating a spreadsheet for someone who truly knows nothing about excel, so I need this to be as simple as pointing and clicking at cells F2 and G2 and the formula doing all other work. Is it possible to do what I am asking for (hopefully the fact that peoples' names are repeated and months are repeated won't cause a problem?
View Replies!
View Related
Return Result Based On Multiple Criteria
I have been trying this for hours but to no avail. I have a table with 4 columns headers Name, Amount, Loc and Code The name may look like ABC 1, ABC 2.... The Loc may be in US, GB... and the Code may be AA, BB I need to return a result "Y" if the sum of the amount is > 100 and "N" if the sum of the amount < 100 based on the conditions of the following :if 1) Name is the same entity, such as ABC 1 and ABC 2 and 2) Loc is the same, US..and 3) Code is the same I have attached a sample to illustrates the result
View Replies!
View Related
Color Formula Result Based On Criteria
I would like to use formulas to display different results eg UNDERBUDGET - OK - OVERLIMIT etc. Ideally they would be shown using different colours & text size. I can't seem to change the colour/text size inside the formula for the different words - only for the whole cell.
View Replies!
View Related
The Result Of A Formula To Move To The Left And Top Of The Table
This formula tells me the highest number: =LARGE((G36,O36,W36,AE36),1) The result is AE36. The answer is correct. I get the highest number. I want the formula now shows Y3 instead of AE36. In my situation Y3 is the name of 1 of my tables. In my case I get from this table, the highest number. These are 6 cells to the left side. These are 33 cells to the upper part. In the future I will work with more tables. So the answer must always move I've been busy this few days to investigate. That is why I know it works with offset. Anyway, I still do not know how it goes.
View Replies!
View Related
Populate Cells/Table Depending On INDEX Result
I am trying to populate a table (a7:c27) in the attached sheet with data. First, I must enter a number 1-16, which uses index function to return either "series" or "base" If it is base, i want the number in the corresponding table filtered down - i.e. if 1 originally picked then h7 is (40) is entered into each month as base = constant. If the result is series, then the data in i7:z7 is entered into the the 18 month table.
View Replies!
View Related
Create SQL Query In VBA And Display Result As Table In Worksheet
I need the VBA script in excel that will do a SQL Query, for this case I need to select a value where there are 2-3 tags and between certain period then display the result as a table in a worksheet. Example I have a database with 3 types of tags "Tag A", "Tag B", and "Tag C", and each tag have a value with different timestamp. the database looks like this: No. Timestamp Tags Value 1 1-Jan-2010 Tag A 18 2 1-Jan-2010 Tag C 20 3 2-Jan-2010 Tag A 20 4 3-Jan-2010 Tag B 17 5 3-Jan-2010 Tag C 19 6 4-Jan-2010 Tag B 18 7 4-Jan-2010 Tag A 20 8 5-Jan-2010 Tag A 22 9 5-Jan-2010 Tag B 18 10 5-JAn-2010 Tag C 20
View Replies!
View Related
Automatically Run Macro On Formula Result Obtained From Pivot Table
I currently have an excel file set up which is refreshing imported data every minute from a server. This data is located on " sheet 1". This data is the result of another system constantly monitoring (pulling data from) the process at my factory. It consists of temperatures, speeds and other settings. When a temperature, speed or setting changes then it will be captured by the monitoring system and therefore my excel sheet will load it into the imported data within the next minute. I would like to be able to capture the change in temperature, for example, if it goes into alarm and automatically send an email reporting this alarm. This alarm is captured in my imported data in a specific column with a 1. If it is out of alarm it is 0. There will not be a report of an item, at any one time, with both a 0 or 1... so there is only one case of any one monitored process. I have a pivot table set up to filter all the uneeded data out and I currently refresh it manually to show the current status of all alarms. If an alarm occurs on the process, I can refresh my pivot table and it will be indicated with a 1 until it is refreshed again (and the alarm is gone). So the question: 1. How can I refresh the pivot table automatically every x seconds? 2. How can I automatically send an email on the value change from 0 to 1 in case of an alarm?
View Replies!
View Related
Compare Dates In Table & Adjust Adjacent Value Based On Result
I have built a macro that tests to see if a date in a range is before the Period Start Date, and if it is, then the labor rate in that same row (3 Columns Over) should be escalated by the appropriate AWI. My code below works, but I am wondering if there is a better way than using offset(0,3) to prevent a code breakdown if columns are inserted between the Date and Rate columns. The date range is names "DATES" and the rate column in names "RATES" Sub CountTheCells() Dim cell As Excel.Range Dim i As Integer For Each cell In Sheet2.Range("DATES") If cell.Value < Sheet2.Range("POPS") Then i = i + 1 End If Next MsgBox ("You have " & i & " Rates that will be escalated b4 the POP Begins") yesno = MsgBox("Would you like to Escalate the Dates and Rates?", vbYesNo) If yesno = vbNo Then MsgBox ("Not Done") Else..........................
View Replies!
View Related
Lookup Multiple Criteria & Sum Result On Multiple Criteria
I am trying to solve a problem. I am currently using this formula = SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300) This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long. So the info looks like this in excel MARKETING 200612 -10 MARKETING 200701 -25 MARKETING 200708 -50 ECONOMICS 200709 -30 The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
View Replies!
View Related
Copy Formula Result As A True Value
Is there a way to copy a formula result as the actual value and not the formula, I can do copy and paste using "value" option but would like an automated formula or code option if possible. In the example, D4 copies C4, would like D4 to show "AB" and not the formula "=C4"
View Replies!
View Related
Copy Rows From Search Result
i have been using a code that "Joe Was" wrote on this forum the code search all cells for a word i type and then copy the rows where the word was found into a new sheet. my problem is that the code overwrites the rows, so only one line is shown even when the result box say it finds 10 results. Public Sub FindText() Dim ws As Worksheet, Found As Range Dim myText As String, FirstAddress As String Dim AddressStr As String, foundNum As Integer myText = InputBox("Enter text to find") If myText = "" Then Exit Sub Worksheets("Søkeside").Cells.ClearContents For Each ws In ThisWorkbook.Worksheets...... Do foundNum = foundNum + 1 AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf Set Found = .UsedRange.FindNext(Found) Found.EntireRow.Copy _ Destination:=Worksheets("Søkeside").Range("A2").End(xlUp).Offset(1, 0) Sheets("Søkeside")....... If Len(AddressStr) Then MsgBox "Found: """ & myText & """ " & foundNum & " times." & vbCr & _ AddressStr, vbOKOnly, myText & " found in these cells" Else: MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation End If End Sub
View Replies!
View Related
Simple Macro - Copy Result Of Formula
I am trying to implement a simple macro. Here is my Function Age(DOB) Age = Int((Now - DOB) / 365) End Function Sub Macro1() Age (Selection) ActiveCell.Offset(0, 1).Select ****** End Sub The function basically calculates the persons age using their date of birth. What I need to know is how to copy the result of the function into the cell next to the selected one. (The code above only selects the adjacent cell).
View Replies!
View Related
Search Range And Copy Result Rows To New Sheet
I have a table in excel with a group of headings. (Serial Number, Model, Description, Repsonsible and a few others) What I would like to do is be able to search every cell within the range of that table. When the user clicks search an inputbox is displayed and you can enter any search term you like. if there is a match within the cell range, i want the entire row (and the heading row always at the top) printed into a new sheet to display results. There may be a match in multiple rows, and id like evrery row displayed.
View Replies!
View Related
Copy Values To Another Worksheet For Calculation & Return Result
I have wind speeds and elevation data for 200 points. Each variable needs to go into a calculations spreadsheet on another worksheet. Once each variable gets put in the calculations automatically spit out the result. Now I need to use macro to automate this process so that it will repeat the steps I took for every line of data. In English code terms, I want the Macro to say, take wind speed number and input in wind speed cell in calculations spreadsheet. Then take elevation number and input in calculations spreadsheet. Then take result number from calculations spreadsheet and input in cell. Then loop to go down the 200 lines of data. Is this possoble? Sorry for the non-technical wording.
View Replies!
View Related
Where To Place The Macros
I have created a excel spread sheet to be set up like a schedule, including multiple macros through the sheet, when I started it put the macros into "Module 1", now if I wanted to use this same worksheet and copy it and create multiple sheets in one file via the tabs does it make a difference where the macros reside? if it remains in "Module 1" it will only be in one spot will this create problems later on or should the each reside in the sheet objects.
View Replies!
View Related
Allows Me To Place Checkboxes In Each Row
What I have is a long list of about 100 items. I would like to set up an excel sheet that allows me to place checkboxes in each row and then whatever checkboxes have been checked, to simply collate and print out ONLY the check boxes that have been checked. I have gotten as far as to create and rename the checkboxes but my skill ends there. I also need to know how to get each checkbox to correspond to a row. I appreciate ANY help that you can give me. EDIT: this doesnt NEED to be checkboxes, it can be optionbuttons if that is easier... simply something to select/deselect and print. 1.1 a blah 1.1 b blah 2.1 a blah 2.1b blah 3.1a blah 3.1b blah 3.3.a blah etc blah etc
View Replies!
View Related
Using DMIN In Place Of Arrays
I currenlty have a spreadsheet that works well but uses a lot of array formulas and, therefore, is running VERY slowly. The slow speed makes it impractical to work with, so I am trying to replace the array approach with the DMIN function. However, I am having trouble getting the DMIN function to find the minimum value in a column range according to a criteria in the same arrays did for rows. Here is my problem: I currently have arrays (composed of dates) in rows and I have converted this to non-array columns. I have pasted a sample of the new worksheet below. Column D ["Original End"] has dates that are static. Column F ["New Start"] has dates that can change according to user inputs. I want Column G to find the date in a Column D that is greater than the date in Column F in the same row. In the table below, you can see that I was able to get this to work for cell G3; however, when I pull the formula down, it stops working. Here is the formula I have in cell G3: =DMIN($B$2:$E$15,"End",$I$2:J3). Conceptually, I want to write the following function in cell G4: =DMIN($B$2:$E$15,"End",">"&F4), where the criteria is 'greater than date in F4' - but this obviously doesn't work.
View Replies!
View Related
Place The Name Before Equal Sign
I'm trying to simplify a section of my code by naming a long string that is repeated. I tried by simply place the Name before equal sign (=) and also putting Set before the name. Both failed though I've seen both methods used successfully in other code. Am I having a syntax issue of some sort? Private Sub ConditionalRowDelete() Dim NumRows, iLine As Integer ActiveSheet.UsedRange.Select NumRows = ActiveSheet.UsedRange.Rows.Count Dim CircuitType As Range Set CircuitType = Range("C" & iLine).Value For iLine = NumRows To 2 Step -1 If Range("A" & iLine).Value > "6999" Then Rows(iLine).EntireRow.Delete End If Next iLine For iLine = NumRows To 2 Step -1 If CircuitType = "VOIP" Or CircuitType = "Customer Care" Or CircuitType = "Dialup" Or CircuitType = "IRU" Then Rows(iLine).EntireRow.Delete End If Next iLine End Sub
View Replies!
View Related
Place File Name Into Private Sub
The code below writes the names of all active worksheets into the A column of the active worksheet. I have a situation where I have 2 workbooks open (3 if you count PERSONAL.XLS). One of the worksheets has a consistent name, the other has a name that changes every week (not in a consistent pattern). I'd like to be able to use the routine below to get a list of open workbooks, ignore the one I'm working in and PERSONAL.XLS, and instead put the remaining filename into other Private Subs in a User Form to run other routines.
View Replies!
View Related
How To See Where The 55th Place In A Cell Is
I use Excel 2002 - I have a column with Text in its cells. The cells usually range from 30 to 130 characters including spaces. I need to use word wrap. I would like to be able to see where the 55th character is ( Including spaces ) - but I also need to see the rest of the contents of the text in the cell. I have thought about coloring the first 55 character/spaces ? or inserting a character at space 56 ? Example below XXXX XXX XXX XX XXXXX XXX XX XXXX XXXX XXXX XXX XXX XXX XX~XXX X XXXX XX
View Replies!
View Related
Formula To Place Name Next To Appropriate Date
Spreadsheet 1: So in column "A" I have all of the dates written out. Then i need column "B" to put the appropriate name next to the date that person chose in spreadsheet 1. Spreadsheet 2: I have a live form that feeds data into a different spreadsheet. I am using google spreadsheets.... The formulas are the same as in excel
View Replies!
View Related
To Place A First And Last Name Into An Email Convention
I have a spreadsheet where I have the first and last name in two separate columns. In the same spreadsheet, I have a column where there is an Email Convention. The email convention can change from row to row. Is there a way to take the first and last name columns and make it format according to the email convention column. For are some samples. First Name Last Name Email Convention Ada Smith @mail.uch.org Ann Blanc _@chs.net Bob Jones .@halifax.org
View Replies!
View Related
Check If AdvancedFilter In Place
I'm in the process of debugging some code at the moment and so have removed the On Error Resume Next's that I had in there. I've since gotten rid of this where it wasn't necessary, but I have a general question about using the ShowAllData method with Advanced filter. When I use: ActiveSheet.ShowAllData when there is no advanced filter applied, I get an error saying the method of Worksheet class failed. I have searched and cannot find a way to check in VBA if there are advanced filters applied, though I can find code that will check if AutoFilters are applied. This doesn't work with AdvancedFilters.
View Replies!
View Related
Save The Tamplate In Original Place
I have VBA program that collect the data from database and make a report.(I will mention I open the browser to look for database ) Here is a problem: My original file is " Daily Report.xlt(template) " .At the begin when I open this file ,the file name appear as a " Daily Report1 "...This is not big deal When I run macros I need to keep some info on the original file. After I done, I have following code to save as " Daily Report.xlt " . but the actual problem is ;the code save this file on the database path that I don't want it.Actually I want to overwrite on original file to keep that file up to date.Here is code ActiveWorkbook.SaveAs Filename:= _ "Daily Report.XLT", _ FileFormat:=xlTemplate, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Note:I know I can add the path in front of the file name and save it in the that directory .Different customer different path I can not use fix directory.
View Replies!
View Related
Ranking Multiple Columns And Second Place
******** ******************** ************************************************************************>Microsoft Excel - Win_calc.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutQ21= QRSTUVWX21TickerLowestLowest*NameBOALEHBEARSecond*priceSecond*name22AT*US95/BEAR10210095**23ARW*US50/LEH605070**24BSX*US65/LEH666567**25CBS*US53/BEAR/MS555453**HVOL8* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR. This is my general template. I get prices from BOA, LEH AND BEAR on the four companies listed. Then I use the Min function to tell me the best price (I'm buying). Then I use some IF statements to tell me who's the "Lowest Name" (lowest price): =IF($G2=$C2,"/"&$G$1,"")&IF($H2=$C2,"/"&$H$1,"")&IF($I2=$C2,"/"&$I$1,"")&IF($J2=$C2,"/"&$J$1,"")&IF($K2=$C2,"/"&$K$1,"")&IF($L2=C2,"/"&L$1,"")&IF($M2=$C2,"/"&$M$1,"") It checks if a cell is = the lowest cell value, then returns the header. This formula refers to different columns than my displayed sheet because Im hiding so cells and I just copied this to another section of my sheet. There's probably a better way to do this but this is what I have do far. I use this for 50 company names and 5 or six dealer banks (BOA, LEH, etc) I'd like column that gives me the Second Place price and one that gives me the associated dealer (BOA, LEH).
View Replies!
View Related
|
TRACKER
