Keep User input column matched with dynamically adjusting column

[KnightFhawker]

Hi all,

I hope I am using the proper terminology in this post, and hope I can explain it well enough for you to understand.

I have a tracking worksheet that pulls a list of names from another workbook (the source) if their status is considered "Current" (this status is found in a related adjacent column on the source workbook). Only the names with "Current" status are pulled, all others are ignored. Therefore if the status changes to "Current" within the source workbook, the tracking worksheet will automatically add this to the list in alphabetical order (as it is listed in the source workbook). This all works just fine; however, the whole point is to have this tracking worksheet allow for user input for notes and other info specifically related to the name it is entered next to. The issue I have is that when a new name appears on the tracking sheet, that column is adjusted appropriately, but the user input columns stay in the same place and are then listed next to a mismatched name.

For instance, assume there are 5 names that are considered "Current" and listed accordingly on the the tracking worksheet:

(I apologize for the Names and User input columns running together, but it keeps deleting the extra separating spaces when I post it)

Names User input

Name A Name A Info
Name C Name C Info
Name E Name E Info
Name G Name G Info
Name I Name I Info


Then if a 6th name (Name B becomes current that is added to the list, shifts the names column down after the Name A, but leaves all User input in it's original row, now mismatched)

Names User input

Name A Name A Info
Name B Name C Info
Name C Name E Info
Name E Name G Info
Name G Name I Info
Name I


In case it is important, I am using a simple IF formula to determine what is added to the names list.
i.e. IF('[Source Workbook]Sheet1'!$A1="Current", '[Source Workbook]Sheet1'!$B1, "")

Does anyone know if there is a way to overcome this issue, either by maybe keeping user input matched to an original master column, or have all columns move up or down based on the shift of the names column?

Thank you in advance for your help.

 

[Krayons]

If I understand you correctly, use vlookup()

Paste into B2 and drag down.

=vlookup(A1,'[Source Workbook]Sheet1'!A:B,2,false)

Edit:

This probably wont work for your exact setup, but pretty much vlookup() seems like the answer your looking for.

=VLOOKUP(lookup_value, table_array, column_index_number, false)

 

[KnightFhawker]

Thank you very much for your response Krayons.

In order to try not to complicate this any more than I already have I didn't go into the additional columns I have, but I do already have additional columns pulled from the Source worksheet which adjust just fine with the names. The issue is that the User input is entered by the User into the Tracking worksheet next to the appropriate matching Name. Therefore, I need to shift the User input up or down with any addition or subtraction of names to the Tracking worksheet. If it is even possible at all, since the User input cells need to be kept clear for the User input, I would assume the only options would be some type of VBA code. My guess is that it would either have to recognize a shift in the Names column and then shift the other columns accordingly, or somehow match the User input to the appropriate Name in the Name column next to it upon entry (essentially making the Name column a master column) then have the user input move up or down to follow the original name it was entered next to.

Something like, with any user entry input, refresh For all Column B Cells to link to Column A names in the same row, then refresh Column B cells to readjust and maintain the original Column A name link with any new addition or subtraction to Column A

Hopefully this will help better describe the setup. If not I apologize. Just let me know and I'll do my best to try a different way to explain it, or answer any question you might have.

Source Worksheet:

Current Name A
Expired Name B
Current Name C
Expired Name D
Current Name E
Expired Name F
Current Name G
Expired Name H
Current Name I

Tracking Worksheet:

Names User input
Name A Name A Info (Input by user on this worksheet)
Name C Name C Info
Name E Name E Info
Name G Name G Info
Name I Name I Info


If Name B becomes Current then this occurs:

Source Worksheet:

Current Name A
Current Name B
Current Name C
Expired Name D
Current Name E
Expired Name F
Current Name G
Expired Name H
Current Name I

Tracking Worksheet:

Names User input
Name A Name A Info (All user info stays in place, while the Name B is added shifting all of the entries in Column A after Name B, down. This causes a mismatch with the info and Names)
Name B Name C Info
Name C Name E Info
Name E Name G Info
Name G Name I Info
Name I


If this is not possible or too complicated I definitely understand. Either way, again, thank you in advance for your time.

 

[Krayons]

I understand, so the user enters the comments on the tracking sheet itself, not the source sheet.

Basically, to sum it up, notes are being entered in a static column, and want those to automatically line up with a dynamically changing column.

In this case, your going to have to give me all the puzzle pieces in order to solve the puzzle. I need to know how you are pulling these names from the source sheet to the tracking sheet. Is this being accomplished through VBA? If so, what code are you using to accomplish this? If I had to guess, it almost sounds like your just using an INDIRECT() array formula to pull them up, and they are alphabetical because they are sorted that way on the source sheet.

Either way, need specifics my man.

 

[KnightFhawker]

Exactly Krayons, you've got it.

Being somewhat new to the site, I'm not sure what the best way is to share what you might need, so feel free to educate me on the process if necessary.

Actually, I kept it pretty basic. I usually do use an INDIRECT formula, but this time around, for better or worse, I happen to use the following:
(I am happy to change this around in any way though.)

Column Titles are in Row 5 and the Information runs from Rows 6-79 currently, many blank at this point since it is only displaying those names that are "Compliant"

Formulas in the First Row of each column (*):


(Static) User Input:

User Date Input (B)

User Contact Type Input (C)


(Dynamic) Information:

Status (D):
=IF(('H:\LEASES\[Insurance and Questionnaire List & CPI Tracking Sheet.xlsm]Tracking (Sorted) (4)'!$C5)=0,"",('H:\LEASES\[Insurance and Questionnaire List & CPI Tracking Sheet.xlsm]Tracking (Sorted) (4)'!$C5))

Names Column Field (E):
=IF(('H:\LEASES\[Insurance and Questionnaire List & CPI Tracking Sheet.xlsm]Tracking (Sorted) (4)'!$S5)="Compliant",IF(('H:\LEASES\[Insurance and Questionnaire List & CPI Tracking Sheet.xlsm]Tracking (Sorted) (4)'!$G5)=0,"",('H:\LEASES\[Insurance and Questionnaire List & CPI Tracking Sheet.xlsm]Tracking (Sorted) (4)'!$G5)),"")

Expiration Date Field (F):
=IF($E6="","",IF(('H:\LEASES\[Insurance and Questionnaire List & CPI Tracking Sheet.xlsm]Tracking (Sorted) (4)'!$T5)=0,"",('H:\LEASES\[Insurance and Questionnaire List & CPI Tracking Sheet.xlsm]Tracking (Sorted) (4)'!$T5)))

Secondary Insurance Type (G):
=IF($E6="","",IF(('H:\LEASES\[Insurance and Questionnaire List & CPI Tracking Sheet.xlsm]Tracking (Sorted) (4)'!$U5)=0,"",('H:\LEASES\[Insurance and Questionnaire List & CPI Tracking Sheet.xlsm]Tracking (Sorted) (4)'!$U5)))

Secondary Insurance Expiration Date (H):
=IF($E6="","",IF(('H:\LEASES\[Insurance and Questionnaire List & CPI Tracking Sheet.xlsm]Tracking (Sorted) (4)'!$V5)=0,"",('H:\LEASES\[Insurance and Questionnaire List & CPI Tracking Sheet.xlsm]Tracking (Sorted) (4)'!$V5)))


(Static) User Input:

User Contact Info Input Columns (I-O)

[TABLE="width: 1993"]
<tbody>[TR]
[TD]Date Notice Sent[/TD]
[TD]Notice Type[/TD]
[TD]Status[/TD]
[TD]Tenant Name[/TD]
[TD]Primary Insurance Expiration Date[/TD]
[TD]Secondary Ins Type[/TD]
[TD]Secondary Insurance Expiration Date[/TD]
[TD]Addressee Name[/TD]
[TD]Street Address[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD] Zip[/TD]
[TD] Email[/TD]
[TD]Phone[/TD]
[/TR]
[TR]
[TD] 12/1/16[/TD]
[TD]Postcard[/TD]
[TD]Expiring[/TD]
[TD]Company Name 1[/TD]
[TD]1/1/2017[/TD]
[TD]Workers Comp[/TD]
[TD]3/1/2017[/TD]
[TD]Company Name 1[/TD]
[TD]P.O. Box 1234[/TD]
[TD]My City[/TD]
[TD]ST[/TD]
[TD="align: right"]11234[/TD]
[TD] email@wmail.com[/TD]
[TD](888) 123-4567[/TD]
[/TR]
</tbody>[/TABLE]

 

[Krayons]

I have tried to re-construct your project from the data you provided. I'm not seeing where these "note" fields are, given what you've provided above.

I do see how you are pulling this data though. If I am correct, doesn't this leave you with a whole bunch of blank rows on your tracking sheet?

The easiest thing to do is just to send me the excel files you are working on (keep like, 3 rows of example data) and just replace any sensitive information with garbage information.

It depends on the type of question you are asking really. If its something like, "hey, im getting this error in my formula / vba code, why?" then someone can most likely answer that without knowing anything about your project. On the other hand, for questions such as yours, I would advise in the future just to attach the excel files as there are a number of things to take into consideration and too much to explain over a forum board. I find, and I'm sure most would agree, being able to visualize the problem goes a long way in solving it.

You can upload your excel files anywhere you'd like. OneDrive, Google Drive or Dropbox are always my go-to's. For something quick, I use TinyUpload.com - best file hosting solution, with no limits, totaly free

 

[KnightFhawker]

Thank you Krayons,

I definitely appreciate your feedback and suggestions, and apologize for the delay in responding and getting the Excel files uploaded. We are going through some software installation and updates at work which has prevented me from being able to spend any time on this project. I will hopefully have an opportunity to get the Excel files prepared and uploaded in the next day or so.

Thank you again for your help.

 

[KnightFhawker]

I have tried to re-construct your project from the data you provided. I'm not seeing where these "note" fields are, given what you've provided above.

I do see how you are pulling this data though. If I am correct, doesn't this leave you with a whole bunch of blank rows on your tracking sheet?

The easiest thing to do is just to send me the excel files you are working on (keep like, 3 rows of example data) and just replace any sensitive information with garbage information.

It depends on the type of question you are asking really. If its something like, "hey, im getting this error in my formula / vba code, why?" then someone can most likely answer that without knowing anything about your project. On the other hand, for questions such as yours, I would advise in the future just to attach the excel files as there are a number of things to take into consideration and too much to explain over a forum board. I find, and I'm sure most would agree, being able to visualize the problem goes a long way in solving it.

You can upload your excel files anywhere you'd like. OneDrive, Google Drive or Dropbox are always my go-to's. For something quick, I use TinyUpload.com - best file hosting solution, with no limits, totaly free

Here is the link to the Tracking Sheet ([Dynamic]Destination of data/[Static]User Input):

TinyUpload.com - best file hosting solution, with no limits, totaly free


Here is the link to the Source Workbook:

TinyUpload.com - best file hosting solution, with no limits, totaly free


Of course, you will most likely have to adjust the file links used in the Tracking Sheet to match the local Drive/folder(s) address you save the Source Workbook in.

If you have any questions, definitely do not hesitate to ask. Thanks again!!

 

[KnightFhawker]

Is there any chance anyone has an idea of whether this is possible, and if so how to accomplish it? It would really help me out quite a bit on 2 different projects actually. Thanks.