Microsoft Excel: I need to sort sales data into categories high >50, Medium >= 35 and Low <30. How would I compose the IF function to do so?
There are two ways to approach this problem: with an IF statement and with a VLOOKUP.IF statement:You'll have to use a nested IF statement. Your range is missing numbers, so I'll assumeHigh: 50+Medium: 30-49Low: <30With this info, the if statement will look like: IF(A1>49,"High",IF(A1>29,"Medium","Low"))It's easy to imagine how this process goes, but let me know if you want me to explain.VLOOKUP:First step is to create a 2x3 table, in descending order, with upper limit values on the left (except for the first record) and categories on the right.This would look like:The next step is the formula which would be:VLOOKUP(D1,$A$1:$B$3,2,1)D1 refers to the cell with the value to be categorized.A1:B3 is the table range thats shown above, and $ signs are to keep the range static, instead of changing when you drag down/copy+paste the formula to other cells. 2 refers to which column number in the table to return.1 is a Boolean operator, which if changed to 0, would only return the exact value (only 0,30,and 50 would not return an error). 1 returns an approximate value, in this case works like 'greater than or equal to' due to the descending order of the table.
How can I set a primary key in Microsoft Excel?
It is a little bit tricky. Basically solution should fit your problem. There is no universal “primary key” declaration or setup. To identify exact col you have notation A B C.. for rows you obviously have numbers 1 2 3. So in general all you need to do is say “=A1″ and you have a unique name for a unique place in sheet. If you would like to identify a range of cells using one “key” you can do that by using Named Ranges or by creating Range type variables in VBA. You can take more detail look on them here:Define and use names in formulasRange.Cells Property (Excel) Does it answers your question? Or maybe you had sth else on mind…
How can I save MS Access form data in a particular table based on a particular value selected in a combo box?
Your form would need to be "unbound" as in not based on EITHER table. You would then need a Command Button to ADD the record to the appropriate table. Right Click the button to view the PROPERTIES and Under EVENTS Your "ON CLICK" would be set to "[Event Procedure]" and You would use the "..." to Build the code. It would refer to the Activity category combo box to decide which table to use, and the code would be something like this: (Since i don't know the actual name of the Combo box I'll call it "Activity Category", You may have to use a Textbox that refers to the combo box to get the currently selected value). IF Me.[Activity category] = "Agro Dealer Association" Then Docmd.RunSQL "INSERT INTO Association Table (FieldName1, FieldName2) VALUES (FromField1, FormField2);" ELSE: Docmd.RunSQL "INSERT INTO Company Table (FieldName1, FieldName2) VALUES (FormField1, FormField2);" ENDIF Using "ELSEIF" will allow You to add more choices, but the last choice will always be "ELSE:" Look up the RUNSQL command, and IF...THEN...ELSE procedures in the ACCESS Help files, or online with Microsoft's free knowledge base.
How do recover permanently deleted records in Microsoft Access?
For MS Access database recovery, you may checkout this manual techniques:1. Restore the database from a backup file – This is why backups are important!2. Delete the .ldb file that is auto-created by the Jet database engine when the database is opened.3. Use the Jet Compact Utility that has been specifically designed to rectify minor corruptions in Access database.4. Remove all compiled VBA code from the database. This can be done using this command line switch from Microsoft:C:\Program Files\Microsoft Office\Office\MsAccess.exe /decompile C:\DatabasePath\FileName.mdbNote: You may need to change the path shown above for MsAccess.exe if you installed it to a different location.5. Use the built-in Access 2010 ‘Compact & Repair‘ feature. Here are the steps to fix Access database corruption using this feature:Open MS Access (not the database file) and go to Database Tools -> Compact & Repair Database.Locate the corrupted database file and click on Ok.The selected database file will be rectified on completion of the process6. Import the corrupt database into a new Access 2010 database file by using the following steps:Open MS Access and create a new database file.Click on Access to Import Access database under the External Data tab.7. In the next window that pops-up, click on Browse and locate the database file while is to be imported.Specify the place and technique to store data in the new database and click on Ok.From the next window select all database objects that you want to import and click on Ok.Hope this answer helpsCourtesy: Courtesy: Manual Techniques To Repair MS Access Database: By EasyTechToolsOr you may also checkout this MS Access Recovery Tool from SysInfoToolsHope This answer HelpsIn case we’re meeting for the first time,Hi! I’m Rahul Roy, ‘a software and data recovery analyst’.Thanks for reading my answer
How do I create multiple cascading Combo Box based on "Find a record on my form based on the value I selected in my combo box" in MS access?
Here’s a paper a colleague wrote which includes a sample database illustrating cascading combo boxes.The example is a list of species, both plants and animals, categorized by their taxonomic rank (kingdom, phylum, class, order, family, genus, then species).The solution involves adding code in the "AfterUpdate" event for one combo box, to update the RowSource property of the next combo box based on the selected value.Microsoft Access Form Cascading Combo BoxesHope this helps.
How do you extract data from the web into Excel?
You can directly import data into excel from a website. Here’s how.Go to Data > Get External Data > From WebA browser window named “New Web Query” will appearIn the address bar, write the web addressThe page will load and will show yellow icons against data / tables.Select the appropriate onePress the Import button.And you will have the information imported into excel worksheet - neatly arranged in columns and rows.This method limits you only to the information provided on that page. Since mobile number and email id are not available here, you cannot acquire them this way.Perhaps you should contact the site administrator or someone from the association and ask for relevant information.
If a cell contains a certain text (Eg: Apple), how can I return the entire row to the same row on another Excel sheet?
If you want to go down the macro route then you'll need some sort of loop with a nested IF inside. Do until activecell.value ="" If lcase(activecell.value) like "*apple*" then X=activecell.row Rows(activecell.row).copy worksheets(2).rows(x) End ifActivecell.offset(1,0).selectLoopI don't know if you've used macros before so I've kept it simple but this tests the cell to see if it contains the word apple using wildcard characters either end. By using lcase it converts the contents to lower case for the purpose of the IF statement which helps deal with data entered in either lower or upper case. The variable x stores the row number it finds apple in and copies over to sheet 2 to the same row number. So now you have a couple of options.
How do I export data from Tally to Excel?
First go to the tally screen you want to export to Excel eg., daybook, ledger, trial balance…etcthen press alt+e to export option then press backspace to select options;Format: you can export tally data to a) ASCII (American Standard Code for Information Interchange, comma delimited) b) Excel (what we use regularly) c)HTML(for web) d) JPEG (Joint Photographic Experts Group, the picture format) e)PDF and f) xml (extensible markup language, for web ); you can select the format as per your choice;select excel and press enterExport Location: is the location or path to where the exported excel file will be store; here you can type or copy paste the path/folder location like my docments, d: etc., generally the default path would be E:\Tally.ERP9.Out put file name: you can type the name of the file and don’t forget to type its extension like daybook.xls or daybook.xlsx or 01022016.xlsx etc as per your choice.Out put sheet name: Basically excel will have 3 default sheets like wise you can choose the name of the sheet for your output file or exported file.Update file existing: select yes if you are exporting the data to the same file 2nd or more time, this option will enable already exported file to update further data.Open exported file: select yes if you want to open the exported file automatically without going to the folder as per the path given above 2.export locationother report options as you are using in the tally…..
How can I pull data from one Excel sheet to another automatically? I want to run a comparison on products I've sold one quarter versus another. Is there a way I can pull in the sales quantity from the other sheet and add it to the row to compare?
There are two ways to do this. Use VLookup or Use Combination of Index-Match.Lets say you have your data in two sheets, X and YPlease note that the same products might not be selling in all the quartersNow, when I want to consolidate in a separate sheet, I would first of all put all product categories in that sheet.Post that, I can use Vlookup to find the value. The only problem is that if the value does not exist, I would get a #N/A error. To avoid that you can use iferror to make the value 0, if it is not found. Please note that the array where you are looking for the key, has to be protected with $sSimilarly I can use a combination of Index and Match, protect the key column with $ and ensure that I can avoid #N/A errors with iferror
I want to transfer my data from sheet 1 to sheet 2 on the basis of one particular value in one column and if I add some data in sheet 1 it automatically updates in sheet 2 (Excel). What should I do?
You can give cell reference in the second sheet.How to give cell reference?Go to second sheet.Select the cell which you want to update by sheet1 value.Now press = in the cell of sheet2. After pressing = you will be able to select the value of origing cell, you want to reflect here.That's it.. You can give reference of cells in same sheet, cell of different sheet or workbook.Now excel will take the updated value whenever you update your base cell.Hope this helps,Regards,