Referencing Across sheets in a workbook

[BabyBandit82]

So I have a workbook I use to keep track of my offices workflow. I want to have one main sheet that shows the status for everyone so basically I want to accomplish this if sheet "test" C1 has an x then on sheet "A" put in text proof pending in a specified cell. Is this something I can do? If so what would the formula be to accomplish this.

Thanks

[jeffreybrown]

Hi BabyBandit82,

This sounds very easy and straight forward, however, a sample worksheet is always welcome.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[BabyBandit82]

So it won't let me upload even in the advanced options. I uploaded my anatomized file to google sheets at this link
https://docs.google.com/spreadsheets/d/1WwUmMBgFDDYfSJ7XwDaMuXo8O-tpWnVSK-_hrTDT0a0/edit?usp=sharing

[jeffreybrown]

Ok, sorry I'm not quite seeing it. Can you elaborate a little more on what you are trying to achieve?

if sheet "test" C1 has an x

I don't see any x on sheet test and what is the "specified cell"?

[BabyBandit82]

Sorry when creating my sample I reversed my sheets. So on sheet A you can see columns with "x" in them I want sheet test to show the header of that column (sheet A) if an x is marked in it. In the final there would actually be several companies I have updated the sample to reflect this.

Not so easy to explain I guess. But I want the furthest field marked X to read in the 1st sheet as you can now see on the updated sample. At first I thought IF statements but I don't really understand those well and don't think that is the correct way to approach this. My basic goal though it to have the cell act as "if sheet "A" D2 has an x ... then on sheet "Test" B2 put in text Proof Pending" referencing the furthest cell to the right in a range of cells to have an x in it.

Sheet A
Sheet A.PNG

Test
Test.PNG

[jeffreybrown]

In its simplest form as the Company names are in order, you could use...

Data Range

	A	B	C	D	E
1	Company Name	1st Column	2nd Column	3rd Column	4th Column
2	Testing ABC	x	x	x
3	Testing 123	x	x
4	ABC Company	x
5	123 Company	x	x	x	x

Data Range

	A	B	C
1	Company	Current Status
2	Testing ABC	3rd Column	$B$2 =INDEX('Sheet A'!$B$1:$E$1,COUNTIF('Sheet A'!B2:E2,"x"))
3	Testing 123	2nd Column	$B$3 =INDEX('Sheet A'!$B$1:$E$1,COUNTIF('Sheet A'!B3:E3,"x"))
4	ABC Company	1st Column	$B$4 =INDEX('Sheet A'!$B$1:$E$1,COUNTIF('Sheet A'!B4:E4,"x"))
5	123 Company	4th Column	$B$5 =INDEX('Sheet A'!$B$1:$E$1,COUNTIF('Sheet A'!B5:E5,"x"))

[BabyBandit82]

I am not sure I follow how would a count if statement get me a text result in the cell?

[jeffreybrown]

I'm not sure then I follow what you're asking. I thought post #6 is what you want. If not, please use post #6 and refine the requirements please?

[BabyBandit82]

Okay I have tried this on both my sample file and my actual file the result i get is "FALSE" is there a way to have it read the columns and then return the text in row 1 that matches the furthest marked x in row 2?

[jeffreybrown]

That is what it does unless I am just not getting what you are communicating. Look at post #6. What is not coming out right?

In row 2 the furthest "X" is in the 3rd column so the formula returns what is in row 1 (D1).

Can you post your sample workbook where you are getting FALSE and tell me what it should actually be?

[BabyBandit82]

Found my error and got it working for the most part. what happens if I skip a consecutive x. As you can see in the second one Test 123 it seems to break the system?

Sheet A
A.PNG

Test
Test.PNG

[jeffreybrown]

Try replacing the formula in B2 with...

=LOOKUP(2,1/('Sheet A'!$B2:$E2="x"),'Sheet A'!$B$1:$E$1)

[BabyBandit82]

Been playing with this for 2 days keep getting an N/A in return. I even watched several training videos on lookup functions. If you have any recommendations on free online training I would appreciate it.

I am sharing a link below that will allow you to edit on my sheet maybe you can help me figure it out.
https://docs.google.com/spreadsheets...it?usp=sharing

Here is what I am attempting to accomplish I think I have broken it down better.
1) Search Row 2 for x (absolute)
2) Return Result Column Headers in Row 1*
*But only reference the last x to the right ignoring blank cells, or cells with different data such as --- to reference it is N/A

Thanks for all your help on this.

[JohnTopley]

Formula from Jeff (post #12) delivers the required results:

[BabyBandit82]

When I put the formula in my workbook and adjust for sheet names and cells I get an error where it can't find value '2'