Using Nested If & right function across two work sheets

[John Louis]

Hi I'm new to this site & hope I'm posting correctly. I am using this formula it's working fine as long as the data I'm working with is on the same sheet =if(right(d2,1)="r",d2,d2) Now I need to look at a new sheet in my workbook to do the same thing, this new sheet has 1000s rows & columns. How do I use my formula & make sure it's looking at the correct row & column.

Thanks very much for any input.

 

[Scott Huish]

You specify the sheet name followed by a ! symbol. If your sheet name contains spaces you will need to put single quotes around the sheet name.

=IF(RIGHT(Sheet2!D2,1)="r",Sheet2!D2,Sheet1!D2)
=IF(RIGHT('Sheet 2'!D2,1)="r",'Sheet 2'!D2,'Sheet 1'!D2)

 

[John Louis]

Thanks Scott I tried your suggestions but no luck. The formula looks like this now. =if(right('published edp report'!cg:cg,1)="r",'pub edp report'!cg:cg,'edp tracker'!cg:cg)
By the way cg in this formula is the same info contained in d2 of my original post. Any ideas? Thank you.

 

[Scott Huish]

You're missing the row number in your formula. You can't do that formula with a range like that. What are you trying to accomplish exactly?
=if(right('published edp report'!cg1,1)="r",'pub edp report'!cg1,'edp tracker'!cg1)

 

[John Louis]

Hi, My second sheet (published edp report) has a value in column cg some of the values end with the letter R I want to look at that column & return the value if it ends with letter R to my first sheet(edp tracker) and if cell doesn't end with letter R return the value of the cell. I ran the formula again and see that my first sheet(edp tracker) does not contain values of column cg.(most likely why it's not working) edp tracker is where I'm trying to pull the info from edp report. Hope this clarifies. As I mentioned earlier this works fine as long as all the info is on the same sheet, as soon as I try pulling info in from a new sheet it fails.

 

[John Louis]

Good morning Scott & all, does my last update in this post on 8/30 help clarify the issue & what I'm trying to accomplish? I understand this is how you should follow up vs posting a new thread. Thank you very much John

 

[Scott Huish]

Hi, My second sheet (published edp report) has a value in column cg some of the values end with the letter R I want to look at that column & return the value if it ends with letter R to my first sheet(edp tracker) and if cell doesn't end with letter R return the value of the cell.

It sound like you want to return the value of the cell regardless which doesn't make sense to me, could you please clarify, maybe provide a sample and expected results.

 

[John Louis]

Hi Scott thanks for checking in and your response. Unfortunately I'm not sure how to add a sheet with an example to this forum. I will have to research a bit. With the formula in this thread what I'm doing is looking at a column and evaluating it & if it ends with the letter R I need the value of that cell returned, if it doesn't end with the letter R than simply return the value of the cell. I have a column in both my sheets that contains the same data set that can be used for the look up. Please let me know if this helps at all. I'm sure your frustrated by now. Thanks John

 

[Scott Huish]

Is it the same cell you are returning a value from? This is the part that is confusing me. If so, is what you are trying to is to see if something starts with a specific value?
So if you were looking up 12345. If 12345R or 12345 was in the table, would you want to return either one? Is that correct?

 

[John Louis]

Hi thanks again for your input. ok let me see if this helps. In column cg rows may contain b123, c123R I'm trying to get the formula to look at that column and return c123R if that value exist in the column if not than return b123. Thanks very much.