Drag date and ignore hidden cells

Jeorge

New Member
Joined
Nov 27, 2015
Messages
32
Hi Sir Ma'am,

Need help to put the dates only on visible cells, I hid A2, A3 then hid A5, A6 then hid A8, A9 and so on... With that my visible cells were A1, A4, A7, A10.
A1= 1-Apr and I wanted to drag it to A10 and my expectation was
A1 = 1-Apr
A4 = 2-Apr
A7 = 3-Apr
A10 = 4-Apr
but when I drag it, hidden cells are still considered which failed my date that I wish to see only on visible cells.

Thank you very much for any help
 
I wanted to drag it ...
None of the suggestions meet this requirement, my suggested formula could be dragged in the same way as any of the others.

My suggested formula and yours will both fill to the hidden cells if they are dragged and both will give the correct dates with the current pattern of hidden rows. I only suggested selecting visible cells on the assumption that the formula should not be entered into those which are hidden, although I did neglect to point out that it should be filled with f2 > ctrl enter rather than dragging.
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi jasonb75,

I did used the formula you gave me "=DATE(2020,4,(ROWS(A$1:A1)-1)/3+1)" and it did worked as it skip the date for hidden files though upon un-hiding cells it showed same date from first cell.
It made my work faster that typing date by date, I'll just have to delete those dates on my hidden files.

Thank you guys for the effort!
Footoo
Peter_SSs
jasonb75
 
Upvote 0
though upon un-hiding cells it showed same date from first cell.
.... I'll just have to delete those dates on my hidden files.
That sounds like as much work as just entering the required dates in the 4 cells to start with. :cool:

It also makes it sound like your visible and hidden rows are fixed. If that is so, you could try this in A1 and dragged down.

=IF(MOD(ROWS(A$1:A1),3)=1,DATE(2020,4,1)+INT(ROWS(A$1:A1)/3),"")
 
Upvote 0
That sounds like as much work as just entering the required dates in the 4 cells to start with.
But still not as much work as creating this thread ?

On the assumption that you're working with more than just 4 dates in your actual file, there is also the option of using ctrl g to select only visible cells, then f2, ctrl enter to fill the formula instead of dragging down. If you do this then the hidden rows will remain empty.

Combine this with Peter's formula from post 8 and it should work with any number of hidden rows, even if the pattern is changed.
 
Upvote 0
it should work with any number of hidden rows, even if the pattern is changed.
That still requires that every visible cell in column B for the selected range contains data or some very strange results may emerge. Very much a leap of faith I would have thought.
 
Upvote 0
I will try to work the new formula out and if it gave a more reasonable result I will immediately inform you guys, thank you very much for your help jasonb75 and Peter_SSs
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top