Why do I have to index every row from my source sheet on my new sheet?

Nancy Kahl

I am using Index to automatically create a line item on a secondary sheet. It only populates one field on the new sheet, then there are other formulas for the remaining fields. If I do not add every line from my source sheet to my new sheet, when the source sheet has any change to it will result in my new sheet dropping data.

This is a client list and I didn't want all clients on the new sheet. However, if I don't have all rows in the client list accounted for in my new sheet, my new sheet corrupts when a change is made in the client list. In testing through this, I had line 40 in my client list that was not mapped in my new sheet. When I resorted my client list, the client that was listed on line 40 in my new sheet dropped off of the new sheet.

L_123

can you post your source and destination sheets (with any proprietary/confidential information redacted) along with formula examples? I think I know what is happening, but I want to confirm instead of guessing.

Nancy Kahl

=INDEX({Project List_WFG Range 4}, 2)

Above is a screenshot of my destination sheet. The second column is where I use the INDEX (formula above) to automatically bring in the that column and create the line on this sheet. The other columns on the sheet are vlookups to the original sheet for the additional data. I have blank lines at the end of this sheet, with the formulas, such that when a new row is added to the original sheet, this one picks it up. The line that has the error in the first column is one that I deleted off. If I do that and do a resort on the original sheet, this sheet drops rows.. In addition, if I have rows that I just type in, with no links, a resort on the original will drop rows. Today, I have linked my "Other" rows to different sheets and so far, this one is not dropping lines any longer when the original source sheet is resorted.

Paul Newcome

What is the reason for pulling this data into a second sheet as opposed to a report?

Nancy Kahl

Because I need to get client counts by consultant. The primary sheet is my master project list and has a lot of data related to the project. This sheet is used to maintain the number of projects the consultants have. The reason I can't do a report is that I have to evaluate 6 fields, that represent the modules, that could have the consultant's name in it and filter that to 1 count. Any given consultant can work on multiple modules on a project, but when counting consultant clients, it only counts as 1 client. In addition, I have another sheet, similar to this where I keep backlog by consultant. Again, I have to filter down multiple fields so my numbers do not double or triple up.

Example: Karen is Lead, Payroll and TLM consultant for a given client - that is 1 client for Karen. But, there could be 3 people, 1 in each of those roles, in which case 3 consultants would each have a count of 1 client.

L_123

Just to verify, in the reference range, did you click and drag, or did you make a column reference by clicking the header?

Nancy Kahl

I'm not sure what you mean, but in the formulas to determine the consultants, it is a nested if/then statement with vlookups. All formulas on the sheet reference back to the Client ID column, which is the one that is indexed to the original sheet. This is the formula for one of the columns:

=IF(AND(VLOOKUP([Client ID]2, {Project List_WFG Range 1}, 1, false) = [Client ID]2, IF(OR(VLOOKUP([Client ID]2, {Project List_WFG Range 3}, 15, false) = "Angela Rachel", VLOOKUP([Client ID]2, {Project List_WFG Range 3}, 16, false) = "Angela Rachel", VLOOKUP([Client ID]2, {Project List_WFG Range 3}, 19, false) = "Angela Rachel", VLOOKUP([Client ID]2, {Project List_WFG Range 3}, 20, false) = "Angela Rachel", VLOOKUP([Client ID]2, {Project List_WFG Range 3}, 17, false) = "Angela Rachel", VLOOKUP([Client ID]2, {Project List_WFG Range 3}, 18, false) = "Angela Rachel"), "Angela", "") = "Angela"), "Angela", "")

L_123

for =INDEX({Project List_WFG Range 4}, 2)

more specifically:

{Project List_WFG Range 4}

check the reference and make sure the column header is selected, not individual cells. If you just highlighted the cells, then it is a static reference, and will move as rows are added and moved in the sheet. If you select the column header it is a dynamic reference to the whole column and your reference won't change.

Nancy Kahl

Ahh, that is on the column. It looks at that column, then it is linked to row 2, for the one I put in.

L_123

are you sure the sheet isn't just lagging? A dynamic reference to a changing index, then more sheet references against that can cause a significant slowdown, as quite a few formulas are dependent on others being updated prior to being updated themselves. Sometimes these types of sheets can take several minutes to finally be correct, and you can't close them until they are updated or the process will stop. Try leaving the sheet open for 5-10 minutes, refreshing occasionally, and see if it changes.

Nancy Kahl

I don't think so, as when it happened this time, I realized I had the same thing on my two sheets that track project costs. I changed those up and they are fine now. The other issue when referencing other sheets is that if you have a column that has no reference, it does not re--sort with the rest of the sheet, which is VERY annoying as well. What I am solving for with all of these sheets is that I need to parse project data in a lot of different ways and when we get new projects, I am trying to keep my input to a minimum. I was having to add every new client to 7 different sheets. I am down to 3 - if I can get this to behave!

Paul H

@Nancy Kahl

For you problem of having to manually add new clients Data Shuttle could help. You would be able to build sheets based of a master list of clients, when clients are added all the other sheets will be automatically update. Downside is its a Premium add-on with added cost.

Nancy Kahl

Thanks, I will have our management team look into that!

Andrée Starå

Hi @Nancy Kahl

I hope you're well and safe!

More info: 

https://www.smartsheet.com/marketplace/premium-apps/data-uploader

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Nancy Kahl

Thank you for the info! I am going to champion for this! :-)

Andrée Starå

@Nancy Kahl

Happy to help!