-1

Using:

=IF(ISNUMBER(F8),CONCATENATE(INDIRECT("'"&A8&"'!"C"&$K$26")," ",INDIRECT("'"&A8&"'!"D"&$K$26")),"")

everything works, but the "D"&$K$26". The error is in "D".

pnuts
  • 58,317
  • 11
  • 87
  • 139
TerriH
  • 1
  • 1
  • Meant to say The error is in "D", but both get the error. – TerriH Sep 11 '15 at 17:52
  • Remove that double quote between the `!` and the `C`.. I'm not sure how that's working with the `D` one since it has the same double quote, but it shouldn't be there. – JNevill Sep 11 '15 at 18:00
  • Just to clarify (&A2&) refers to a cell that contains the sheet name, C and D are the explicit Columns and &$K$26 is the cell that contains the row number. – TerriH Sep 11 '15 at 18:03
  • After doing what JNevill, I only get a #REF! now. That's an improvement. – TerriH Sep 11 '15 at 18:05
  • To answer pnuts, I have a summary sheet that lists software, the version, days before a license expires. The other sheets are clients with all the information in them. I get the Software and version from the client sheet (001-NLC1). I know which row I want to the software (C) and version (D), but I need to know an ever changing row. I got the ever change row in a set location (K26). Does that help? – TerriH Sep 11 '15 at 18:10
  • I just looked at the error for the rep. A ref to a cell has changed. I will need to check that out before I get any more help. – TerriH Sep 11 '15 at 18:12
  • It seems to be referencing everything correctly, but the C and D on the client sheet. – TerriH Sep 11 '15 at 18:18
  • When I'm having a problem with a formula that uses `INDIRECT()` I take the bits out of `INDIRECT()` and stick it in it's own cell to make sure that it's actually a cell location that is being built through the concatenation. In your case, stick `="'"&A8&"'!"C"&$K$26"` and `="'"&A8&"'!"D"&$K$26"` in a new cell and see what's coming out of that. If that works, then stick `=INDIRECT()` around those in their own cells and see if it's erroring. It's a quick way to troubleshoot. – JNevill Sep 11 '15 at 19:06
  • Thank you very much for your help. I still got #REF!, but I decided instead to do the concatenating in the individual sheets so all I had to do was grab the cell in the client sheet and get the whole line I wanted. – TerriH Sep 14 '15 at 18:05

1 Answers1

0

This is correct syntax, but whether suited to your requirement rather depends upon what that may be: 

=IF(ISNUMBER(F8),CONCATENATE(INDIRECT("'"&A8&"'!C"&K26)," ",INDIRECT("'"&A8&"'!D"&K26)),"")
pnuts
  • 58,317
  • 11
  • 87
  • 139