• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Way to link static info to dynamic data pulled from outside source?

ChrisR

New Member
Hello all.

I have searched over this excellent website for ideas to help me with my issue, but haven't found anything that might help. Perhaps I didn't look hard enough!

Here is the problem I'm trying to solve. Please see the attached example file.

The purpose of the spreadsheet is to let the user (any person in Group A, B, or C) know if they should raise cash in any account assigned to them. The actual spreadsheet has up to 3,000 rows of data, so it's very important to be able to sort the accounts and tell with a glance if any assigned accounts need action (based on column M).

The data is pulled and dumped into the "Data Imported Here" tab a few times a week. The row order changes all the time (information in row 4 might be found in row 632 the next time), which creates a problem I will describe later.

On the Cash Balances tab, I'm trying to display the data dumped into the second tab so it can be sorted by any user for their assigned accounts. Columns "A" - "I" all link directly to the Data sheet. Columns "J" - "M" are static and exist only on the first sheet.

Is there a way to link the information in columns J - M to each respective row so that when new data is pulled, the cash target information in J - M will still line up with the right account?

If the row order of the data isn't the same as the previous input data (which happens all the time), then the information in columns J through M won't be relevant to the "new" account and the recommendation in column M is useless.

Any recommendations on a layout for what I'm trying to accomplish? It's almost like I need a way to "Expand the selection" so the data in columns J - M is included when new data is dumped into the second tab.

Thank you! - Chris
 

Attachments

  • Example Cash Balances.xlsx
    17.4 KB · Views: 3
Chris

I would;

1. Use named Formula to access the Data table on the data Imported Here Sheet. This way it will automatically expand/shrink as the data changes

2. You will need a list of the Account Number on the Cash Balances Sheet

3. The 6 month target is directly related to the Account Number and is manual

4. All other fields are recovered from the data table using Index formula

see attached
 

Attachments

  • Example Cash Balances-Hui.xlsx
    17.1 KB · Views: 5
Thank you sir!

This is a more elegant way of using a Vlookup, correct?

Initially I had a static list of account numbers in Column A, and then used Vlookup to populate the rest of the fields from the Data Import tab. The index method is much more robust though, thank you for helping me with the formulas Hui, I appreciate it!

- Chris
 
Yes

VLookup can have issues especially in unsorted lists

This solution gives you ultimate flexibility
 
Back
Top