Posts by freddie0

    Thank you, this was ideal.......


    Until somebody moved the goalpoasts :-(


    It now seems that some of the original choices that were in the 'first half' also need to be included in the 'second half' list and only have those options available (not first half) - as highlighted on the sheet.


    Not sure if would be simpler/possible just to do a list and have one set at the top for all those of one type, and another for the other type, but then they need to have the drop-down options?

    Sample 3.xlsx

    Sample 2.xlsxThank you for this and I can see how this works. Unfortunately it doesn't do what I was looking for but maybe this was due to my explanation.


    I have attached another sample so hopefully this may show better (along with the first).

    In this sample I have just used a list for the drop-downs in column A to speed things up, but this would normally be an indirect substitute, and there would be a number of columns previous to this also that contains options for the user dependent on the previous option chosen.


    Column B here (Role) is similar and dependent on what is chosen in column A.


    Depending on what is chosen in column B then needs to allow a restricted list only to be available, the information is shown in the List tab. So if one set of options are chosen then it would only allow the drop-down to show the choice of ab, cd, de, or fg. If some of the other options are chosen then it only allows a list of hi, jk, lm, or no to be chosen.


    The list of options in column B is quite extensive due to the previous possibilities (approx 250-300) and only approximately a quarter fall into the latter category.

    The previous columns were only given to show that this was part of a bigger data sheet, each one contains the same formulae e.g. =INDIRECT(SUBSTITUTE(A1," ","_")) which is dependant on the previous column/cell.


    This works fine for a small list of information relative to one option chosen, which in turn has been dependent on the previous options chosen.


    Issue here is this: in the 'Jobs' column (in this example) you will be presented with 22 options in a drop-down, the formula required in column G (levels) needs to provide the options in data from D to G, only relative to what is chosen as the Job i.e. different options would be available for the lower part of the data table.


    I know a solution would be to set these up with individual names ranges and the information below relative to each, however the true data has much longer names and there are also approx 300 names in the list so hoping for a simpler solution.

    I have been working on improving a data sheet that narrows down choice options but hit a snag.


    The columns so far have been drop downs via indirect substitute formulae that have been dependent on the previous column content. The one that is needed now needs to limit the range of responses available in the drop downs to 2 sets of options depending what is chosen in the previous column. There are a significant number of responses that can go into the previous column (see example attached as column F on the Reports tab - Jobs), and depending on these it needs to allow either the top half of the table in the data tab - showing D2:D17 to show in the drop-downs, or the lower half, showing D18:D23. Column C on the Data tab would be the responses that would show on the Report tab column F.


    This is just a sample sheet and there is significantly more data in the actual sheet that needs to be capture, though only 2 range options.Sample.xlsx


    Cross Post: also posted here https://www.mrexcel.com/board/…ute-formula-help.1129611/

    Re: Create drop down then IF statement?


    This seems to cover what I need, thanks


    I assume that - no matter how large the database is - as long as the named range covers all the data it wouldn't be a problem with the size? (number of rows)

    It sounds simple, but maybe it just isn't. I'm trying to create a database of addresses with a lookup front page. If the user enters a postcode in one box it will give them the possible addresses linked to that postcode in a dropdown. On choosing the required address the three required fields will be automatically populated. The same postcodes will not necessarily mean the 3 data fields will be the same etc, etc. The database may become quite large but there will be nothing more difficult than the above.


    I have attached a simple file to try and demonstrate.
    ozgrid.com/forum/core/index.php?attachment/46682/


    Thanks

    Files

    • Sample1.xls

      (18.94 kB, downloaded 25 times, last: )

    Re: Vlookup max number


    Used Ctrl-Shift-Enter and it works for the first year, then starts to repeat the '11 figures if I stretch to '12 year. I have amended the formula to cover the increase in rows to account, but still only repeatsThanks

    Re: Vlookup max number


    Thanks, but this is only giving me numbers in rows 3 and 18 on sheet1 - and these aren't the correct figures. All other cells are showing '0'

    I am hoping someone can suggest a code for the attached. I need something like a Vlookup in sheet1 that will find the maximum number for each month when looking at sheet2. I.e Shhet1 Jan 11 would show Apples 436, Oranges 843 etc, etc.Thanks

    Files

    • test.xls

      (18.43 kB, downloaded 36 times, last: )

    Re: delete, copy, paste within limits


    Quote from Stanley D. Grom;558427

    freddie0, Does the macro you posted fill your needs? If not: Is there always one blank row between the last field in group 'London" to the title 'Leeds'? Is there always one blank row between the last field in group 'Leeds" to the title 'Manchester'? Have a great day,Stan

    Hi StanThe method posted suits fine thanks. It is a set of numbers (results) from a csv paste horizontally with no gaps, just consecutive numbers.The code I used allows the transpose to vertical and gives the option of how many rows to insertThanks

    Re: Copy, transpose, and insert blanks


    After banging my haed on the desk I got nowhere. As is often the case, I went for a drink, came back, and got the following to complete what I needed:




    Issue solved

    I have 2 rows of data with Row 1 containing only the word Monday repeatedly in every column (various lengths). Under each of these is a relevant figure for each Monday.Under this, down the sheet is a list of Monday to Sunday, and I need the horizontal figures to be pasted in the Monday's vertically (having 6 gaps between where there is no data i.e. nothing Tuesday to Sunday.)Probably as simple as anything, but it's one of those 'tip of your tongue' thingsThanks

    Re: vba unable to recognise workbook export


    The buttons are created from the 'Forms' toolbar with a macro on each one. This macro is to run the various scripts required to copy and paste from data files. At present if we try to run them then they copy and paste from the active instruction sheet. It is not the instruction sheet with buttons that we need to copy, we need to switch to the data export sheet to copy the relevant data, but we can't get it to do this

    I have a set of data exports from a reporting suite that I currently run code to copy and paste into other files. These scripts work fine, but obviously these are stored in my personal files so only I can use at present. I am trying to set up an instruction guide for others to be able to run the reports and then the current script by pressing different 'buttons' within the instruction sheet. But no matter what I try I cannot switch the cut and paste to the sheet I need to, it always now cuts cells from the instruction sheet. Could it be because the exported files are not saved in excel, only exported? Or is there some way around this?
    Thanks

    Re: delete, copy, paste within limits


    OK, found one suitable