How to return a value from another sheet by referencing the sheet name.

[mpyrant]

I am trying to create a more robust budget and invoice tracking sheet.
I track contract values, changes and budgets on worksheet that are named by the financial accounting node they are associated with.
I have a column in my project budget sheet that needs to reflect the total value of each sheets contract values and $$ paid to date.

Is there a way to create a formula that will read something like this - If the Node in Column B equals a sheet name, return value from B37? If false, Zero.

I have tried =IF(ISNUMBER(SEARCH("1.06",sheetnames)),'1.06'!B35, 0) to no avail. Should this be an indirect formula?

See attached Sheet for reference. thanks so much for the help.

 

[vletm]

mpyrant​

Your writing has for my eyes something wild ...
If the Node in Column B equals a sheet name, return value from B37? If false, Zero.
I have tried =IF(ISNUMBER(SEARCH("1.06",sheetnames)),'1.06'!B35, 0) to no avail.
Where/what is Your ... sheetnames?
Where/what is Your 1.06?
Where/what is Your '1.06'!B35?
... Didn't You want B37 value from somewhere or how?
Where do You would like to use something?
... hmm?
If You'll write to Contract Budget's cell M11 =INDIRECT(B11 & "!B35")
... You could get 1300.00
You can copy that formula for other Initiations.
I modified other sheets B35-value that it's possible to verify other sheets values too.

 

[mpyrant]

Thanks so much for your help!

My sheet names would be the accounting NODE as indicated in column B.

I'll try to articulate a clearer scenario...

I would like column P to return a cell value from the sheet whose name that matches the Node in column B.

In other words, in column P of 1.01 Land Acquisition, I would like that cell formula to populate value G6 from sheet 1.01.

When I contract a new vendor, I will create a new sheet named by their accounting node. With this formula, I could avoid having to hyperlink every individual cell value.

I added some markups in this attachment.

 

[vletm]

mpyrant​

Did You test below?
If You'll write to Contract Budget's cell M11 =INDIRECT(B11 & "!B35")
Now You changed ... Your on the other words:
In other words, in column P of 1.01 Land Acquisition, I would like that cell formula to populate value G6 from sheet 1.01.
... something from G7 ... which are empty cells
Could You modify Yourself my green formula as You really would like to use?
... eg to ...hmm? to Contract Budget's cell P11 =INDIRECT(B11 & "!G6")

 

[mpyrant]

The above returned #REF with an invalid cell reference. so, I tried =INDIRECT(B12,'1.01'!G6), same error.

My attempt to write out your green formula below:

=INDIRECT(Reference Sheet Name which is derived from B11)('1.01'!D13) but that '1.01' Sheet in the return value should be determined by the indirect reference to column B...

Thanks again!

 

[vletm]

I ( mpyrant ) tried =INDIRECT(B12,'1.01'!G6), same error.​

... but I offered in cell M11 as below

... and to cell P11 as below (if You would like to avoid some errors)

Can You see differences?

 

[mpyrant]

I do! I think I got it. Thank you!!!

 

[mpyrant]

VLETM - one more question. Instead of returning #REF when there is no value in the specified cell, can I add to the formula "If False,0" so it shows Zero dollars instead of an error?

 

[vletm]

mpyrant​

Do You use that lower formula?
... which has IFERROR
... or what do You use?

 

[mpyrant]

I had tried to add the ,0 into the formula but it didn't read correctly. Are you saying I should incorporate, "

=INDIRECT(B12,'1.01'!G6),IFERROR(0)​

"

 

[vletm]

As given in #6 reply ...

Your ...
Excel has rules, how do it work.

 

[mpyrant]

you're a genius. Thanks for your help!

 

[mpyrant]

VLETM! I have another challenge!

I've attached a file where i'd like to apply conditional formatting to DUE DATES (Column F) IF there is no value in Column K.

That is to say that if column F is past due, I want the text in that cell red.
If the value in that cell is within three days of being due, I want the text in that cell orange.

Thanks so much! this is really fun with your guidance.

 

[vletm]

mpyrant​

You have skipped this challenge from:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Start a new post every time you ask a question, even if the theme is similar. The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.