Previous Topic Next Topic

Using Text in 2 Fields in a Formula

Solved/Closed
Josh - Mar 27, 2012 at 11:28 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 30, 2012 at 04:59 PM
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

Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 28, 2012 at 07:16 PM
use ADDRESS function to do that. Perhaps you may also need INDIRECT, but on surface I would say you need ADDRESS function
Josh
Mar 30, 2012 at 09:02 AM
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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 30, 2012 at 12:22 PM
try this

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

Thank you,
Josh
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 30, 2012 at 04:59 PM
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

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response