Populating info from one row into another row within a range if certain data exists

[SergeantDarth]

Fellow Excel Users,

I'm having difficulty coming up with a formula for this issue. It may be I need a VBA?
Here's what I need to do: (Please reference attached Excel workbook)

If "LV" appears in Column I (in this case row 3) then I need a formula to copy the information in A through G of that row, search for an empty row in 9 through 13 and paste the info in one of the available blank rows. I cannot have any existing data in rows 9-13 erased.

Any help would be appreciated as I've racked my brain trying to come up with something that will work but have had no success.

Thanks!

[JieJenn]

Like this?

Please Login or Register  to view this content.

[simarui]

are there always going to be as many available rows in the second table as the first? Are you ok with (as is showing in your example) blank rows except for the corresponding entry with the LV to the right? Or does it need to populate into the first blank row?

If the former then a pretty simple If function will do the trick:

Please Login or Register  to view this content.

Also noticed your column H is based on the value in E... With that formula in there every row populates with a "1"

Need to change the forumula in H to:

Please Login or Register  to view this content.

[SergeantDarth]

JieJenn,
Thanks but the button you created radically alters the appearance of the "Leave" area and when I remove the "LV" from column I it doesn't remove it from the leave area. I need the formatting of all these area to remain intact. I just need the info contained in the first area to populate in the leave area and then dissappear again after I remove the LV without changing the way the sheet looks.

[SergeantDarth]

simarui,
The real spreadsheet has about 400 employees listed and only about 30 available leave rows. Yes, I'm ok with the blank rows except for the corresponding entry with the LV to the right. When the LV appears up above I'd like it to throw the info from that row in any blank row down under the leave area.

Column H sums at the bottom of that section which is why I have numbers there. If there are no numbers in Column H then there should be no employee listed in that row.

Thanks!

[JieJenn]

Oh. I thought you want to append the data. Anyway, just add the range clear line then.

Please Login or Register  to view this content.

[SergeantDarth]

Keep in mind Jenn....I'm very unfamiliar with VBA. How do I implement this code to see if it works?

Ok~ I figured out how to view code, entered it then hit the play button but it still raadically modified the format of the "Leave" area. Also, when I removed the LV from column I it did not remove the info from the leave area either.

[simarui]

you're going to have to re-run the macro each time to get it to update when you add/remove LV...

This can be done with formulas, and i'm really close, but i have to go.

work with:

Please Login or Register  to view this content.

Where A7:G7 in your sample workbook is set to 1-7... still needs some tweaking, but it should get you going in the right direction and will automatically update when you add/remove LV's...

[SergeantDarth]

simarui,
Thanks for your dedicated assistance! I was real close also by using a bunch of nested IF formulas. Hopefully you'll have an epiphany overnight. I await your reply.

[simarui]

Just remembered a similar request on another post which was helped dramatically with a counter column. In my attachment I've added the following to column J in your master list

Please Login or Register  to view this content.

Then added 1-6 to column J in the 'leaves' table, and with that you can populate your entire leaves table with:

Please Login or Register  to view this content.

I believe this will give you exactly what you're looking for, with some tweaking of course to match your actual data set.

[SergeantDarth]

You're awesome!
Question: Instead of adding numbers 1-7 in A7:G7 couldn't I just use the column names (A1:G1) at the top (i.e position#, shift, name)?

[simarui]

you can change it to:

Please Login or Register  to view this content.

or if you've named your ranges for each column then you can use Column(RANGE NAME)...

Other than that i'm not aware of a way, but that doesn't mean it doesn't exist i learn something new about excel just about every day.

[SergeantDarth]

OK,
Well rather than change those I simply hid the row and column J so the numbers wouldn't show. Thanks again for your assistance. After looking at the way you went about it I could tell I wasn't even close with my nesting "IF"s but I definately learned something.