0

I'm trying to create a formula so that Column E will output the Employee names on Column A. I've provided an image below as my example. As you can see, there is a pattern of 4 vacant after every employee's name.

enter image description here

I've tried using vlookup to no avail. Been working on offset for more than an hour now but it seems that I want the opposite of how the offset works like this

Improve this question
2
  • Hi! I'm trying to understand what you're trying to do? Should the "Vacant" be editable? Is the list of employee names the only dataset that needs to be taken into account?
    – David Tan
    Apr 30 at 5:42
  • @DavidTan thanks for taking the time on my post. The vacant part is employee info which is fixed. What I'm trying to do is to re-organize various information and make it printer-friendly so the user can print the google sheet with various info of employees quickly.
    – Cary Bondoc
    May 1 at 7:55

1 Answer 1

Reset to default
1

I'm not sure whether this answer your question, but I think this should do it. https://docs.google.com/spreadsheets/d/1QZJODzDyA8RgM_Skh0Kor7O1r4-7dtmU1eROtAfIwzA/edit?usp=sharing

=IF(MOD(ROW()-1,5) = 0,OFFSET($A$1,CEILING(ROW()/5),0),"Vacant")

This is the function column E. Though it still needs to be adjusted since I see that your data starts at row 47. I'm not exactly sure on how this works, but it does.

=IF(MOD(ROW()-2,5) = 0,OFFSET($A$47,CEILING(ROW()/5)-9,0),"Vacant")

Note: You will need to use the fill handle because an array formula won't work. For some reason, this method of selecting then using the fill handle seems to work. It auto-increments the Employee number even though there isn't a pattern established.

enter image description here

Improve this answer
1
  • Beautifully done! Thanks a lot!!
    – Cary Bondoc
    May 2 at 6:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.