autofill a rrange based on value entered in a different cell

[runway]

Hi,

I should intorduse myself in the "saying hello" forum first but didn't....my bad....


So, I have a shiftplan or time schedule I am trying to make easier to use.

The sample workbook explains it all but briefly, I want to type a letter into one cell (say any cell in C1 and then autofill c2:c20 based on a pattern somewhere else on the spreadsheet. It is difficult to make this clear but the example spreadsheet explains perfectly. I hope.

Any help would be appreciated. I have a feeling the best options will involve vba of some sort.....


sample spreadsheet here:-


http://www.mediafire.com/?476j49abpnjrxk7


Many thanks in advance to anyone who can help out.


Best Regards,

 

[robg]

I know you said you'd prefer not to use formulas because you'd have to fill the entire range, it's actually pretty easy. If you use the right formula you won't have to change it for each cell in the range C2:L6.

[pre]

=IFERROR(IF(VLOOKUP($B2,$B$11:$L$14,COLUMNS($B2:C2),FALSE)="*","*",""),"")

[/pre]

Highlight the whole range (C2:L6), enter this formula and press CTRL-ENTER which will copy it to all cells at once.


Add a little conditional formatting and you're done.

 

[runway]

That works fine BUT (sorry...) as these are time schedules it is actually necessary to adjust them during the week/month etc. Currently we just select and drag a filled cell across into a blank area to create extra hours and select/drag empty cells across to remove hours. Unfortunately once the cells have a formula in (great tip for entering a formula into multiple cells by the way, very useful....)the flexibility to change them on the fly is gone.

So if we say Bob has an A shift the last filled cell against his name is in column G. But we need Bob to work an extra hour so we select G2 and drag to H2. With your formulas in every cell nothing happens as we are just pulling the formula from G to H and it is still referencing B.

That is why I need some method of filling the rows by referencing the list which doesn't involve putting a formula into every cell.

What we need is to be able to fill the rows quickly and easily and also fine tune/tinker with them afterwards with complete freedom.


I guess I should have provided more info to start with, sorry....

But thanks for your effort, it does actually achieve what I asked, I just didn't ask enough.....


My first thoughts where some kind of cell validation/drop down box affair that would autofill a row based on the letter chosen from the box but I could only get a cell autofilled not a row..... And the drop down box slows down the process of entering the shift letter.

 

[robg]

So you want to be able to manually adjust after you've done the initial setting with the Shift code.


Here's some VBA to try. Right click on the sheet tab and copy/paste this code.

[pre]

Option Explicit
Const TRAPCELLS = "B2:B6"
Const LOOKUPCELLS = "B11:B14"
Const SHIFTCOLUMNS = 10

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sourceRow As Integer
Dim copyRange As Range

If Not Intersect(Target, Range(TRAPCELLS)) Is Nothing Then
On Error Resume Next
sourceRow = Application.WorksheetFunction.Match(Target, Range(LOOKUPCELLS), 0)
If sourceRow = 0 Then Exit Sub
Set copyRange = Range(LOOKUPCELLS).Cells(1, 1).Offset(sourceRow - 1, 1)
Set copyRange = Range(copyRange, copyRange.Offset(0, SHIFTCOLUMNS - 1))
copyRange.Copy
Target.Offset(0, 1).PasteSpecial
Application.CutCopyMode = False
Target.Offset(1, 0).Select
End If
End Sub

[/pre]

If you change ranges you can alter the constants at the top, or preferably use named ranges instead.

 

[runway]

BINGO!

Thanks, I have absolutely no VBA knowledge whatsoever and (shamefully) the right click on the sheet tab and copy/paste threw me for a bit.

But I have succeeded and it works!

So many thanks.

I can see how to modify the code for my real timesheets.


I have changed the cell references in the list of Const to named ranges, ie Const TRAPCELLS = "MYNAMEDRANGE"


Everything still works fine.


And I can see the purpose of the shiftcolumns bit.....


And now I have seen it in action I'm not scared of VBA anymore, I will have to do some proper studying....


Thanks guys, you have both been really helpful, much appreciated!


All the best, I am off to bed now ready to wow my boss on Monday