Hi,
I'm not sure if the following is possible, but here goes:
I have an Excel workbook with several tabs. Tab 1 contains the 'masterdata', to make it simple say it looks like this:
<tbody>
</tbody>
What I want now is for Excel to check if there's a 1 in the last column, or no value. If there is a value, I want to copy data to a second tab, but not all data.
Example1: It checks row D for the value '1'. Excel finds a value of 1 in D2, so it copies the Name and the Country (A2 and C2) to another tab in the workbook.
Example2: Excel continues down and finds another 1 in D7. Again it copies the Name and Country (A7 and C7) to another tab in the workbook, right below the one from Example 1.
Why? Tab 1 is an Excel 'database' of all new hires in my company for the current BY, sorted by business unit and contains all possible info. Tab 2 etc are several subdatabases that calculate different KPIs based on subsets of data in Tab 1, also sorted by business unit. In total there are 4 different tabs for 4 different KPIs. These are split up because the 4 KPIs apply to 4 different HR business units. Right now we are basically copying the relevant data for Tab 2/3/4/5 from Tab 1, which is ridiculous and way too time-consuming. What I want to do is basically only input data in Tab 1 and then have the relevant data copied to the remaining tabs automatically.
To make it a bit more concrete: In Tab 1 i list the following (after each / there's a new cell in the same row):
Function / Reason for job vacancy (X leaves the organization) / New hire (Y joins the organization) / when was the job vacancy published / when was it closed / when did Person X leave / when did person Y join.
In Tab 2 I want to copy the following:
Function / Reason / New Hire / job vacancy publish date / closing date / formula that calculates the time between the 2 dates.
In tab 3 I want to copy the following:
Function / New Hire / when did X leave the organization / when did Y join / formula that calculates the time between the 2 dates.
I, sadly, don't know a lot about Excel. I wanted to make use of nested IF functions, but that's too limited. For some business units I would need over 70 nested IF functions in order to do what I want to do.
Another option would be to turn Tab 1 into one large database with all the formulas for the different KPIs and then apply filters or something, but that sounds too messy and I'm afraid that will be too complex for our senior management.
I'm not sure if the following is possible, but here goes:
I have an Excel workbook with several tabs. Tab 1 contains the 'masterdata', to make it simple say it looks like this:
| Category 1 | |||
| Name | Address | Country | 1 |
| Name | Address | Country | |
| Name | Address | Country | |
| Category 2 | |||
| Name | Address | Country | |
| Name | Address | Country | 1 |
| Name | Address | Country |
<tbody>
</tbody>
What I want now is for Excel to check if there's a 1 in the last column, or no value. If there is a value, I want to copy data to a second tab, but not all data.
Example1: It checks row D for the value '1'. Excel finds a value of 1 in D2, so it copies the Name and the Country (A2 and C2) to another tab in the workbook.
Example2: Excel continues down and finds another 1 in D7. Again it copies the Name and Country (A7 and C7) to another tab in the workbook, right below the one from Example 1.
Why? Tab 1 is an Excel 'database' of all new hires in my company for the current BY, sorted by business unit and contains all possible info. Tab 2 etc are several subdatabases that calculate different KPIs based on subsets of data in Tab 1, also sorted by business unit. In total there are 4 different tabs for 4 different KPIs. These are split up because the 4 KPIs apply to 4 different HR business units. Right now we are basically copying the relevant data for Tab 2/3/4/5 from Tab 1, which is ridiculous and way too time-consuming. What I want to do is basically only input data in Tab 1 and then have the relevant data copied to the remaining tabs automatically.
To make it a bit more concrete: In Tab 1 i list the following (after each / there's a new cell in the same row):
Function / Reason for job vacancy (X leaves the organization) / New hire (Y joins the organization) / when was the job vacancy published / when was it closed / when did Person X leave / when did person Y join.
In Tab 2 I want to copy the following:
Function / Reason / New Hire / job vacancy publish date / closing date / formula that calculates the time between the 2 dates.
In tab 3 I want to copy the following:
Function / New Hire / when did X leave the organization / when did Y join / formula that calculates the time between the 2 dates.
I, sadly, don't know a lot about Excel. I wanted to make use of nested IF functions, but that's too limited. For some business units I would need over 70 nested IF functions in order to do what I want to do.
Another option would be to turn Tab 1 into one large database with all the formulas for the different KPIs and then apply filters or something, but that sounds too messy and I'm afraid that will be too complex for our senior management.
Last edited:
