0

I have 2 Spreadsheets

S4 (Sheet 1)(Purple screen shot) **Clients that Fit Hire (Sheet 2) **(all other screen shot) (this is the sheet I need help with S4 is functioning correctly - rows are not aligning on Sheet2)

https://docs.google.com/spreadsheets/d/1BipApj7oYrbpeOSwk3wrSgceZ88CA4Rmt4YBYPIu3Nk/edit?usp=sharing

The main sheet is the S4 (Sheet 1) and if a client is identified as a potential fit for Hire the box in ColZ is checked.

When the box is checked it transfers selected cols to Client that fit Hire (Sheet 2).

Once data has been transfered to Clients that fit Hire (Sheet 2) they then have Col K-P on Sheet 2 where they will manually input data (Static).

Problem: When a new client is checked on the S4 (Sheet1) sheet and those cols transfer to Clients that fit Hire (Sheet 2), any notes that were previously made in a row on the client in Clients that Fit Hire (Sheet 2) those notes do not align with their original row and all dynamic data shifts and now aligns with notes that are not supposed to be with that row

[enter image description here](https://i.stack.imgur.com/tkqsQ.png)
[enter image description here](https://i.stack.imgur.com/1ad7o.png)
[enter image description here](https://i.stack.imgur.com/SDezY.png)
[enter image description here](https://i.stack.imgur.com/aKybj.png)
[enter image description here](https://i.stack.imgur.com/4okMR.png)
[enter image description here](https://i.stack.imgur.com/jxTGO.png)

I have this that is pulling the data I need from S4

This is the most recent formula I tried:

={"Date Assigned", "Assigned IS", "AdCard ID", "Account Type", "Sub/Account Name And ID",   "Opportunity Name And ID", "Sales Rep Name", "Close Date (Year-Month-Day)", "Schedule Amount"; ARRAYFORMULA(IFERROR(VLOOKUP(A3:A, 'HIRE POTENTIAL'!A3:J,{2,3,4,5,6,7,8,9,10},0)))}*

These are others I have tried:

={"Date Assigned", "Assigned IS", "AdCard ID", "Account Type", "Sub/Account Name And ID", "Opportunity Name And ID", "Sales Rep Name", "Close Date (Year-Month-Day)", "Schedule Amount"; ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, FILTER('HIRE POTENTIAL'!A2:J,'HIRE POTENTIAL'!A2:A=""),{2,3,4,5,6,7,8,9,10},0)))}*

=ARRAYFORMULA(IFERROR(VLOOKUP(A3:A, 'HIRE POTENTIAL'!A3:J,{2,3,4,5,6,7,8,9,10},0)))
Kayla Mortensen
  • 1

1 Answers1

0

The simplest answer may be to simply check the checkbox for "Potential Fit" then manually copy/paste the row for that candidate over to the other sheet.

If you're already manually checking the checkbox then this isn't much extra work and will get you your desired result of having the notes always stay with the correct row because it's not doing any fancy shifting based on formulas.

The only other ways I can think of to have the notes stay with correct rows is to either (a) add them in original tab, or (b) have a 3rd tab with the candidate id and/or name and the notes there so you can join them in the 2nd tab using an XLOOKUP on the candidate id.

Both of these seem overly complex for what you're trying to do. My advice would be to go with the simpler process of manually checking the box then copy/pasting over to the 2nd tab.

horanimal
  • 352
  • 1
  • 10