Ultra Stuck: If Then Function referencing ranges of cells from another worksheet

[Evildoer]

Attached is the workbook and it might be a tad easier to have it open while reading the question.

So this is an equipment inventory workbook. Each tab is a type of environmental sensing equipment that is present at either a field location or on the shelf as inventory. The last worksheet tab is entitled "11143000" which is a location in the field, and what I want to do is to use a function, or set of functions, that find the different equipment types in all of these other worksheets to compile data from ranges of rows into worksheet 11143000 (So one could simply click on the 11143000) worksheet to see what type of equipment is at that location...instead of sifting through the different equipment types to find what is there.

The first piece of equipment to reference is the Datalogger. What I need to do is to be able to have Excel reference Column D to locate if 11143000 is present worksheets 'H350XL', 'H500 & H500XL', or 'H522+', and if it is then copy the range of cells associated in that row to worksheet '11143000' (A5:G5). Of course the issue is a bit more complicated because 11143000 could possibly be listed under either 'H350XL', 'H500 & H500XL', or 'H522+'. In this case, if you click on the H522+ worksheet you will see under "Current Location" that there is an H522+ logger at 11143000. So I need Excel to locate 11143000 (D6), then copy the row associated with that referenced cell (in this case A6:G6) and copy it to worksheet 11143000 ((A5:G5).

I know that there is a lot of information here to sift through...but I'm just plain stuck here...

I'm not sure where to start, whether its an INDEX or an IF/THEN or a combination of both.

Any insight would be extremely helpful. If you need any more information let me know (if you need less...then sorry...)

Thanks in advance and for your possible willingness to help an obvious NOOB!

[nemo74]

Hi Evil,
What you are asking is not a small task. With out going fully into VBA, it will be a lot of forumlas and worksheet manipulation to get this how you are asking. Now, I'm not an full fledged expert on excel, but it wouldn't take me a fair amount of time to get the that H522+ listed under the 11133000 tab in the order you want it, but as far as auto updating, that is another story. Maybe one of the super users can help more, but for the reasons I mentioned, for now

I'm out.

[Evildoer]

Well thanks for at least reading the thread and for the micro-second consideration nemo! I wish I could drop this too! I might consider switching from Excel to Sharepoint based on more responses.

[nemo74]

Hi evil,
I played with your sheet for about 20min before I came to my conclusion. I would think this is why no one else has chimed in here. I wish I could help, but that is a lot to do.