Seeking assistance to populate a list from a table

[auto.pilot]

I searched and can't find anything quite like this. I have a table with times the range of D3:L3 and dates starting in B4 and below. I'd like users of this workbook to add an 'x' in time slots that are available and open. Thereafter, I'd like some code or formulas that would populate the list shown in column N. The colors are simply for posting to this forum and will not be used.

I have written a formula that populates a single cell, for example from the x in cell D4, I wrote a formula that produces the desired date and time format in cell N4.

I am struggling to populate a list like what is shown in column N. I guess I'd prefer a code-based approach.

Thanks in advance for any and all assistance.

Jim

 

[alansidman]

Cannot manipulate data in a picture. You can unpivot this data in Power Query. Any updates will automatically refresh the results. If you wish to see a demonstration, then please reload your data using XL2BB. If unsure how to use, read my signature.

 

[auto.pilot]

Thank you for your suggestion of using a Power Query. However, I find this method a bit clunky. I need to manipulate the data further once it is output from the Power Query Editor using Close & Load. This workbook will be used by multiple others in our organization, and I won't be able to count on each of them to learn about Power Query. In addition, formulas that reference the output from Close & Load throw off a ref error if I clear the table, then re-run the Power Query.

If there are any other suggestions, I'm open to try.

Thanks

Jim

 

[Akuini]

Could you post a sample data as a table instead of image? So we can copy-paste it to Excel.
Please use XL2BB add-in.

 

[auto.pilot]

I will try. Not sure if I can use the add-in from my office. Thanks!

 

[auto.pilot]

Unfortunately, I am unable to use the XL2BB add-in. Company firewall prevents me from extracting it. I get an error message for which there is no work-around. I would appreciate any other suggestions.

Thanks in advance.

 

[Akuini]

Unfortunately, I am unable to use the XL2BB add-in.

Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.

 

[auto.pilot]

Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.

I will try that. Thanks

 

[auto.pilot]

Here's a link to the sample on google drive: Loading Google Sheets

I am not a regular user of google drive, so please let me know if you are able to get the file.

Thanks very much for your assistance.

Jim

 

[Akuini]

Thanks for the file. The result in col M, is it going to be a text instead of date+time?