PDA

View Full Version : Find first available blank in a row



robinssdawn
2017-12-01, 05:28 PM
I have a spreadsheet which shows the duration of when puzzles are worked on. This spreadsheet will be made available to all my FB puzzle group members esp useful for the contests and challenges we will be involved in. Each puzzle will take a differing amount of sessions to complete. I have designed the spreadsheet to start at Session 1 and end at Session 120 (probably wayyyy more than enough sessions). Example: if someone has currently used 30 sessions to work on a puzzle but still has not completed it, I don't want them to have to scroll ALL THE WAY to Session 31 to enter the date/times for their new session. (Granted there are other "manual" ways to get to Session 31 but not everyone is Excel savvy enough to do so). So, I want them to be able to use a hyperlink to the next blank cell in that row (if you have any other method that will work (insuring it's user friendly), I am open to that too).

I have attached the spreadsheet to this inquiry to make it easier to follow along, as described below:


I made an outline (columns E-J) which is not to be included in the formula I need. it merely shows informational content regarding the puzzles. The formula i need needs to start finding blank cells starting from column K (session 1) to column WL (session 120) for each row.
Column D shows each line item that I will refer to here going forward. I also want each line item (1, 2, 3, etc) to be what we click on to get to the next empty blank cell.



So, if Line item 1 is clicked (hyperlink?), the active cell should land at cell address L4.
If Line item 2 (hyperlink?), is clicked, the active cell should land at AJ5
If Line item 3 (hyperlink?), is clicked, the active cell should land at N6.


As mentioned, it would be nice if somehow the line items could be the hyperlink to take the user to the first located blank cell. Obviously, the blank cell location will change as the user continually works on a puzzle, so the location of the blank cell for a session needs to obviously change.

Nothing needs to be done in any columns preceding column D.

Hope someone can help.

Bob Phillips
2017-12-01, 07:33 PM
No attachment.

robinssdawn
2017-12-10, 04:26 AM
Sorry - had some life issues to attend to and then try to figure out how to attach a file AFTER the fact......

PLEASE SEE ORIGINAL POST BELOW

UPDATE: the original file was too large to upload so I had to make some adjustments to the spreadsheet from the original post and change the file format to xlsb - the adjusted cell address is below (Z5):




So, if Line item 1 is clicked (hyperlink?), the active cell should land at cell address L4.
If Line item 2 (hyperlink?), is clicked, the active cell should land at Z5
If Line item 3 (hyperlink?), is clicked, the active cell should land at N6.

p45cal
2017-12-11, 01:01 AM
In the attached, see formula in cell D4 (click and hold to prevent the hyperlink working):
=HYPERLINK("#"&CELL("address",INDEX($K4:$WL4,MATCH(TRUE,INDEX(ISBLANK($K4:$WL4),0),0))),1)
The red 1 is what was in the cell before.
You can copy this formula down column D, the only problem being that you'll have to edit that red 1 for what was in the cell before for each cell.
You can run the macro blah in the file to speed things up; you select the cells in column D that you want to add the formula to, then run blah. Whatever was in each cell beforehand, remains showing.
Clicking on any of these cells should do what you're after.

Edit post posting: Updated little macro to handle text and empty cells better.

robinssdawn
2017-12-11, 06:20 PM
thanks p45Cal!

However.....it works like a charm in your file, but when I tried it in myactual file (copied the formula from your file to mine), and clicked, the Active cell went to the first blank cell which was in theOutline area - not from the Day 1 section of the spreadsheet.

So, I triedsomething, I made a copy of the sheet I plan to use, and deleted the outlinecolumns (actually deleted them, did not just remove the outline) and theformula worked perfectly in my file.

But in reality, Ineed to keep the outline and outline columns and still make this work.

p45cal
2017-12-11, 06:32 PM
The formula looks for the first blank cell in columns K:WL. Are these not the relevant columns in your file? If not, change both instances of the column references in the formula to your column range.
=HYPERLINK("#"&CELL("address",INDEX($K4:$WL4,MATCH(TRUE,INDEX(ISBLANK($K4:$WL4),0),0))),1)

If that doesn't work, I need to see your file.