Trying to copy a row of information into a new sheet if a column in original sheet has "x"

[cliftag]

I am very new to this and apologize upfront if this is answered elsewhere. I have a master spreadsheet that contains a list of "users" for a piece of software that I use at work. Each "user" has access and permissions to 5 different areas of this software. Each of the 5 areas has its own "tab" within the workbook. If a "user" has been given access to one of the 5 different areas, I put an "x" in the cell under the column header for each area. If a user has an "x" under a particular area, I want that users information in the spreadsheet (column a,b,c and d) to be copied into the separate tab for that specific area. Is this possible?

I am trying to manage the users based on areas of access and permissions.

Any help would be greatly appreciated.

[Pete_UK]

You can set up a helper column for each different area that checks for an "x" and allocates a unique reference. Then it is relatively easy to use an INDEX/Match combination on each subsidiary sheet to bring the data across. If you attach a sample workbook (the FAQ describes how to) then I can show you how to set it up - it will be in the morning, as it's getting a bit late here.

Hope this helps.

Pete

[cliftag]

Pete,

Thank you for the reply. Unfortunately I do not know how to do the necessary steps that you provided. I will try to upload a sample as you asked.

Cheers,
Adam

[cliftag]

Thank you for your help

[Pete_UK]

In the attached file I have put this formula in cell M3 of the master sheet:

=IF(G3="x",MAX(M$2:M2)+1,"-")

This can then be copied into N3:R3 for the other packages, and then M3:R3 is copied down as far as you need to (i.e. beyond your data, to allow for new data to be added) - the hyphens help to show how far the formulae are active. The formula sets up a unique sequential number for each user who qualifies for each package in turn. These helper columns can be hidden if you don't want to spoil the look of your sheet.

In the Citrix sheet I have inserted a new column A (slightly more convenient to do it there - easier to hide if you wish to), and put this formula in A2:

=IFERROR(MATCH(ROWS($1:1),'ALP Registry'!M:M,0),"-")

When this is copied down, the ROWS($1:1) term becomes ROWS($1:2) (returning 2) and then ROWS($1:3) (returning 3), so the formula looks for those sequential numbers on each row and returns the row where they occur in column M of the master sheet. Cell B2 contains this formula:

=IF(OR($A2="-",$A2=""),"",INDEX('ALP Registry'!A:A,$A2))

which returns the data from column A of the master sheet if there is a row indicated in cell A2. This formula can be copied across into C2:E2, and it will return data from the appropriate other columns. The formulae in A2:E2 can then be copied down as far as required (I've just done it to row 10).

I then deleted your other sheets and took 5 copies of the Citrix sheet and then renamed each of them in turn. In each of those sheets you just need to make a simple adjustment to the formula in A2 - instead of:

'ALP Registry'!M:M

you need to change this to:

'ALP Registry'!N:N

in the EAGLE sheet, and copy it down, so that it is looking at column N of the master sheet, and to:

'ALP Registry'!O:O

in the EPS sheet, and so on for the other sheets.

I've added some more made-up data in the master sheet so you can see it working in practice. This is a dynamic solution, so if you were to add an x to G5 of the master sheet, for example, then Jo Bloggs will be pushed into 4th place on the Citrix sheet. Similarly, if you were to remove an x, then there would be appropriate automatic adjustments to the corresponding sheet.

Hope this helps.

Pete

[cliftag]

Pete,

This is all wonderful....I think
Im still confused. When I change the names or delete names or "x"......nothing in the corresponding sheets changes. Am I missing something here? Id even call you if you wanted. Are you in the UK?

[cliftag]

Nevermind.....it looks like I have to hit the "save" button in order to update. I figured I could just refresh the data.

You are awesome mate, really appreciate your time and efforts.

[Pete_UK]

You're welcome - glad to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete