Posts by freddie0

Quote from GlennUK

I did a full list of all jobs, and the relevant half per job, and then altered the half_indicator to fetch the correct half:

Sample 4.xlsx

Glenn, this worked a treat. Many thanks

Thanks, but I don't think that this would capture those highlighted as in sample 3 which also need to be included with the second half options?

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.
forum.ozgrid.com/index.php?attachment/46682/

Thanks

Re: Vlookup max number

Brilliant!!!Many thanks fior your help

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

Nope, should I be?

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

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:

Sub insert_blanks()


    Dim j As Long, r As Range
    
    j = InputBox("type the number of rows to be insered")
    Set r = Range("A1")


    Rows("1:1").Select
    Selection.Copy
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp


    Do
        Range(r.Offset(1, 0), r.Offset(j, 0)).EntireRow.Insert
        Set r = Cells(r.row + j + 1, 1)
        If r.Offset(1, 0) = "" Then
            Exit Do
        End If
    Loop
    
End Sub

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

Think we've managed it with:

Windows(1).ActivatePrevious

Thanks

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

Sub FillColBlanks()
'http://www.contextures.com/xlDataEntry02.html
'by Dave Peterson  2004-01-06
'fill blank cells in column with value above


'2010-10-12 incorporated Ron de Bruin's test for special cells limit
'http://www.rondebruin.nl/specialcells.htm


Dim wks As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim LastRow As Long
Dim col As Long
Dim lRows As Long
Dim lLimit As Long


Dim lCount As Long
On Error Resume Next
 
lRows = 2 'starting row
lLimit = 8000


Set wks = ActiveSheet
With wks
   col = ActiveCell.Column


   Set rng = .UsedRange  'try to reset the lastcell
   LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
   Set rng = Nothing
   
    lCount = .Columns(col).SpecialCells(xlCellTypeBlanks).Areas(1).Cells.Count
    
    If lCount = 0 Then
        MsgBox "No blanks found in selected column"
        Exit Sub
    ElseIf lCount = .Columns(col).Cells.Count Then
        MsgBox "Over the Special Cells Limit" 'this line can be deleted
        Do While lRows < LastRow
            Set rng = .Range(.Cells(lRows, col), .Cells(lRows + lLimit, col)) _
                           .Cells.SpecialCells(xlCellTypeBlanks)
            rng.FormulaR1C1 = "=R[-1]C"
            lRows = lRows + lLimit
        Loop
    Else
        Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
                       .Cells.SpecialCells(xlCellTypeBlanks)
        rng.FormulaR1C1 = "=R[-1]C"
    End If


   'replace formulas with values
   With .Cells(1, col).EntireColumn
       .Value = .Value
   End With


End With


End Sub