Forum Discussion

SElia622's avatar
SElia622
Copper Contributor
Jun 17, 2020

Formula Help

I am no excel expert but have learned a lot on my own by trial and error and reading help pages.  I have an excel sheet with multiple tabs.  One sheet serves as my roll-up for various totals throughout the workbook and so far everything is working perfectly.

I am trying to also insert information from one cell in each tab if the value of a series of cells equals specific text, across the whole workbook.

 

Basically what I'm trying to do is:

IF A12-A15 on all tabs in series="123", THEN Insert A6 from each tab

 

Is this doable?

Or do I have to make a formula 35 times for each tab in the workbook in separate cells?  That would kind of defeat the whole point in doing this roll up page if I have to manually enter all the data.

 

Formulas and Functions

4 Replies

Sort By
  • ExcelExciting's avatar
    ExcelExciting
    MVP
    Jun 17, 2020

    Hi SElia622,

     

    Please attached the sample data with manually displaying the results

     

    Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

    If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more

    • SElia622's avatar
      SElia622
      Copper Contributor
      Jun 18, 2020

      Hi ExcelExciting ,

       

      I can't upload the sheet because it's work related and private data.  The error I kept receiving was invalid formula.  I've never done IF/THEN formulas and know there must be symbols that I need to put in certain places for it to work properly.

       

      I have 92 tabs on this worksheet.  I want to be able to auto fill on my roll up sheet which jobs are on which invoice number.  In each tab I have the invoice numbers in cells A35-A46.  I'd like to have the job numbers appear in the designated cell on the roll-up sheet.

       

      So my thought would be:  If A35-A46 (from all tabs) = "Invoice 1234" Then put Data from cell A4 (which is the job number) in Cell C12.

       

      I may have answered my own question here, I'm assuming I can't put a series of numbers in one cell, I can only add them together?  (Like a sum of those numbers)?

       

      I may have to do this manually, which isn't really what I was hoping for.  

       

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jun 18, 2020

        SElia622 

        It's not necessary to upload your work file, you may generate small sample file to illustrate the question.

        If you'd like to find the sheet in which defined invoice number is in one of A35:A46 cells and return the value of the cell A4 from this sheet, formula could be

        =INDIRECT("'"&
   INDEX($A$20:$A$114,INT(
       (LEN(  LEFT(TEXTJOIN(",",0,Sheet2:Sheet94!A35:A46),  SEARCH(A1,TEXTJOIN(",",0,Sheet2:Sheet94!A35:A46))-1))-
        LEN(SUBSTITUTE(LEFT(TEXTJOIN(",",0,Sheet2:Sheet94!A35:A46),  SEARCH(A1,TEXTJOIN(",",0,Sheet2:Sheet94!A35:A46))-1),",","")))/12)+1)
  &"'!A4")

        assuming all sheet names after first are in cells A2:A114 of the first sheet and the invoice number is in cell A1 

         

Resources