Excel formulaqs and functions

Deecoy88 

Brian, glad to help

SergeiBaklan 

Thanks Sergei. Your increased specificity was very helpful and everything is now working. Thx again!

Brian

Deecoy88 

Brian, as for the second question

In formula both ranges shall start from row 39, you changed only one

=IFNA(VLOOKUP($A2,INDIRECT("'"&INDEX(SheetNames, MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetNames&"'!$A$39:$A$50"),$A2)>0,0))&"'!$A39:$B$50"),2,0),"")

if to highlight

=IFNA(VLOOKUP($A2,INDIRECT("'"&INDEX(SheetNames, MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetNames&"'!$A$39:$A$50"),$A2)>0,0))&"'!$A39:$B$50"),2,0),"")

Deecoy88 

Hi Brian,

First question.

- Add name of the another sheet to the bottom of the list

- on ribbon open Name Manager and change the range for SheetNames

in our case just remove latest 6 and add 7, press Close and Yes

With data in C1 as here

Master sheet will be updated accordingly, please see in attached file.

With second question bit later.

SergeiBaklan 

Hi Sergei,

Thanks for your help to date on this formula, but I still am unable to manipulate it.

First, I an unable to add a third sheet C1 with additional source data.  I added C1 to the reference range and am unable to pull the C1 source data over to the Master.  I have attached this file for your review and correction [Copy of Sample Worksheet 6.1]

What am I doing wrong?

Secondly, I took the same Copy of Sample worksheet file you sent me and shifted the egg data down to beginning in row 39 instead of row 5, in an effort to fully understand the rules of the formula, and was unable to get the data to the Master.  I've also attached this file [Copy of Sample Worksheet 5.2] for your to review and ask that you correct my formula so that it correctly pulls the data from A1 and B1.  I may not also be entering the array formula correctly in the Master or correctly adding new sheets to  SheetNames. ??????

Fully understanding the use of this formula is very critical to my Excel work, and I  will greatly appreciate your assistance at your earliest convenience.  Thx again.

Deecoy88 

You have only two sheets right now, you named range is

I didn't catch why do you add any formulas in sheets A1 and B1 - my guess there is source data only here.

With that formula in sheet Master works.

Deecoy88 

Hi Sergei,
I have dropped in your formula, set up Sheets and am getting REF error messages. I've attached the file. Would you be able to make the formula work on this sample file (Filling up a column in the master with the egg# and hen data from A! and A2, and then email it back to me? That will enable me to run with it and expand my analysis.
Many thanks,

Brian McCoy

SergeiBaklan 

Thx. I'll work on this.

Deecoy88 

To lookup on multiple sheets first create named range of all sheets names where to lookup. In attached file it's in sheet Sheets and named as SheetNames

Second, data in all sheets shall be within same ranges, in our case A5:B10.

Formula in Master sheet in cell E2 is

=IFNA(VLOOKUP($A2,INDIRECT("'"&INDEX(SheetNames, MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetNames&"'!$A$5:$A$10"),$A2)>0,0))&"'!$A5:$B$10"),2,0),"")

That is an array formula, i.e. use Ctrl+Shift+Enter to enter it instead of Enter. Drag down after that.

SergeiBaklan 

In what cell does the formula go?  It appears to go in the Master and pull values from A1 and B1, but I will have over 100 tabs like A1 and B1.  Is there a way to have the formula go in each A1, B1, etc and move the hen values to Master?  Thx.

Deecoy88 

That's exactly what the formula do:

Bit shorter formula if use VLOOKUP, but that's the same

=IFNA(IFNA(VLOOKUP(A2,'A1'!$A$5:$B$10,2,0),VLOOKUP(A2,'B1'!$A$5:$B$8,2,0)),"")

SergeiBaklan 

Thx, but the formula doesn't work in the B1 cell, and seems more complicated than necessary????

Again, I'm trying to transfer the values in the hen cells from A1 and B1 to the Master.  I've filled in the desired result in the Master (attached below)to assist you in understanding the objective, which receives all the hen data from A1 and B1.

 Hopefully this clarifies. Thx again!

Deecoy88 

I didn't catch the second part, there is no sub-file. If to pick-up Hen:s from the sheets into the Master that could be like

=IFNA(IF(ISNA(INDEX('A1'!$B$5:$B$10,MATCH(A2,'A1'!$A$5:$A$10,0))), INDEX('B1'!$B$5:$B$10,MATCH(A2,'B1'!$A$5:$A$8,0)),INDEX('A1'!$B$5:$B$10,MATCH(A2,'A1'!$A$5:$A$10,0))),"*no such*")

Deecoy88 

Hi Sergei,

Another challenge.   I am trying to come up with a formula (using INDEX/Match?) that pushes data from my tab files (in this case A1 and A2 attached), back to the Master file, and drops in the hen id based upon the egg # from the sub file and sends it back to the Master and drops the hen id into the proper cell based on egg #.  (Every egg # will have a unique hen id). If the hen id cell is " ", it returns a " " to the master.

Thanks again for your assistance.

SergeiBaklan 

OK...thx!