Forum Discussion
Formula Help
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
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.
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
