• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

using different lookup tables for males and females

C

cataholic

New Member
Hi everyone,

This is my first post and I should say that I have never had any excel training, I have just picked it up as I go along, primarily from reading this forum. Unfortunately this means thee are some pretty big gaps in my knowledge, so there are complex things I can do and simple ones I can't.

I am trying to create a spreadsheet that will automatically calculate hearing loss by age and sex and I have got the spreadsheet working perfectly for men, using the following code

=HLOOKUP(K7,'MTables'!C6:O9,IF(K8='MTables'!B7,2,IF(K8='MTables'!B8,3,4)))

MTables are values for men sorted by various factors. I also have similar tables for women called FTables

what I want to do now is add a cell where I input the sex and select the Table accordingly. I have tried doing it with IF statements but all of them result in a ridiculously long argument and don't work.

I am sure someone out there can see a simple way out of this and I am looking forward to slapping my forehead and saying 'D'OH!

Thanks in advance to everyone.
 
Welcome to the forums!

Try this formula:

=HLOOKUP(K7,INDIRECT(A2&"Tables!C6:O9"),IF(K8=INDIRECT(A2&"Tables!B7"),2,IF(K8=INDIRECT(A2&"Tables!B8"),3,4)))

Where all the A2 references in bold represent the target cell where you will simply enter M or F to toggle between the data sources.
 
Last edited:
eibi said:
Welcome to the forums!

Try this formula:

=HLOOKUP(K7,INDIRECT(A2&"Tables!C6:O9"),IF(K8=INDIRECT(A2&"Tables!B7"),2,IF(K8=INDIRECT(A2&"Tables!B8"),3,4)))

Where all the A2 references in bold represent the target cell where you will simply enter M or F to toggle between the data sources.
Click to expand...

Thanks Eibi-this just gives a REF error. I have googled INDIRECT and see how this works but don't see how the code distinguishes between MTables and FTables I will upload the spreadsheet in my answer to Somendra in the hope this will assist.

Many thanks
 
Somendra Misra said:
Hi,

Can you upload a sample file with some dummy data.

Regards,
Click to expand...
Hi Somendra,

Here is the sample file. I have created a dropdown for male & female, but as you can see, it only selects the male data at present.

Kind Regards,
 

Attachments

  • draft audio.xlsx
    57.7 KB · Views: 0
See this file, I had given the formula on Calculation sheet K9. If it is working as per your need try to put the formula with change ranges in other cells.

Regards,
 

Attachments

  • draft audio.xlsx
    59 KB · Views: 0
Somendra Misra said:
See this file, I had given the formula on Calculation sheet K9. If it is working as per your need try to put the formula with change ranges in other cells.

Regards,
Click to expand...
Hi Somendra.

Your formula works perfectly on K9. I will try putting it in other cells, but can you explain to me why the formula refers to 'Tables!' when the relevant tables are called MTables and FTables respectively? Until I understand this I cant see why your formula works.

Many thanks,
 
There is a LEFT function also which will give Either M or F from drop down selection. Which is than concatenated with Tables to make sheet name for ref.

Regards,
 
You must log in or register to reply here.
Back
Top