Use a cell name to look at a tab rather than typing in the tab name

GOOD EVENING.

THIS IS MY FIRST POST SO I HOPE THAT THIS IS EXPLAINED CLEARLY AND I HAVE POSTED CORRECTLY. MY CODE IS BELOW, THIS IS WORKING HOWEVER I WANT TO USE A CELL WHICH CONTAINS MORLEY RATHER THAN TYPING IN THE TAB NAME, I THINK I REQUIRE THE INDIRECT FUNCTION BUT NOT SURE AS I CANNOT MAKE IT WORK, CAN ANYONE HELP?

=IF(F2=0," ",IF(F2=0," ",IF(AND($F2>=Morley!$F$3,$F2<=Morley!$G$3),Morley!$H$3,IF(AND($F2>=Morley!$F$4,$F2<=Morley!$G$4),Morley!$H$4,IF(AND($F2>=Morley!$F$5,$F2<=Morley!$G$5),Morley!$H$5,IF(AND($F2>=Morley!$F$6,$F2<=Morley!$G$6),Morley!$H$6,IF(AND($F2>=Morley!$F$7,$F2<=Morley!$G$7),Morley!$H$7,IF(AND($F2>=Morley!$F$8,$F2<=Morley!$G$8),Morley!$H$8,IF(AND($F2>=Morley!$F$9,$F2<=Morley!$G$9),Morley!$H$9,IF(AND($F2>=Morley!$F$10,$F2<=Morley!$G$10),Morley!$H$10,IF(AND($F2>=Morley!$F$11,$F2<=Morley!$G$11),Morley!$H$11,IF(AND($F2>=Morley!$F$12,$F2<=Morley!$G$12),Morley!$H$12,IF(AND($F2>=Morley!$F$13,$F2<=Morley!$G$13),Morley!$H$13,IF(AND($F2>=Morley!$F$14,$F2<=Morley!$G$14),Morley!$H$14,IF(AND($F2>=Morley!$F$15,$F2<=Morley!$G$15),Morley!$H$15,IF(AND($F2>=Morley!$F$16,$F2<=Morley!$G$16),Morley!$H$16,IF(AND($F2>=Morley!$F$17,$F2<=Morley!$G$17),Morley!$H$17,IF(AND($F2>=Morley!$F$18,$F2<=Morley!$G$18),Morley!$H$18,IF(AND($F2>=Morley!$F$19,$F2<=Morley!$G$19),Morley!$H$19,IF(AND($F2>=Morley!$F$20,$F2<=Morley!$G$20),Morley!$H$20,("NO MATCH")))))))))))))))))))))

This looks to see if the value of F2 is less than, greater than or equal to the cells in the Morley tab, I have a number of different tabs therefore would like to reference the cell and *** &"!" rather than type the tab name each time.

Regards
Danny

I think you can simplify that formula considerably using an INDEX/MATCH combination.

Which cell are you likely to use to put the value Morley (or any other sheet name) into?

Pete

Hi Pete, Thanks for your reply, im not that good with these things and I needed something quick at the time but now need to change it and finding it difficult, the word "Morley" will be in column "C".

Regards
Danny

Hi Moo.

How do I upload it?

Reagrds
Danny

I have atached the excel sheet but taken out most of the data as it was far too big and confidential.

Regards
Danny

You can replace your massive formula in G2 with this one:

=IF(F2=0,"",IF(AND($F2>=Morley!$F$13,$F2<=Morley!$G$13),Morley!$H$13,IFERROR(VLOOKUP(F2,Morley!$F$3:$H$20,3),"NO MATCH")))

and then copy it down to get exactly the same results as your formula produces.

The reason for the part in red is to cope with the way your table is set up in the Morley sheet, where your second value in the Max column is less than the first. Is this correct? Will all other sheets follow a similar pattern in this table? If they vary, it will be more difficult to incorporate an INDIRECT function within the formula which will cope with those variations.

Hope this helps.

Pete

Hi Pete.

Thank you for the reply, the formula you gave does seem to work until I change the Morley sheet, If I change the max of a row and min of the next row so there is a gap between the quantities I would expect to see no match, I have uploaded the test so you can see it with your formula, as you can see it is giving the same result in G4 even though Morley does not have a value between 1184 (Data Tab F4).

The layout of all the tabs are the same.

To try and explain it better, I need the formula to look at column C to find the tab name, then look at F4 (data tab) to find the lookup value then return the value of column ‘H’ (within the specific tab) once it determines which row that value falls between and if it doesn’t then to return ‘no match’.

test.xlsm

I can't see what you have changed. The value in F4 (1184) lies between 0 and 2201 (row 13 on the Morley sheet), so the formula returns 1350-2200 from H13.

In order to test the formula out, I first copied your values in column G and used Paste Special | Values into column R. Then I put the formula in column G, with this formula in column S:

=R2=G2

It returns TRUE for all values, meaning that the formula returns exactly the same as your formula.

The issue with the table in Morley is that you have overlapping ranges - from 0 to 2201 in row 13 and from 1351 to 2000 in row 14 and from 2001 to 2601 in row 15. So, if your value is above 1351 and less than 2000 it falls into both the first range AND the second range, and if it were between 2001 and 2201 it would fall between both the first and the third ranges. Both formulae identify the first applicable range, but my formula could be simplified further (and made easier to cope with other sheets) if you did not have those overlapping ranges. Thus, G13 should be 1350 by my logic, and then the red part of my formula could be omitted.

Hope this helps.

Pete

Hi Pete sorry I uploaded the wrong test documenttest.xlsm

Okay, I can see what you have done now, and presumably you would want that entry to return "No Match"?

The way the formula is set up, it would assume there is a continuous range from an entry in column F of the Morley sheet and an entry in the next row. So, 0, 1200, 2001, 2602 etc. in column F would imply the ranges are 0 - 1199, 1200 - 2000, 2001 - 2601, and so on. Is this not the case?

Pete

Hi Pete.

Yes I want it to return no match in that case, the the formula needs to look if the value is between (Morley column F and column G) if it is return the tank name in column H, if it does not lie between any of the rows from F3-G20 then return 'NO MATCH'.

Regards
Danny

You've not answered the questions I posed in Post #8 and #12.

It would help to be able to see some other sheets like the Morley sheet so I can see how they vary.

Pete

Hi Pete.

I have done i again with more tabs to show you. I hope this is a better example.

#8 - The value in the max should never be less than the min

#12 - It should not assume the range is between them as some tanks cannot have certain values between them.

The attached Excel sheet shows what I would expect to see if there was a gap in the values.

I hope this explains it better, sorry for any confusion.

Regards
Danny

test.xlsm


Sorry I forgot to put the attachment in

test.xlsm

With attachment

Put this array* formula in G2 of the DATA sheet:

=IF(F2=0,"",IFERROR(INDEX(INDIRECT("'"&C2&"'!$H$3:$H$20"),MAX(1,MIN(IF(($F2>=INDIRECT("'"&C2&"'!$F$3:$F$20"))*($F2<=INDIRECT("'"&C2&"'!$G$3:$G$20")),ROW($H$1:$H$18))))),"NO MATCH"))

then copy down. Note that I have also put "NO MATCH" in the first row (H3) of the Morley table and the GlossC table in the attached file. I've also copied your original values to column X and put a comparison in column Y - these can be deleted if you want to.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>.

Hope this helps.

Pete

ef533_test.xlsm

P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Hi Pete.

Thank you so much, everytime I changed the tanks over I had to change all the data to say Morley!, you have saved me so much work.

Regards
Danny

Thank you for the advice on how to give feedback.

Regards
Danny