Data Search & Adjustment

[Don Juan]

I am trying to find a way to have a cell look into a group of other cells and display the first available things it comes to. Then have the next cell look in that same group and display the next item.



cells A1:A5 have 3 pieces of information in them scattered among that column (A1, A3, A5 might have the info in it one day, then A2, A3, A4 the next day)

I want B1:b4 to find the info in the A1:a5 and display it in order as it appears in the A column.

Greatly appreciate all help.

[Trebor76]

Hi there,

Let me know if the following macro meets your needs:

Sub Macro1()

Dim lngColBRowNo

lngColBRowNo = 1

For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If IsEmpty(cell) = False Then
        Cells(lngColBRowNo, "B").Value = cell.Value
        lngColBRowNo = lngColBRowNo + 1
    End If
Next cell

End Sub

HTH

Robert

[Don Juan]

Thanks for the help. Its not quit what I was looking for. I uploaded the workbook so you can see more specificly what I need. This is a schedule generator for papa johns pizza. The problem is the shifts won't always be the same which is creating a programing nightmare for me. You will see the shift times in the A column which I have chosen to use a drop down menu to change. When I change the shifts or choose to add or take away another shift I need everything on the worksheet to auto update the shifts. You can see what I need to auto update in cells: J35:j41 & M35:m41 & t25:au25

Another problem I have though is if you look down to q59:aa57 those boxes search the schedule to generate the amount of hours worked for each person. I have put name labels into it so that a certain number of cells in the schedule is counted only. So on the insider section lets say 5pm-8pm has cells b11:h13 associated with it. q59:aa57 would count that section and display it in e43:e58 and only count it as 3 hours worked.

So the problem is when I add new shifts into the schedule or take away shifts I need the program to adjust so that it will still find the hours a person worked and display them. There might need to be a different way of doing it. Maybe instead of using countif I could have it search through the a column to find the shifts then search for the name of the person in the cells alotted? Anyway, any help on this would be greatly appreciated. Its a bit beyond my skill level.

Thanks,
MIke

[Don Juan]

I've been looking over this for days and i'm really at a loss. Any help would be appreciated greatly.

[Trebor76]

Its a bit beyond my skill level

mine too unfortunately

The only thing I can suggest is create named ranges as these are much more dynamic than direct cell referencing.

[Don Juan]

thanks anyway. Btw, the cell range is named (im not directly referencing the cell). That is part of the problem. When I add more shifts, unfortunately it shifts the cells associated with the shift times. Anyone else have any ideas?

[ratcat]

......................
Anyone else have any ideas?

G'day guys,

I've added a few formula to Don's workbook.

I haven't fully completed the all the quests (I think), nor the fill the all formulas to all the cells. Because I not sure I'm on the right track

But you should get an idea what I'm trying to achieve and hoping I've helped you out.

Any feedback would be great.

HTH

Cheers

[Don Juan]

Thank you for looking at the workbook. I looked at what you did. I think you may have misunderstood what the J33:N42 box is about. The schedule will eventually be auto generated. To do that I need to tell the program how many people per shift I need. So that box is to put the number of persons per shift I need. The problem I was dealing with is first lets say I decide to add another shift to A14. I need everything else on the worksheet to auto update the fact that that shift is now there. SO the J33:n42 box would now display that shift and q22:au52 box would display the shift for each day of the week. Right now you can see that some of the boxes in the j33:n42 range have 0s in them. Thats because they are referenced to one cell in the a column right now.

[Don Juan]

what formula would I use in say cell b1 to search a column range say a10:a15 for input and then have it display that input then have b2 search using the same formula and search criteria but it only display the next data entry in the A10:a15 not the same as b1...

[Don Juan]

I've got a lollipop for anyone who can solve this riddle!