0

I have a spreadsheet with a summary page containing a selection list for week and a selection list for the portfolio. The workbook has the portfolio data on separate tabs. What I require is data for specific weeks from different portfolio tabs to be brought into a cell in the summary sheet.

E.G. Total Budget for week 32 on Portfolio 3

Currently I can do a VLOOKUP to bring the data back based on the week being selected in the drop down, but I need to have the logic to also look at the correct data tab for the right portfolio data.

What I've tried below (that is not currently working) is:

=IF(C4="Portfolio 3",VLOOKUP(C3,'Portfolio 3'!D:E,2,IF(C4="Portfolio 4",VLOOKUP(C3,'Portfolio 4'!D:E,2,IF(C4="Portfolio 2",VLOOKUP(C3,'Portfolio 4'!D:E,2,FALSE))))))
6
  • Please share some sample data will help us to fix the issue..also what C3 contains, Week number or what? And one more are you looking for Total (Sum) of Week 32 for Portfolio 3 in all Sheets? Jul 10, 2019 at 11:33
  • Cont,,, if so then you have two Criteria,, 1. is Week Nu & 2nd is Portfolio 3,, write!! Jul 10, 2019 at 11:43
  • C3 contains the week number which is anything between 1-54. The second drop down contains 4 portfolios. E.G. Portfolio 1. I just want to return lookups of the values in the corresponding cell/s on the portfolio tabs, not looking for a total sum, just the current value of a single cell, there is only ever one row per week on the portfolio tabs.
    – Clare
    Jul 10, 2019 at 12:03
  • For example: Financial Week 40 Budget £40,000
    – Clare
    Jul 10, 2019 at 12:07
  • That should be 52 weeks not 54, couldn't seem to edit the comment
    – Clare
    Jul 10, 2019 at 12:34

2 Answers 2

0

This solution is based on Concatenation method, I've used in both sample Sheets 2 and 3.

enter image description here

How it works:

  • Enter Sheet's Name in Cell A41 & A42 of Sheet 1.

  • Write this Formula in Column B of both sample Sheets to Concatenate Values =C2&D2 & fill down.

  • Enter combined value in Cell C41 of Sheet 1.

  • Now enter this Array Formula in Cell D41 of Sheet 1, finish with Ctrl+Shift+Enter.

    {=VLOOKUP(C41,INDIRECT("'"&INDEX($A$41:$A42,MATCH(TRUE,COUNTIF(INDIRECT("'"&$A$41:$A42&"'!B2:E6"),C$41)>0,0))&"'!B2:E6"),4,0)}

N.B. As soon you change combination in Cell C41 you get new data.

enter image description here

Edited:

You may use this Formula also to pull data across multiple Sheets using more than one criteria.

=LOOKUP(9.999999999E+307,CHOOSE({1,2,3},0,LOOKUP(2,1/((Sheet2!$C$2:$C$6=B$10)*(Sheet2!$D$2:$D$6=B$11)),Sheet2!$E$2:$E$6),LOOKUP(2,1/((Sheet3!$C$2:$C$6=B$10)*(Sheet3!$D$2:$D$6=B$11)),Sheet3!$E$2:$E$62)))

enter image description here

Note:

  • Cell B10 and B11are criteria in Sheet 1.
  • LOOKUP(9.999999999E+307 is used to find the Largest value that is less than or equal to the lookup value.
  • Where Lookup(2,1/dataRange,,) calculates an array of #DIV/0!'s and 1's, and LOOKUP ignores errors and returns the value associated with the lats numeric element of an array.

Adjust Cell references in the Formula as needed.

4
  • Thank you, unfortunately it doesn't work for me. I can't concatenate the values. It's a very large set of data which makes things harder. The main sheet has drop down selection boxes which can't be concatenated, it's also not a small data range to select from like your example, we are talking thousands of records which will dynamically grow/change. I was hoping there was a way to do this without VBA but I can't see how to write an IF logic statement to just switch between tabs in the formula
    – Clare
    Jul 10, 2019 at 14:13
  • @Clare,, now check the edited part I've included new Formula is rather easier and while using this no need to work with Concatenation !! Jul 11, 2019 at 6:01
  • Thanks for your help, that also could work but I ended up trying again with the IF formula and came up with a working solution below. Thank you for your suggestions!
    – Clare
    Jul 11, 2019 at 8:18
  • @Clare,, what I've suggest you has been tested many times before I've posted so there is no question of not working!! May be you trying different than what has been posted as Question by you!! Jul 11, 2019 at 8:23
0

I found a more suitable solution to the problem that has now worked:

=IF(C4="Portfolio 3",SUMIF(Table5[Financial Week],C3,Table5[Portfolio Stock Value]),IF(C4="Portfolio 4",SUMIF(Table6[Financial Week],C3,Table6[Portfolio Stock Value]),IF(C4="Portfolio 2",SUMIF(Table3[Financial Week],C3,Table3[Portfolio Stock Value]),IF(C4="National",SUMIF(Table1[Financial Week],C3,Table1[National Stock Value]),IF(C4="NDC",SUMIF(Table2[Financial Week],C3,Table2[NDC Stock Value]))))))
5
  • Could you edit the post & include the Screen Shot !! Jul 11, 2019 at 8:24
  • I can't screenshot as I've had to remove any sensitive names and information. I'm not able to post a screenshot of anything due to the nature of the work.
    – Clare
    Jul 11, 2019 at 8:32
  • , then put some sample data not similar to original to justify your formula,, seems it may not pull correct data if get duplicate!! Jul 11, 2019 at 8:47
  • I'm afraid I don't understand your request. I have the formula tested and working.E.G. If I select week 30 for portfolio 3 and there are four values for that week spanning years 2016-2019 it sums those values and returns the total on my summary tab.
    – Clare
    Jul 11, 2019 at 9:58
  • ,, exactly and what you have posted as Question,, Looking for Value for particular Week!! And now you are getting the SUM?? Jul 12, 2019 at 7:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .