Forum Discussion

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 througho...

Formulas and Functions

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

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