+ Reply to Thread
Results 1 to 14 of 14

Lookup that looks down then across (dynamic rows)

  1. 08-28-2020, 08:57 AM #1
    MarkoGreenio
    MarkoGreenio is offline
    Forum Contributor
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    104

    Lookup that looks down then across (dynamic rows)

    Hi all,

    Please could someone help with a formula that would Lookup a value then looks down a table to find it then across (dynamic rows). The rows will retract and expand depending on the date/day of the month.

    For ease I have attached a sample worksheet with some representative sample data and some manually calculated results. The worksheet is GDPR checked and there is no sensitive data on there.

    Thank you for your help and as always very much appreciated.

    Mark
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-28-2020, 09:00 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,410

    Re: Lookup that looks down then across (dynamic rows)

    Are you still using Excel 2010, Mark? If not, please update your forum profile. Thanks.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 08-28-2020, 09:03 AM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,410

    Re: Lookup that looks down then across (dynamic rows)

    Are you open to suggestion regarding the layout of your data? This could be very easy with a couple of tweaks ...
    Reply With QuoteRegister To Reply
  4. 08-28-2020, 10:04 AM #4
    MarkoGreenio
    MarkoGreenio is offline
    Forum Contributor
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    104

    Re: Lookup that looks down then across (dynamic rows)

    Thanks Ali for the prompt and I have updated my profile, never realised I had been a member for so long.

    As for the other question then the answer is no I'm afraid - the original data is a direct dump from a WFM system that is notorious for its non-Excel friendly reporting. Normally I would use a Microsoft SQL report builder to make a more Excel friendly version but unable to do with this particular report. It's basically how the report dumps out, not great I know hence why its beyond my abilities.
    Last edited by MarkoGreenio; 08-28-2020 at 10:09 AM.
    Reply With QuoteRegister To Reply
  5. 08-28-2020, 10:19 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,410

    Re: Lookup that looks down then across (dynamic rows)

    That's a poor answer.

    Did you know that you do not need to be bound by data dump layouts? PowerQuery is your friend and can reshape the data for you to make what you want to do with it much easier.
    Reply With QuoteRegister To Reply
  6. 08-28-2020, 10:42 AM #6
    MarkoGreenio
    MarkoGreenio is offline
    Forum Contributor
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    104

    Re: Lookup that looks down then across (dynamic rows)

    Thanks Ali, I will take a look at some online videos and have a look wish me luck!!
    Reply With QuoteRegister To Reply
  7. 08-28-2020, 10:43 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,410

    Re: Lookup that looks down then across (dynamic rows)

    I can help if you get stuck.
    Reply With QuoteRegister To Reply
  8. 08-28-2020, 10:47 AM #8
    MarkoGreenio
    MarkoGreenio is offline
    Forum Contributor
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    104

    Re: Lookup that looks down then across (dynamic rows)

    Thank you that is very much appreciated have a great weekend in the meantime and enjoy the Bank Holiday, hope the weather is better for you down there than it is in Yorkshire.
    Reply With QuoteRegister To Reply
  9. 08-28-2020, 10:52 AM #9
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,410

    Re: Lookup that looks down then across (dynamic rows)

    It's mixed here at the moment - I want to get out and stain the garden furniture this weekend!!!
    Reply With QuoteRegister To Reply
  10. 09-01-2020, 10:43 AM #10
    MarkoGreenio
    MarkoGreenio is offline
    Forum Contributor
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    104

    Re: Lookup that looks down then across (dynamic rows)

    Hi Ali, hope you had a good weekend, I did indeed try your suggestion but PQ is probably something I need to invest time in. As a quick fix do you suggest I close down this thread and repost?

    I have sourced a few courses off the internet and we have resource within the company so hopefully I will be ok after a few hours training.

    Thank you

    Mark
    Reply With QuoteRegister To Reply
  11. 09-01-2020, 10:47 AM #11
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,410

    Re: Lookup that looks down then across (dynamic rows)

    No, you can't have duplicate threads. If you want a different solution, then just bumping this thread (which you've done) and saying so is fine. Someone else may have a suggestion for you.
    Reply With QuoteRegister To Reply
  12. 09-01-2020, 11:04 AM #12
    MarkoGreenio
    MarkoGreenio is offline
    Forum Contributor
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    104

    Re: Lookup that looks down then across (dynamic rows)

    Thanks Ali, appreciate your help.

    I have just added the original text below.

    Please could someone help with a formula that would Lookup a value then looks down a table to find it then across (dynamic rows). The rows will retract and expand depending on the date/day of the month.

    For ease I have attached a sample worksheet with some representative sample data and some manually calculated results. The worksheet is GDPR checked and there is no sensitive data on there.

    Thank you for your help and as always very much appreciated.

    Mark
    Reply With QuoteRegister To Reply
  13. 09-01-2020, 09:55 PM #13
    wk9128
    wk9128 is offline
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Taiwan
    MS-Off Ver
    365 V2403 and WPS V2022
    Posts
    3,380

    Re: Lookup that looks down then across (dynamic rows)

    In response to question #1, do you want to apply the yellow area and get the result? If yes

    All are array formulas, copy and paste down

    HTML Code: 
    Reply With QuoteRegister To Reply
  14. 09-02-2020, 04:26 AM #14
    MarkoGreenio
    MarkoGreenio is offline
    Forum Contributor
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    104

    Re: Lookup that looks down then across (dynamic rows)

    Thank you very much, that formula works perfectly well, I appreciate your help wk9128
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dynamic Arrays, Dynamic Drop down list, Filters and multiple rows
    By Big_Kev007 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2020, 06:35 AM
  2. [SOLVED] adjusting vba code to make it dynamic as rows data rows differ
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 11-26-2018, 10:37 PM
  3. [SOLVED] Clear dynamic named sheet, compare cells from 2 sheets, copy all matched rows to dynamic s
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 12-15-2015, 12:31 PM
  4. [SOLVED] 2 Way Lookup with a dynamic row
    By greenmonke in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-21-2014, 05:17 PM
  5. [SOLVED] Multiple Lookup Values rows and columns to lookup a single target column on the right end.
    By gurusanthanam in forum Excel General
    Replies: 3
    Last Post: 04-08-2014, 03:11 AM
  6. Lookup from fixed rows and dynamic columns
    By Alexvs in forum Excel General
    Replies: 2
    Last Post: 11-09-2010, 05:39 AM
  7. Fill in blank rows with 0's in a dynamic table, to always have same number of rows
    By Jadesky in forum Excel General
    Replies: 2
    Last Post: 05-29-2010, 03:28 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1