Hopefully this is an easy one that is just not coming to my mind.
I am trying to find the max value and sheet name by looking up a reference cell across multiple sheets which may/may not contain that reference value
I'll try to write it as an example to help you picture the issue I have.
Lets say I have sheet1 called "Animals"
Now lets say I have 10 sheets one for each Farm
Sheet1 will have all the Animals in Column A eg:
Now each sheet for the Farm may or may not contain each of the animals eg:
Sheet2: Joes Farm contains the following:
Sheet3: Bobs Farm contains the following:
Sheet4: Jacks Farm contains the following:
So I'd like to enter a formula or VBA to have a result on sheet1 like this:
Thanks for your help in advance!