Searching for a value over multiple rows/columns and returning the first cell in the row

I'm sure there's a stupid easy way to do what i'm trying to do, but I'm failing miserably so far. I'm relatively new to Excel, but I've been having a lot of success figuring out various function and formulas thanks to this forum and others. However, so far i'm stuck on this one.

Senario: (I've attached a demo workbook for reference)

I have two sheet in my workbook. The first sheet is a table of data which has various codes in column A, and circuit numbers in row 4. The second sheet is trying to fill cells with data from sheet 1. I need to search on a value specified in sheet 2, within the array of sheet 1. Assuming that value is located, I want to return the "Code" value from the first column of the row the value is found in. Secondly, the header of the array is a number, ranging from 1 to 6, I want to return that in a separate cell next to the first on sheet 2, separated by a cell with a dash in it. The end result being information being displayed as "Code-Number" to the user.

The final "issue" is that this same basic table, with different values may/may not be repeated on succeeding pages of the same sheet. I need to be able to search through and return data from however many pages of arrays exist.

If anyone needs more clarification, please let me know. I've attached a demo workbook to this post.

Thanks

Dave

It worked in the demo I sent you, but it did not work in my actual workbook. I've attached the actual workbook to this email. You'll see the blanks which need to be filled in with the heading "Circuit #" on the first sheet called "Patch by Universe". The array that the data pulls from is the sheet call "Distro". As you can see, I currently have two pages of arrays on that sheet, in theory there can be any number. Each array represents a piece of equipment, so each piece gets it's own page in the sheet. Realistically I wouldn't expect to ever have more than, say, 25-30 pages in this sheet.

For some reason when I put your code from above into this workbook, I get a response of #N/A, but it does work in the demo I posted earlier. Any help is very greatly appreciated.

thanks

Dave

Anyone able to help determine why the offered code doesn't work in my actual example?

That did fix the problem for the first formula cell, it is now successfully and accurately pulling in the values in the first cell of the row, thank you!!

I'm now trying to figure out how the second formula you sent works. While it is returning a value, most of the time, the values are only accurate 5 out of the 24 sets of data. The 'circuit #' at the top of the array is what should be returned, and while it is returning values between 1 and 6, they do not correspond to the columns the search value is found in. In four cases, it returns a #REF! when the values are present in the array, and in three cases it returned column numbers on search values which weren't in the array.

Unfortunately my Excel skills are lacking, so I don't really know how to trouble shoot this. I'll post up the revised file, perhaps it's a simple solution?

Thanks
Dave


Atomic_WB_Template1.xlsm

You are so close. Almost all of the 24 data sets in my workbook calculated correctly for both formulas now.

Two that aren't on the Distro sheet returned "Circuit #"s, while two returned "REF!".

The other weird thing is that now if I change the "Channel" (column A in PATCH by Universe), to a different number, or delete one, the whole sheet immediately throws reference errors. Additionally, if I try to add an additional line on that sheet, the DMX column doesn't calculate, and throws a #N/A, even if I use exactly the same data set as the line above, with only the "Channel" incrementing. It's almost as if all the data has been locked or something??

Perhaps I've moved beyond the scope of what I should be attempting with this. I'm not sure. The current version is attached.

I am extremely thankful for your help, ChemistB.


Dave

I'm getting close, and fixed my issues with my address incrementation, but the two formulas which you created for the circuit number columns are the current issue still.

The code I wrote which fills in my sheet "Distro" with info from the "PATCH by Universe" is working correctly. However, the search code you wrote which fills in the circuit info on the "PATCH by Universe" from the "Distro" sheet seems to only work with the original test channel numbers. If I make any change to those channel numbers on the "PATCH By Universe" the entire column for Circuit # immediately switches to #N/A for both portions.

The code for both columns is using whatever channel number is in the first cell of that row as it's search parameter, and each one is correctly incrementing that cell, so it isn't because they are all referencing the same cell. (near as I can tell)

I am able to add additional rows, and those do return the requested channel's circuit numbers accurately, but nothing can change otherwise, without screwing up the circuit cells on the "PATCH by Universe" sheet.

Finally, for some reason when I expand the range of the number of rows to search of the distro sheet, to allow for more pages on that sheet (how many pages depends on various factors), it also immediately throws the #N/A response down the whole column of the "PATCH by Universe" sheet.

Thanks again for the help!!

Current version E attached.

Bump to the top?

Ok, so your last set of formulas cleaned up the sheet in the rows below where I have information, which is great,

I'm still working on the reason it throws #REF! when I change the channel number. I started a new workbook, using the formulas you've given me, to make sure non of mine are screwing things up. I don't think there's a "problem", but perhaps something that needs to be accounted for. When All of the channel numbers listed in the "PbU" sheet are present in the "Distro" sheet, all plays perfectly (assuming no doubled numbers). When a channel number exists in the "PbU" but not in the "Distro" sheet, the "PbU" returns a circuit number of "0-5" which is fine with me, blank would be better, but 0-5 works for now. However, if I channel number exists in the "Distro" sheet, but NOT in the PbU sheet, the entire column (both halves) for Circuit # give a "#N/A" result. I'm assuming this is because my "Distro" sheet shows #N/A in the cells below that channel number because it was not able to located the channel number in the "PbU" and the presents of the "#N/A" is screwing up the search for all the channels.

Is there something different I should be doing to fill the two cells on the Distro sheet so that they don't throw the #N/A? Right now I have a "Fixture Type" which is a single space, and it has an associated name of a single space, and location of a single space, to keep everything pretty. Is there a better way to do this? (I'm sure there is) and would that correct the issue plaguing the circuit # formulas on the "PbU"??


Thanks!!!

New version Rev A attached.