Using Text in 2 Fields in a Formula

Solved/Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

Those who read and attempt to think about the following, I definately do appreciate you taking your valuable time to help me out.

I want to calculate the time between today and another date, which is on another worksheet, in the same workbook. That worksheet has the same name as what is in columns A and B with a "-" between the two.

Ex:
A3 = "Company C"
B3 = "Type 3"
Sheet name = Company C-Type3
My formula is =NETWORKDAYS('Company C-Type 3'!F2,TODAY())

I would like to know how to replace Company C-Type 3 with the information from columns A and B, so that it will automatically be able to figure out what sheet to pull the information from. The date will always be in F2 on the referenced sheet.

I have tried the T function, the CONCATENATE function, and the & formulas and even the TEXT function. I can combine the two texts in another cell (by using =(A3&"-"&B3) or =CONCATENATE(A3,"-",B3)), just not within the formula. I keep getting a #REF returned.

Any assistance would be appreciated. I would like to handle this within the cells with a formula, and not in a macro, if possible.

Thank you,
Josh

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
use ADDRESS function to do that. Perhaps you may also need INDIRECT, but on surface I would say you need ADDRESS function
rizvisa:

I appreciate the assistance and have been trying a few things out with the ADDRESS and INDIRECT functions. Here is what I've come up with but still am hitting a road block.

"=NETWORKDAYS(ADDRESS(2,6,1,TRUE,H10),TODAY())"
H10 is "=CONCATENATE(A3,"-",B3)"

This gives me a #VALUE error. If I step through the calculations, I think there are a few quotation marks that get thrown in there from somewhere and I need to eliminate those I think.

Any additional help would be greatly appreciated.

Thank you,
Josh
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
try this

=NETWORKDAYS(INDIRECT(ADDRESS(2,6,4,1,A3 & "-" & B3)),TODAY())
Awesome! Thank you. Out of curiosity though, why wouldn't it let me do that without the ADDRESS function?

Thank you,
Josh
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
ADDRESS function basically allows you obtain a string that tells you an address of a cell in a worksheet.. So in your case this allow gave you a string "Company C-Type 3'!F2"

INDIRECT allows you to take that reference and give you reference specified by the address string. That converted it to a usable address ( kind of format has you typed in the location yourself)

Hope above two things kind of explained to you why you cannot directly used the string. You don't have to use address and create string yourself, but I find it very continent