Posts by paulbiddis

  • VBA to unlock worksheet to allow table row to be added and then protected again

    Hi Roy,

    Thanks. Getting there. The code does what it says on the tin :)

    Just a couple of questions:

    1. Not all columns in the worksheet. Column A is one of these. Hence the original ask.

    2. Is it possible to limit the change event to the last empty cell in column A? (I have found that any further change to already entered entries in column A results in another row being added when it is not necessary.

    Thanks.

    Paul

  • VBA to unlock worksheet to allow table row to be added and then protected again

    Hi Roy,

    Thanks for that. It works... but I didn't explain that I wanted the vba to run when a new invoice number is entered into first empty cell in column A (i.e. the first cell in column A below the table).

    I have put in a change event in the worksheet code that will run your code but there are 2 issues:

    1. The change event looks for any change to any cell in column A. I just want it to run when the first empty celling that column is changed.I cannot find a way to include the vba to find the first empty cell within the change event code.

    2. When I run the change event the code adds approx. 150 rows instead of just one. This does not happen when I run your code separately.

    I have attached the revised worksheet.

    Thanks.

    Paul

    PS. I have removed the password.

  • VBA to unlock worksheet to allow table row to be added and then protected again

    Good evening all,

    Sorry for the lengthy subject but not easy to explain.

    I have a table in an excel worksheet. I want to lock a number of columns in the table (but not all) and then protect the worksheet to ensure that formulas in those columns are not accidentally deleted/changed. Unfortunately, once I protect the worksheet I am no longer able to add rows to the table via entering a new invoice number in the first column.

    In the attached example a new invoice number in column A will, when unprotected/unlocked, will automatically add a new line across the table column range. The locked columns are H and J.

    Any help would be appreciated - there is no password.

    Many thanks.

    Paul

  • 'tricking' excel to recognise a table header outside of the table

    Hi Roy,

    Thank you for your reply.

    I have, perhaps not been clear. Attached is an example.

    I have filtered lists based on the table data. If we take the EOS300 filter list (column H). At the moment the 'include' part of the filer array refers to


    ,ShutterSpeeds[[#Data],[EOS300]]<>"",


    (I have included <> to remove blanks).


    What I am trying to do is replace [EOS300]] with the value in H1.


    I expect it is not possible but it would be helpful if I could.

    Thanks.

    Paul

  • 'tricking' excel to recognise a table header outside of the table

    Good afternoon,

    I have a table and am using the =filter formula to create lists from the table. My question - is it possible to trick the filter syntax to see a #$#$ reference (in another cell) as the header reference?

    I.e. instead of

    =FILTER(Table5[[#Data],[EOS300]],(Table5[[#Data],[EOS300]]<>"")*(Table5[[#Data],[Setting]]=T$1),"")

    I want to substitute the [EOS300]] reference with text in, say, $A$5.

    Note - the text in $A$5 will contain a valid name - I just want to be able to drag the formula.

    Thanks.

    Paul

  • FILTER and UNIQUE - dependent dropdown lists...

    Good morning Carim, good morning Roy,

    A question - is there any vba that will determine if the dependent list contains just one dropdown option/value and, if so, populate the cell with that one option/value bit, if not, then populate it with the dependent list. Using the example attached if I select GB (country) and then Wales in C2 only one value will be returned in D2. If, on the other hand, I select Northern Ireland in C2, multiple values are listed in D2. Rather than displaying a dropdown list where the list contains just one value is there any vba to recognise this single value and populate D2 accordingly - otherwise it will provide the (multiple value) dropdown list.

    Many thanks.

    Kind regards,

    Paul

    Ps. please let me know if I should post this in the vba forum - I have kept it here as you have background knowledge.

  • FILTER and UNIQUE - dependent dropdown lists...

    Hi Roy,

    I am not sure what you mean? I already have a look up cell (F2#). The system works for the first line of tblGPSLocation (A1:D60)... it is when I want to start populating rows below - i.e. B3,C3 and D3... then B4, C4 and D5, and so on. The problem is that the link to the lists are fixed by the validation so no matter what I enter in (e.g.) B3 I will always get GB regions (because C3 is linked to the original option in B2. Does that make sense?

    Thanks.

    Kind regards,

    Paul

  • FILTER and UNIQUE - dependent dropdown lists...

    Good evening,


    Apologies for the poor subject heading... I don't really know how to phrase the problem :)


    Attached is my working example.


    What I am trying to do is to have dropdown lists (via data validation) change dependent on the initial list selection.

    Using the example - I have already populated the first row in a table with the dropdown lists. B2 is a dropdown of country codes (listed in Col F). The dropdown list in C2 links to a filtered list (using the Filter and UNIQUE function) that returns those Regions (in tblLocation) that are in Country Code selected in B2 (which is GB).


    The dropdown list in D2 links to a filtered list (using the Filter and UNIQUE function) that returns those Places listed (in tblLocation) that are in the Region selected in C2 (which is England).


    This is great... but... I want, in the next row, to select different dropdown options. For example, I want to enter 'RU' in B3 and then select 'Southern Federal District’ (which would be one of the RU Region options if it worked). At the moment I cannot find a way of achieving this.


    Any help greatly appreciated.


    Thanks.


    Kind regards,


    Paul

  • INDIRECT function does not work when referencing to a dynamic list

    Good afternoon,

    I am trying to link a dynamic list to the value of a cell (D2) immediately to the left (reference cell). The reference cell (C2) is itself is populated by the user (it is a list of country codes). In other words depending on the country code selected in C2 the user will then be confronted (in D2) with a dropdown list dependent on what country code they select. E.g. for the country code UK I have: Ambleside, London,Manchester set up as a dynamic list (which will expand as and when I add to it). In D2 I set the cell up as a list with "=INDIRECT(C2)" (without quotations) but I have found that it does not recognise this (i.e. no dropdown list appears.

    I have found that if I use a non-dynamic list this works... so assume it is something to do with trying to use a dynamic list.

    Can anyone advise whether there is a work around or if I am doing something wrong?

    Thanks.

    Paul

    (Ps. I was on here before but lost my login details).