+ Reply to Thread
Results 1 to 14 of 14

Formula to auto populate cells to match multiple tabs with certain criteria

  1. #1
    Forum Contributor
    Join Date
    03-20-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    108

    Formula to auto populate cells to match multiple tabs with certain criteria

    I have attached a sample template.

    I would like the Executive Summary tab to auto populate information from tabs 1-3 based on the criteria that if column ''D" says 'Submitted'.

    I am not sure how hard this is or if it is possible. Any help or suggestions would be greatly appreciated.

    THANK YOU
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Formula to auto populate cells to match multiple tabs with certain criteria

    See attached file. I've kept it as a .xls file, as I see from your profile that you are using XL2003. I've also ensured that the formulae do not use any functions which were introduced in later versions.

    Hope this helps.

    Pete
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Formula to auto populate cells to match multiple tabs with certain criteria

    Pl see file.
    Attached Files Attached Files

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Formula to auto populate cells to match multiple tabs with certain criteria

    @ kvsrinivasamurthy

    btw,
    you forgot to say she cannot change any character in title row or it should be changed exactly in all titles in all sheets.

    regards
    sandy

  5. #5
    Forum Contributor
    Join Date
    03-20-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: Formula to auto populate cells to match multiple tabs with certain criteria

    Quote Originally Posted by Pete_UK View Post
    See attached file. I've kept it as a .xls file, as I see from your profile that you are using XL2003. I've also ensured that the formulae do not use any functions which were introduced in later versions.

    Hope this helps.

    Pete
    Quote Originally Posted by kvsrinivasamurthy View Post
    Pl see file.
    Thank You so much for your help. It works great in the Executive Summary Sample I provided, but when I apply both samples to my actual document, I don't get the same results. I get #REF and errors. I have attached the actual document I am working on, maybe you can help me with that.

    Thanks again!
    Attached Files Attached Files

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Formula to auto populate cells to match multiple tabs with certain criteria

    I've modified the table in columns M and N, to take account of zero entries in the sheets, and checked/amended the formulae in B and C - seems to work okay now.

    Hope this helps.

    Pete
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    03-20-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: Formula to auto populate cells to match multiple tabs with certain criteria

    Quote Originally Posted by Pete_UK View Post
    I've modified the table in columns M and N, to take account of zero entries in the sheets, and checked/amended the formulae in B and C - seems to work okay now.

    Hope this helps.

    Pete
    Thank you so much Pete for taking the time to help me on this.

    Column N doesn't seem to be accurate. The information (Log No., Program, Funding Package Title, etc.) are not grabbing from the appropriate cells and when I try to correct that, it doesn't work.

    Also, when I add new information, or change cell to 'Submitted', it does not capture in the executive summary.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Formula to auto populate cells to match multiple tabs with certain criteria

    I've just spotted that in cell A7 of the subsidiary sheets you have omitted a formula, and put the sheet name in there instead. The formulae in these helper columns set up a unique consecutive number, and these need to flow from one sheet to the next. It doesn't matter in the first sheet (or you could have zero in there), but on the BPCO sheet you need this formula in A7:

    =MAX('AIDS & HEP C'!A:A)

    i.e. it is looking back at the previous sheet. Similarly in the HFMHCS sheet, you need this formula in A7:

    =MAX(BPCO!A:A)

    and in the final sheet (SPALU):

    =MAX(HFMHCS!A:A)

    See if those changes improve matters.

    Hope this helps.

    Pete

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Formula to auto populate cells to match multiple tabs with certain criteria

    As for not getting the correct data, you need to change the columns in the formulae in the cells D7 to J7. In D7 you have this formula:

    =IF(OR($B7="-",$C7=""),"",INDEX(INDIRECT("'"&$C7&"'!b:b"),$B7))

    which is bringing data from column B (highlighted in red above). You have different columns in this file, so you need to amend where you get the data from. You actually want to get the data from column D for this field, so you need to change b:b to d:d above. You will need to make similar changes to the other formulae on that row, then copy the formulae down.

    Hope this helps.

    Pete

  10. #10
    Forum Contributor
    Join Date
    03-20-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: Formula to auto populate cells to match multiple tabs with certain criteria

    Quote Originally Posted by Pete_UK View Post
    As for not getting the correct data, you need to change the columns in the formulae in the cells D7 to J7. In D7 you have this formula:

    =IF(OR($B7="-",$C7=""),"",INDEX(INDIRECT("'"&$C7&"'!b:b"),$B7))

    which is bringing data from column B (highlighted in red above). You have different columns in this file, so you need to amend where you get the data from. You actually want to get the data from column D for this field, so you need to change b:b to d:d above. You will need to make similar changes to the other formulae on that row, then copy the formulae down.

    Hope this helps.

    Pete
    I have made all your suggested changes, but column N is still not working properly for example HFMHCS should be '0' and not '1' and it still is not picking up other submitted cells. I have attached an updated version.

    Can you please take a look?
    Attached Files Attached Files

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Formula to auto populate cells to match multiple tabs with certain criteria

    Change the formula in N2 of the Summary sheet to this:

    =IF(AND(MAX(INDIRECT("'"&M2&"'!a:a"))=0,SUM(N1:N1)=0),0,MAX(INDIRECT("'"&M2&"'!a:a")))

    and copy down to N5. Also, I see that in the SPALU sheet you have put the starting value for the sheet in cell A8, so put this formula in A7:

    =MAX(HFMHCS!A:A)

    and this one in A8:

    =IF(E8="Submitted",MAX(A$7:A7)+1,"-")

    That should now give you three records in the Summary sheet, one from the BCPO sheet and two from the SPALU sheet.

    Hope this helps.

    Pete

    EDIT: Note that column N is giving a cumulative count of the records, not the records on each sheet that meet the criteria.

    Note also that you will have to copy the row of formulae down from row 7 to row 57 on the Summary sheet

    Pete
    Last edited by Pete_UK; 03-18-2015 at 03:22 PM.

  12. #12
    Forum Contributor
    Join Date
    03-20-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: Formula to auto populate cells to match multiple tabs with certain criteria

    Quote Originally Posted by pete_uk View Post
    change the formula in n2 of the summary sheet to this:

    =if(and(max(indirect("'"&m2&"'!a:a"))=0,sum(n1:n1)=0),0,max(indirect("'"&m2&"'!a:a")))

    and copy down to n5. Also, i see that in the spalu sheet you have put the starting value for the sheet in cell a8, so put this formula in a7:

    =max(hfmhcs!a:a)

    and this one in a8:

    =if(e8="submitted",max(a$7:a7)+1,"-")

    that should now give you three records in the summary sheet, one from the bcpo sheet and two from the spalu sheet.

    Hope this helps.

    Pete

    edit: Note that column n is giving a cumulative count of the records, not the records on each sheet that meet the criteria.

    Note also that you will have to copy the row of formulae down from row 7 to row 57 on the summary sheet

    pete
    i got it to finally work! Thank you so much...you are a life saver!

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Formula to auto populate cells to match multiple tabs with certain criteria

    Glad to hear that we finally got there in the end. It helps to post a replica of your real file with dummy data, then you don't have to mess about changing cell and range references.

    I just spotted a slight error on my part - the formula in N2 should be:

    =IF(AND(MAX(INDIRECT("'"&M2&"'!a:a"))=0,SUM(N$1:N1)=0),0,MAX(INDIRECT("'"&M2&"'!a:a")))

    (i.e. it needs a $ symbol in the first N1 - shown in red), and then you can copy down.

    Thanks for the rep, and for marking the thread as SOLVED.

    Pete

  14. #14
    Forum Contributor
    Join Date
    03-20-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: Formula to auto populate cells to match multiple tabs with certain criteria

    Quote Originally Posted by Pete_UK View Post
    Glad to hear that we finally got there in the end. It helps to post a replica of your real file with dummy data, then you don't have to mess about changing cell and range references.

    I just spotted a slight error on my part - the formula in N2 should be:

    =IF(AND(MAX(INDIRECT("'"&M2&"'!a:a"))=0,SUM(N$1:N1)=0),0,MAX(INDIRECT("'"&M2&"'!a:a")))

    (i.e. it needs a $ symbol in the first N1 - shown in red), and then you can copy down.

    Thanks for the rep, and for marking the thread as SOLVED.

    Pete
    The real file had was too large to upload so I had to simplify it. I thought I could manage and figure it out, but I guess not. Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. auto populate between tabs
    By smatt26 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-10-2013, 12:48 AM
  2. Auto populate multiple cells with a value entered in one Cell
    By Excelrookie_1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-22-2012, 07:18 AM
  3. Replies: 1
    Last Post: 02-29-2012, 02:23 PM
  4. Auto Populate to Multiple Tabs Macro?
    By Desert Jay in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-30-2011, 11:59 AM
  5. Populate cells using multiple criteria
    By petrocaravi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-20-2008, 01:54 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1