DropDown from vlookup

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Delete rows that the first cell's text is "A" I have a large spreadsheet (41,000+ rows) that I would like an easy way to delete rows that contain specific Network Letters that are in Column A. Here is the first row of a very large POP dial up list. I'm tired of deleting rows for networks I don't have access to. Is there easy way to setup a macro/script to delete rows based on the text contained in column A? Greg :-) Network Letter, Area Code, Number, City, State, Country, Tier, ISDN Offered,V92 Offered A 205 6050101 Alabaster AL USA 1 N Y ... E 317 7070800 Danville IN USA 1 N N ... ... G 765 6798010 Glenwood IN USA 1 Y N I 52 9999423904 Merida N/A MEX 3 N N Tag: DropDown from vlookup Tag: 78566
  - 2
    - deleting sheets without excel asking I'm using this code to delete a sheet: Sheets("START").Select ActiveWindow.SelectedSheets.Delete When I do this excel says there may be data on the sheet do you really want to delete it? And then you have to click delete. Is there a way to have it delete the sheet without having the user click delete? Thanks, Phil Tag: DropDown from vlookup Tag: 78563
  - 3
    - not show value Hi, I am creating a simple sheet that has repeated formulas in the columns. Maybe like this: Cell C1 =A1+B1 where B cells are a user input. Cell A2 will equal Value in C1 and so on. A B C 1 1 2 2 1 3 3 3 3 3 3 3 3 Is there a way to create incremental values in cells where no value will show until the B cell has had a value placed in it? I am trying to avoid a situation where the last value is repeated through out the columns as in 3 in cells C3:C5. Thanks, Louis Tag: DropDown from vlookup Tag: 78561
  - 4
    - Making Tables in Excel This is a VERY elementary question. I'm trying to make a table in Excel. I actually imported data from SPSS. So basically, MSN assistance tells you to select the data, go to INSERT and choose TABLES. There is no option like that. I clicked on DATA and then TABLE and its asking me for a ROW INPUT CELL and I can't seem to figure out what they're asking me for because everything I put in, they tell me the format is invalid. HELPPPPPP!!!! -- Sheena Tag: DropDown from vlookup Tag: 78560
  - 5
    - Sumif + Large = headache turkey leftovers for first elegant solution .. col A col B ------------------- RR 450 SS 350 TT 250 TT 300 RR 450 RR 400 SS 450 TT 500 what i need is formula out in column C that will pick off the largest x values in colunn B based on criteria A. e.g. sumif (large("TT"s, 2) = 800 having trouble isolating the 'TTs' in separate array to put in Large function - want to keep entire table in this order, as formula will be copied down and range will be based on dates in another column TIA Tag: DropDown from vlookup Tag: 78554
  - 6
    - How to give a value to NA in Excel Hi, I'm trying to come up with a way in Excel to QA customer service phone calls. I've created a simple spreadsheet to add up the scores for sections of the sheet and have added a formula to convert them to a percentage. For instance, the first section has three questions worth 2 points each for a possible total of 6. The formula to convert it to a percentage is =SUM(C8:C10)/6 This formula works fine when adding values of 0 and 2, but depending on the type of call, not all questions are applicable, so I'd like to add a value of NA. However Excel sees NA as a 0, thus skewing the percentages. I'd like to give NA a value of 2. Sure, I can just enter the full value, but would prefer to see NA in the cell. Is there any way that I can do this? Any help or suggestions would be most appreciated. Mike Tag: DropDown from vlookup Tag: 78549
  - 7
    - Dependent cells I have a financial model with some 15-20 worksheets. I dont need a lot of these work sheets and would ideally like to delelte them but before that i want to ensure that no cell references to other worksheets i.e. cells in other worksheets are not dependent on the to-be-deleted sheet. I have tried CtrlG>Alt S>dependent cells Formula Auditing tool but these are too tedious when i have too many cells filled in every worksheet. Any other way to ensure that the sheet has no dependents? Tks/Amag Ps: tried http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/9255c6015d88f636/83b64de66473889a?q=dependent+cell&rnum=30#83b64de66473889a cudn't figure out? Tag: DropDown from vlookup Tag: 78548
  - 8
    - Lookup Function In Column A, I have the following listed down the column: Initial Independent 2 3 4 5 etc etc.... Now in Column B next to these, I have a corresponding monetary value. Now, My lookup function works for the numbers but it doesn't work for the words 'initial' or 'independent'. Any ideas why? The numbers bring back the corresponding value perfectly but if I choose 'initial' it bring back a wrong value. Here's my function: =N5*24*LOOKUP(C2,A2:A13,B2:B13) Tag: DropDown from vlookup Tag: 78541
  - 9
    - Date last modified cell Hi guys, I am wondering if there is a function or vba script in excel that can show the date my .xls fle was last modified? So i need it to only change when any cells are changed with in the workbook but if the workbook is opened and just viewed at, the date will not change. Thanks for the help. Tag: DropDown from vlookup Tag: 78529
  - 10
    - Moving data to another worksheet My company receives a spreadsheet from another company with information reported in an unusual format, something like this: Heading Heading Heading Heading Heading Data Data Data Data Data Heading Heading Heading Heading Heading Data Data Data Data Data Heading Heading Heading Heading Heading Data Data Data Data Data I don't know why they format the information in this manner, and I can't get them to change their report. I am stuck with this. This would be one set of information, and there is a variable # of these information sets each week contained in the report. I would like to pull this information out append it to another spreadsheet across one row at the bottom of the current dataset. Anybody have any ideas? Thanks Lance Tag: DropDown from vlookup Tag: 78528
  - 11
    - Can You Help Me Please.... I have been given the job of booking vouchers in at work.....they come in pads of 20 and i have to list 200 books manually once a month. The vouchers are all serial numbered and have to be booked in by serial number and as I issue them ,they are booked out (you still with me?) The voucher numbers will be something like 123501 - 123520, then the next pad is 123521 to 123540 etc etc. When I get them in I get an invoice which shows me the start and end numbers, i'd love to try and set something up where i could enter the start and end serial number and excel would create a sheet with all these numbers enterred for me. My worksheet displays the date i receive these vouchers in, the start number and then the end number i.e. ........................... Date Start Serial No End Serial No 25 Nov 05 123501 123520 " " " 123521 123540 Can any kind person suggest a way of helping me achieve this, my experience of Excel is a bit limited but i'll have a crack at anything. Many Thanks Dave Tag: DropDown from vlookup Tag: 78516
  - 12
    - Help with a formula Hello, I need some help with a formula. It is a nested formula with vlookup and if. I am trying to estimate the insurance cost depending on the region, if the driver is an excellent driver (- $250) or poor driver ( + $ 450) and if the driver lives in the city ( + $350) or rural ( - $ 400). I have 5 regions into a different spread sheet, and also the information about excellent or poor, are into a different spreadsheet. I got it on the paper but when is to put it in excel I have problems. Insurance = region ($) + - Excellent, poor driver +- City residence, rural residence. Thanks. -- leu ------------------------------------------------------------------------ leu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29103 View this thread: http://www.excelforum.com/showthread.php?threadid=488231 Tag: DropDown from vlookup Tag: 78505
  - 13
    - Need help...getting #DIV/0! My formula is =if(sum(p6/o6)*100<=0,"",sum(p6/o6)*100) The formula works fine if there are numbers in p6 and o6 cells. If I leave it blank I get #VALUE! and if i put a zero I get #DIV/0!. Is there a solution? Thanks for any help in advance. Tag: DropDown from vlookup Tag: 78503
  - 14
    - Using auto filter when worksheet is protected Hello I'm just adding the finishing touches to a spreadsheet and a large area of one of the worksheets has been protected. However the spreadsheet is designed with the functionality of autofilter in mind. When I protect the sheet it no longer gives the ability to auto filter. Is this just the way it is or does anyone have any suggestions Thanks in advance Regards Tim Tag: DropDown from vlookup Tag: 78497
  - 15
    - Sticking cells BlankHiya, Im not sure how 2 explain this, but I would like 2 get Row 1 and 2 on my spreadsheet to 'stick', so when I scroll down my sheet the top 2 rows are always at the top. How can I do this? Thanks in advance. -- @---}-- Laura..... :) Liverpool, England "Do you know where you're going to?" Tag: DropDown from vlookup Tag: 78490
  - 16
    - Files won't open when dbl click on filename We have a small 3 station network - all running XP Pro SP2 and Exce 2000 SP3. On only one of the stations when you double click on the .xl filename in My Computer, excel starts up but the file doesn't load. just get a blank excel screen. File associations are all set correctl and I have double checked network settings, folder is shared etc etc I'm stumped. 2 of 3 stations work perfectly - excel opens and file load. Please help!! Thanks : -- Global MH ----------------------------------------------------------------------- Global MHP's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2909 View this thread: http://www.excelforum.com/showthread.php?threadid=48818 Tag: DropDown from vlookup Tag: 78489
  - 17
    - Utility for Recover password Hello to all, you could recommend me a good software to recover or to remove password of a file Excel 2000. Thank you ahead of time Mario Tag: DropDown from vlookup Tag: 78488
  - 18
    - GAS meter spreadsheet GAS meter spreadsheet I would be grateful if you could help me with this problem, perhaps by suggesting suitable freeware, or maybe by pointing me to sources of help. SITUATION: I take gas meter readings at random intervals; sometimes seven days might elapse between readings, sometimes seventeen. It could be any number of days between readings. REQUIREMENT: I want to know how much gas has been used in the year up to any day. The day might not be one on which I took a meter reading. Also, the day 365 days before that might also be one for which the meter was not read. I would assume linear interpolation between two adjacent readings. QUESTIONS: What would be the best way of doing this? Spreadsheet? Database? Python or similar? Anything else? I should point out that I am not experienced with any of the above, which means that I would have to learn from scratch whatever method is suggested! You might even know of the existence of something that will do it already? -- Chris Tag: DropDown from vlookup Tag: 78485
  - 19
    - Show only required data and transfer to MS Word I have a spreadsheet that shows some materials we use in our company The first column shows a description of the material, the second colum shows the catalogue number of the material and the third column is lef open for the user to enter in the required quantity. I have attached to the spreadsheet a command button but I need som help with the VB coding. What I'd like to do is have the user enter th required quantities in the 3rd column and then click on this comman button which would then only show the rows that have had quantitie entered. If I could get this done it would be sufficient. However if it is possible I would then like to have these rows tranfe into an order form which i already have developed in MS Word. Any help with this would be greatly appreciated -- Neil_Pattiso ----------------------------------------------------------------------- Neil_Pattison's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2769 View this thread: http://www.excelforum.com/showthread.php?threadid=48812 Tag: DropDown from vlookup Tag: 78476
  - 20
    - Open a specific worksheet Hi, Just reading some of the other posts I am drawn to ask a follow up question. I have a workbook with sheets named for each month of the year. Is it possible to have the opening sheet open on the month of the year.. so jan is the opening sheet during January and February the opening sheet in february etc etc. Regards Don Tag: DropDown from vlookup Tag: 78471
  - 21
    - Using VBA to time protect ranges Hi hope you can help. Is it possible to use VBA to protect cells based on the current date. I am wanting to stop information from being changed that would belong to a past event ie I worked on Monday when I didn't Regards Don Tag: DropDown from vlookup Tag: 78470
  - 22
    - ANOTHER dropdown question! BlankHello again, Im sorry for asking lots of questions, but I am attempting my first spreadsheet and I am stuck :( I have now been able to do a drop down feature (thanks to Bob and Roger). I would now like to know:- In my list of names in the drop down feature, if I selected a certain name, is there any way I do enter a formula or something so that whole row turns a different colour. Have I explained myself okay?? Thanks for your help anyway :) -- @---}-- Laura..... :) Liverpool, England "Do you know where you're going to?" Tag: DropDown from vlookup Tag: 78468
  - 23
    - Drop down. *EASY* question! BlankHiya everyone :) I was just wondering if anybody could explain something reeeeally easy for me? In Excel, I would like a two drop-down features in my columns of my spreadsheet. The first one (columnE) with the options 'yes' or 'no'. The second one (columnD) with a list of names, eg. Anthony Peter Mark Jonathan Thankyou for ANY help you can give me! -- @---}-- Laura..... :) Liverpool, England "Do you know where you're going to?" Tag: DropDown from vlookup Tag: 78464
  - 24
    - Conditional Sum testing cell format. I suspect this problem may be solved more simply with a pivot table, but my users are having problems creating the pivot tables after they build the data base. Event data is recorded as pairs in a row where the first cell of the pair contains the date and the second cell of the pair contains the data. For example, the sheet where column A, row 9 indicates the hotel (Hilton), column B will contain the formula, and columns C through L will contain the data: Hilton|formula| 1/11/1996 | $56 | 5/8/2004 | $89 |etc? to column ?L? I am looking for a formula that I can insert in column B on the same row as the data stream which will ignore the dates and give me the sum of all of the expenditures listed in that row (record). My approach has been to use the SUMIF function in conjunction with the ?cell? function. The ?cell? function will return a code based on the format of a cell. The formula shown below will yield a series of true and false answers when entered into cells in the range D20:M20 (or any other row) based on whether or not the corresponding cell in row 9 is set to display data in the General format. =("G"=CELL("format",D9:M9)) The SUMIF formula returns the WRONG answer when I then used this SUMIF formula entered as a regular or array formula {=SUMIF(C13:L13,C13:L13="TRUE",C9:L9)} The formula returns the sums of the date code numbers. The SUMIF yields the right answerif I enter as an array formula: {=SUMIF(C13:L13,C13:L13<>"TRUE",C9:L9)} The fact that the <> works implies there is some unseen column offset issue. When I try to go to the next step and build the cell format test into eith normal or array configuration of the SUMIF formula, =SUMIF(C9:L9, ("G"<>CELL("format",C9:L9)) ,C9:L9) {=SUMIF(C9:L9, ("G"<>CELL("format",C9:L9)) ,C9:L9)} I get zero as the sum whether I enter it as a regular formula or as an array formula. I also have tried using the SUM(IF ?. ) in an array formula as. {=SUM(IF(("G"=CELL("format",C9:L9)),C9:L9))} {=SUM(IF(AND("G"=CELL("format",C9:L9)),C9:L9))} {=SUM(IF(("G"<>CELL("format",C9:L9)),C9:L9))} All yield zero sum. SUGGESTIONS? Tag: DropDown from vlookup Tag: 78461
  - 25
    - Chart with three SeriesCollections Greetings! At me such question: I have constructed a chart with thre SeriesCollections, but it shows strange - that is values1 = values values1 and values2 = values + values1 + values2. Categories are the same. How to show it in a normal kind -- docd ----------------------------------------------------------------------- docdz's Profile: http://www.hightechtalks.com/m35 View this thread: http://www.hightechtalks.com/t229428 Tag: DropDown from vlookup Tag: 78459
- Next
  - 1
    - PMT I know how to use Excel to get the PMT., i.e. a loan for $10.000, 5 years, 7% interest rate. How do I use a regular hand held calculator to get the PMT or do I need a special calculator? Douglas Tag: DropDown from vlookup Tag: 78454
  - 2
    - Transposing data from columns to rows > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3215780710_1600263 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit Hi, I have cut and pasted a table from an online database into a spreadsheet. It has pasted into one column. How can I format the data so that each entry appears in one row across various columns. Currently I am transposing each entry individually by paste special. Help! I have 300 to do! --B_3215780710_1600263 Content-type: text/html; charset="US-ASCII" Content-transfer-encoding: quoted-printable <HTML> <HEAD> <TITLE>Transposing data from columns to rows</TITLE> </HEAD> <BODY> <FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYLE=3D'font-size:12.0px'>Hi, <= BR> <BR> I have cut and pasted a table from an online database into a spreadsheet.<B= R> <BR> It has pasted into one column.<BR> <BR> How can I format the data so that each entry appears in one row across vari= ous columns.<BR> <BR> Currently I am transposing each entry individually by paste special.<BR> <BR> Help! I have 300 to do!<BR> <BR> </SPAN></FONT> </BODY> </HTML> --B_3215780710_1600263-- Tag: DropDown from vlookup Tag: 78452
  - 3
    - Old problem resurfaces in new guise I once posted the topic "When is an Empty Cell not Blank?" and received an answer from Dave Peterson as to the reason why an empty cell may not necessarily contain nothing. He wrote as follows. *If you had formulas that evaluated to "" and then converted to values, you can see a single apostrophe in the formula bar of one of those cells if you toggle this setting: Tools|Options|Transition tab|check the 'transition navigation keys' box (uncheck after you're done checking.) * In order to 'blank' the 'empty' cells (and eliminate the single apostrophe) I used the technique (not recommended if you have formulas) selection.value = selection.value (as there is only string data in this column). This works for empty cells. It does not work if you have string data in the cells. How then can I 'clean' the single apostrophe from the data without damaging the information in these cells? This single apostrophe is preventing my replacing blanks between characters in these cells with nothing (" " with ""). We've just received a file of information from a client and my program is rejecting the ABN information because I can't make the string of numbers into a numeric field. It isn't a drama at the moment but it could be in the future. Thanking all respondents now to my 'Friday 5pm fault' and have a good weekend. I'm out the door now. Tag: DropDown from vlookup Tag: 78451
  - 4
    - Make Excel Database from HTML Hello. Im new here and I kinda know some about Excel but I have a problem. I need to make an Excel Database from an HTML file and I dont know how to get about doing it. The website is simple w/ one graphic at the top and just links going down... I dont know if Im posting in the right place but... Thanks, Nick -- NTL1991 ------------------------------------------------------------------------ NTL1991's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29081 View this thread: http://www.excelforum.com/showthread.php?threadid=488078 Tag: DropDown from vlookup Tag: 78450
  - 5
    - Record duplication > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3215759230_303770 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: 8bit Hiya I¹ve got two lists with 10k records in each and I want to see which records appear in both lists. Is there a way of doing this in excel? Tab --B_3215759230_303770 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable <HTML> <HEAD> <TITLE>Record duplication</TITLE> </HEAD> <BODY> <FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYLE=3D'font-size:12.0px'>Hiya<= BR> I’ve got two lists with 10k records in each and I want to see which r= ecords appear in both lists.<BR> Is there a way of doing this in excel?<BR> Tab</SPAN></FONT> </BODY> </HTML> --B_3215759230_303770-- Tag: DropDown from vlookup Tag: 78442
  - 6
    - Keyboard shortcut When I use Ctrl C to copy or Ctrl V to paste, it pastes in a screen shot to my workbook. This occurs in both Word and Excel. I have checked the cusomised keyboard shortcut set up options and both shortcuts have been set to just copy and paste. -- mryan ------------------------------------------------------------------------ mryan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29076 View this thread: http://www.excelforum.com/showthread.php?threadid=488038 Tag: DropDown from vlookup Tag: 78440
  - 7
    - What Function to use Hi I am quite a novice with MSExcel but have managed to achieve some success with formulas and formatting even between worksheets. However I am struggling with the following problem and would appreciate some advice. I have two columns, each of thirty cells, Column J will contain either the letter C or the letter P and column K will contain an amount. I want cell D37 to contain the total of only the cells in column K where the corresponding cells in column J equals "P" and likewise I want cell D38 to contain the total of only the cells in column K where the corresponding cells in column J equals "C". Hope I have explained this ok. Many thanks, Rob Tag: DropDown from vlookup Tag: 78428
  - 8
    - Rounding I have my workout in excel. So I have my max in one cell. 315. For my workout, each set has a percent of that max for the amount of reps I do. I have it setup so my max is in cell A6. And where one of my reps are theres a formula that looks like "=a6*.52" that gives the 52% of 315. That gives me 163.8. I want it to round down to 160. How do I set that up automatically into the formula or the cell. So when I put that "=a6*.52" into the cell it automatically rounds up or down depending on the number that it produces. thanks. -- darkpiece ------------------------------------------------------------------------ darkpiece's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29073 View this thread: http://www.excelforum.com/showthread.php?threadid=488025 Tag: DropDown from vlookup Tag: 78426
  - 9
    - Clear a range of cells I have recorded a macro to select and clear a group of cells yet whenever i ran it, the sheet starts going nuts. but it completes the task. is there a way to do it with a code: example i want to clear C3 and A14:A37 and d14:d37 and e14:e37 and f14:f37 then i want it to jump to C41 and A52:A75 and d52:d75 and e52:e75 and f52:f75 then i want it to jump to C79 and A90:A113 and d90:d113 and e90:e113 and f90:f113 and there are 27 more sets of cells that i want to clear.... can anyone just show me how to create the first 3 codes then i's enter the rest... thank you... -- Joe@excel ------------------------------------------------------------------------ Joe@excel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29016 View this thread: http://www.excelforum.com/showthread.php?threadid=488021 Tag: DropDown from vlookup Tag: 78422
  - 10
    - Need template (possibly Excel) for estimating a software project Hello, I need a template / tool (possibly excel) to estimate a software project. Any one know of anything? Cheers Paul M Tag: DropDown from vlookup Tag: 78415
  - 11
    - Start with a specific worksheet when a Workbook is opened i have a work book Call *i* and when open i want it to always start at *Data* sheet i found th following code but i dont know what else i need to modify. Private Sub Workbook_Open() On Error Resume Next Application.Goto Reference:=Worksheets("Data").Range("A1"), _ scroll:=True If err = 9 Then 'actually is subscript out of range MsgBox "Requested worksheet ""Data"" was not found " _ & "by Workbook_Open in ThisWorkbook." End If Worksheets("Menu Sheet").Activate Sheets("Data").Move Before:=Sheets(1) 'insure is 1st sheet On Error GoTo 0 'only needed if you have more code End Sub thank you in advanc -- Joe@exce ----------------------------------------------------------------------- Joe@excel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2901 View this thread: http://www.excelforum.com/showthread.php?threadid=48801 Tag: DropDown from vlookup Tag: 78414
  - 12
    - sheet naming I have a macro that copies a sheet then names it. It goes as follows: Sub Createsheet() Sheets("Sheet1").Select Sheets("Sheet1").Copy Before:=Sheets(1) Sheets("Sheet1 (2)").Select Sheets("Sheet1 (2)").Name = "datasheet1" Range("C4").Select End Sub Is there a way to make it so that when you run the macro again and create a new sheet and it sees that datasheet1 already exists it will name the next sheet datasheet2? And then when you run it again it sees that datasheet1 and datasheet2 exists and it names the next one datasheet3? Thanks, Phil Tag: DropDown from vlookup Tag: 78410
  - 13
    - Hide my code Is there a way to hide my macros and vb code so that no one can view it or edit it? Now, even with a protected sheet, I can just right click the sheet name and choose view code and see the code. I'd like to be able to disable this feature, if possible. Thanks, Phil Tag: DropDown from vlookup Tag: 78409
  - 14
    - Macro Coding Gee, I find myself here seaking help again and just when I thought I wa getting a hand on how this MACRO stuff and the coding worked. I have found an other coding challenge that I can't firgure out on m own. Can one of you smart MACRO Coding experts please help. I hav played with this for over a week and just can't firgure it out on m own. I am trying to develop is a MACRO that will seach an entire colum an everytime it finds a number greater than "0" it will move that numbe to the left one colum. Here is a sample of the type Data elemets from a spreadsheet that currently working: Z00056 1 13 0 0.7 0 Z00384 4 4 0 0 0 Z32566 3 261 0 15 0 Z39441 1 67 0 2 0 Z46135 1 340 0 33 0 Z53098 1 46 0 3.12 0 Z72045 1 55 0 4.5 0 Z72475 1 5 0 0.11 0 C18234 2 0 6240 0 150 D12087 0 0 0 0 0 F40375 0 0 0 0 0 T60081 0 0 0 0 0 T61494 0 0 0 0 0 T61908 1 0 10000 0 537 W98825 0 0 0 0 0 X40009 0 0 0 0 0 Z36683 1 0 10000 0 0 Thanks in advance for any help provided and have a great Turkey Day, Ro -- Duster14 ----------------------------------------------------------------------- Duster142's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2773 View this thread: http://www.excelforum.com/showthread.php?threadid=48799 Tag: DropDown from vlookup Tag: 78408
  - 15
    - Paste Special not working I have a file with data for a particular week with formulas in the cell which are using this data to produce totals, but when i copy these cell with the formulas and then "paste special" into a new worksheet for th following week (which has its own data which i want to total up usin the same formulas from previous week) i am not getting correct totals. Sometimes when use the paste i dont even get the correct "past special" dialogue box, instead getting prompted to choose a type o paste- excel 8, text or others from list. Others times when the correc "paste special" box appears, and i select "all" and then paste im onl getting the formulas being used in the cell not actually any data Other times if i paste i get some data but its not updated with th values from the new worksheet im working in (not giving correct ne total). Ive tried on a couple of pcs and im pretty sure its not profile relate - Is there anything in excel that would be causing the prob or is i just bad data somewhere????????? -- didemp ----------------------------------------------------------------------- didemps's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2906 View this thread: http://www.excelforum.com/showthread.php?threadid=48790 Tag: DropDown from vlookup Tag: 78386
  - 16
    - Addning info from diffrent workbooks using VBA I'm working on my master thesis and I could really use some help. I have two workbooks and I want to add information from workbook2 to workbook1 by matching the order numbers in the different lists. Workbook1 includes the following columns Ordernr |Date| etc Workbook2 includes the following columns Ordernr| Customer | Quality | etc I want to take the first cell in the ordernr-col from workbook1 and search for that specific order number in the ordernr-col in woorkbook2. When I find that number I want to take the information (Customer, Quality) from that row in workbook2 and add this information to workbook1. If the order number is not found in workbook2 it can be indicated in workbook1 as blank. I want to loop through all of order numbers in workbook1. Perhaps this is a matter for access but I think It could be written in excel with VBA. I'm not that good with VBA so I tried to write how I think it could be done. Maybe someone can help me with translation or point me in the right direction. Do While IsEmpty(ActiveCell.Offset(1, 0)) = False 1. Read value (order number) from cell workbook1.A"currentRow" 2. Search workbook2, column A for order number and return row-number to "foundrow" 3. Read value (Customer) from cell workbook2, B."foundrow" and write value (customer) to workbook1, C."currentRow" 4. If order number not found, then write blank or "not found" to workbook1, C."currentRow" Loop Tag: DropDown from vlookup Tag: 78383
  - 17
    - why if function does not work? I would like to have 25% in U column when N column is 1:2:3 I used if function and it looks like this: =IF(N6="1:2:3",U6=25%,U6="") But it does not work. Suggestions? Tag: DropDown from vlookup Tag: 78376
  - 18
    - Print Quantity from a cell I have created an Invoice form in a tab sheet call Invoices In excel. in this tab sheet "INVOICES" i have 30 bookmarks to separate pe pages example D1 D2 D3 D4 D5 then there's a bookmark and continue to 2nd invoice D6 D7 D8 D9 D10 then there's a bookmark and continue to 3rd invoice and so on untill theres 30 invoices in the sheet. now when there's dat entered in let say D1 then E1 shows 1 as a result using th If(D1="","",1) and same for D6 if there's data entered in it. the E6 shows 1 as a result usin If(D6="","",1). this allows me to find out how many invoices i have t print. by adding all the E column. on F1 using formula =sum(E1:E300). not all 30 invoices have data. but if i just press print then all o them print. there for i will have to enter Crtl+P to get to print menu and the select From and To to get the number shown in F1. is there a way to create a *macro* or a *code* that will print an given number in F1 as the total invoices to print? once again thank you very very much... -- Joe@exce ----------------------------------------------------------------------- Joe@excel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2901 View this thread: http://www.excelforum.com/showthread.php?threadid=48787 Tag: DropDown from vlookup Tag: 78372
  - 19
    - Turning Off Error Reporting using API Functions and VB We want to automatically turn off the error reporting options using Windows API functions for the following functions area Click on Start, Settings, Control Panel, System, the Advanced tab and Error Reporting (at the bottom of the screen), which launches the Error Reporting screen. Another way to get to that screen: Open My Computer, right-click on Properties, click on Advanced tab and Error Reporting. Any assistance would be welcome. Tag: DropDown from vlookup Tag: 78367
  - 20
    - Rename Sheet Event When we rename a sheet Excel crashes. What event is triggered when a sheet is renamed. Thanks in advance Tag: DropDown from vlookup Tag: 78366
  - 21
    - VB code help needed I'm not a programmer, but with the help of this group I can usually figure it out, but this time I stuck. Could someone please help me with writing the vb code for the following situation? I have two excel files: excelfile1.xls and excelfile2.xls In excelfile1.xls I have a button that when clicked opens a form that has a field to enter a name. You enter the name you want and click an ok button. It then creates a new sheet in both excel files with the name you entered. (example: you enter test1 as your name and a new sheet is created in both excel files called test1). Works fine. I then have another button in excelfile1.xls that gets created on the test1 sheet that when clicked opens a form with 3 fields. Game1, Game2, Game3. I enter values in these text boxes and click ok. When I do this I need the values to be copied to excelfile2.xls into cell c4,c5,c6 on the sheet test1. The catch is I could create test1 sheet today, test2 sheet tomorrow, test3 sheet the next day, etc. When I enter the values in the form I need them to be copied to the same sheet name in excelfile2.xls. (example: test1 and test2 sheet gets created in excelfile1.xls and excelfile2.xls. I am on sheet test1 in excelfile1.xls, I click the button and enter values in the form. I need the values to get entered in cell c4,c5,c6 on sheet test1 in excelfile2.xls. Then I'm on sheet test2 in excelfile1.xls, enter my values in the form, I need them to get entered into c4,c5,c6 on sheet test2 in excelfile2.xls) So basicly I need the values from the form in excelfile1.xls to get entered onto the sheet with the same name in excelfile2.xls. I hope this isn't too confusing. If you need more info from me let me know. Thanks for any coding help you can supply. Phil Tag: DropDown from vlookup Tag: 78365
  - 22
    - Combining Different Spreadsheets Hi, I'm trying to combine several reports into one. They all have lists o names with statistics associated with them. The problem is that the don't all have the same names. Here's an example of what I mean: List 1 - study 1 John 1.2 Mary 4.0 Ed 1.5 Tom 5.5 List 2 - study 2 Edna 4.5 John 2.5 Tom 5.5 Alicia 2.2 Goal......Study 1.................Study2 Alicia .................................2.2 John .....1.2 ......................2.5 Edna ................................4.5 Ed.........1.5 mary......4.0 Tom ......5.5......................5.5 So these list 1 and 2 both have some of the same names but also som different names. How do I combine them to make the goal list with th different results side by side? It seems like there should be some wa to sort them against a master list of names so that there are blan spaces for for people not on the individual lists -- mlev ----------------------------------------------------------------------- mlevy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2905 View this thread: http://www.excelforum.com/showthread.php?threadid=48783 Tag: DropDown from vlookup Tag: 78361
  - 23
    - Copying and pasting mulitple formulas, can it be done? Hello Can you copy and paste more than one formula at a time? In this instance Iâ??m referring to a work sheet has 3 IF statements; one for Monday through till Friday is the same, while both Saturday and Sunday have different IF statements respectively. Is there away to accomplish the copying and pasting of all three IF statements simultaneously? Kind regards Tim Tag: DropDown from vlookup Tag: 78352
  - 24
    - A question on templates in excel Can we use a "template" for a sheet and when we change the template, the sheets uses that template updates automatically (like layouts in database applications). I dont think it's possible but better to ask pros here :) Tag: DropDown from vlookup Tag: 78350
  - 25
    - what form control to use? I want to use a form for the user to enter in search parameters - I know how to do this. These parameters are used in a database query. Then I want to display the results of the database query in the form - What control do I use for this and could you give me an example of the vba code I would use to populate it? Thanks Tag: DropDown from vlookup Tag: 78337

Hi

How is this done ? I want to create a drop-down list on a cell, that is
populated from a named range. The user selects from the list to get it's
value for the cell. I've seen this done, but don't know how.

Thanks
Robert

--
RobertLees
------------------------------------------------------------------------
RobertLees's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26580
View this thread: http://www.excelforum.com/showthread.php?threadid=488450

Re: DropDown from vlookup by Bob

Bob
Sun Nov 27 03:40:35 CST 2005

This sounds like a simple Data>Validation, type of list, and set the list to
point at a range, such as =M1:M20

--

HTH

RP
(remove nothere from the email address if mailing direct)

"RobertLees" <RobertLees.1z5eqz_1133082901.383@excelforum-nospam.com> wrote
in message news:RobertLees.1z5eqz_1133082901.383@excelforum-nospam.com...
>
> Hi
>
> How is this done ? I want to create a drop-down list on a cell, that is
> populated from a named range. The user selects from the list to get it's
> value for the cell. I've seen this done, but don't know how.
>
> Thanks
> Robert
>
>
> --
> RobertLees
> ------------------------------------------------------------------------
> RobertLees's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=26580
> View this thread: http://www.excelforum.com/showthread.php?threadid=488450
>

Re: DropDown from vlookup by Norman

Norman
Sun Nov 27 03:42:23 CST 2005

Hi Robert,

See Debrra Dalgliesh's tutorial on Data Validation at:

http://www.contextures.com/xlDataVal01.html

---
Regards,
Norman

"RobertLees" <RobertLees.1z5eqz_1133082901.383@excelforum-nospam.com> wrote
in message news:RobertLees.1z5eqz_1133082901.383@excelforum-nospam.com...
>
> Hi
>
> How is this done ? I want to create a drop-down list on a cell, that is
> populated from a named range. The user selects from the list to get it's
> value for the cell. I've seen this done, but don't know how.
>
> Thanks
> Robert
>
>
> --
> RobertLees
> ------------------------------------------------------------------------
> RobertLees's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=26580
> View this thread: http://www.excelforum.com/showthread.php?threadid=488450
>