List names of other worksheets as data/text in a column of the "master worksheet"

Originally Posted by Tony Valko

See this for a formula method:
http://www.excelforum.com/tips-and-t...a-formula.html

Dear Tony, this worked like a charm, thank you so much!!!

Now, it turned out the next part wasn't as easy as I thought:

Originally Posted by ExcelHack3r

Once I can grab the names of the other worksheets, I can easily reference their data for displaying in the other columns of the master worksheet.

I want to pull in data from the other worksheets to the master worksheet using this generated list of worksheet names, so I need to input the value of another cell into the basic syntax for getting the value of another cell...

Let's say I have used your solution above to list the Worksheet names in Col A. For example, cell A5 contains the name for "Worksheet 4" since cell A1 contains the name of the master worksheet, according to your formula.

Now, I want to add the values of Col B in each subsequent Worksheet in Col B of the master worksheet, ditto for Col C, Col D etc.

The regular code for cell D4 that should display the value of cell D1 in Worksheet 4 would be

Please Login or Register  to view this content.

However, I am looking for a solution that inputs the name of Worksheet 4 from cell A5, in principle like this code, which obviously does not work at all:

Please Login or Register  to view this content.

So, how to do this??

Thank you so much in advance!

Originally Posted by ExcelHack3r

The regular code for cell D4 that should display the value of cell D1 in Worksheet 4 would be

Please Login or Register  to view this content.

However, I am looking for a solution that inputs the name of Worksheet 4 from cell A5, in principle like this code, which obviously does not work at all:

Please Login or Register  to view this content.

Note; if I just mark cell A5 and copy it into the function expression for cell D4, it works. So what I need is something that can emulate this - copying the contents of cell A5 as pure text, in order to input it as a variable in the expression for cell D4.

Originally Posted by Tony Valko

Try it like this...

=INDIRECT("'"&A5&"'!D1")

Sorry, I posted before refreshing to see that you had posted meanwhile.

Super, this does the trick!!!

Thank you :-) :-)

So, the next step for me is to use the worksheet name that you helped me extract in a nested expression.

What I am trying to achieve is to convert a US currency value from US format to European format, where the unit separator is a comma (",") instead of the US dot ("."). This is by the way also the reason why I can't use any of the code examples here directly, I always have to replace all the commas with semicolons in all function expressions...

The starting point is a US currency value on the following format, however the number format is "text", not "currency": $#.##

What I need to do is to remove the dollar sign and replace the , with . in order to process it as a number (in a cell with USD currency formatting, so a new dollar sign appears).

My approach with REPLACE causes additional problems because it takes positional input parameters and the currency values I work with have varying numbers of digits (1 or 2, all amounts are below $100).

Here's my attempt, which doesn't compute because apparently the " signs used by REPLACE crash with the input format of INDIRECT:

Please Login or Register  to view this content.

Originally Posted by Tony Valko

Ok, so A5 is the sheet name. Let's assume that name is Sheet10.

What's $C$2? Is that the cell you want to link to on Sheet10?

Actually it would be something like Sheet4, since the offset is only 1 according to the previous setup you gave me. But that is NOT important ;-)

And yes, you are correct sir - $C$2 is indeed the cell with the original text expression "$#.##" that I want to convert.

Originally Posted by Tony Valko

In the INDIRECT function what do the 2 and 5 mean? Those are invalid characters as far as I can see.

This is where the trouble starts, those numbers are input parameters for the MID function.

I took the INDIRECT function you gave me earlier:

Please Login or Register  to view this content.

and inserted a modified version of it in my working nested expression where I have to enter the Worksheet Name manually:

Please Login or Register  to view this content.

So it's the marriage of the INDIRECT with the nested function that doesn't work.

My take is that if you have a more elegant way than VALUE+REPLACE+MID to convert the US format to European format, then the INDIRECT function would probably be more compatible.

Thanks again!

PS: I'm not allowed to give you more reputation now, but I will next time I can do it (I have to give to some other people first).

Originally Posted by Tony Valko

Does C2 contain this literal string: $#.## ?

Or, does C2 contain a numeric value in the format $#.## ?

The last one, that is the format. 1 or 2 digits in front of the unit separator, i.e. amounts below 100 USD. And how to remove the dollar sign is also unsolved, RIGHT cannot do it unless you introduce an IF clause, and that seems like way overkill. There must be a search-replace thing that can simply delete the dollar sign and change "." to ","?

Originally Posted by Tony Valko

I don't know how to do what you want.

If Sheet4 C2 contains the number 7.25 we can get it to look like 7,25 but it won't be a numeric number, it'll be a TEXT string.

=SUBSTITUTE(INDIRECT("'"&A5&"'!C2"),".",",")

If you try to convert that TEXT string to a number then you'll get an error.

I have no experience with international number formats such as #,##.

Thanks again for the code!

The international number format simply works like this; the US number format is a text string to me just like the "7,25" is a text string to you. This is why I need to convert the text "$7.25" to "7,25" before it will be possible to change it to a number (with VALUE) and change the formatting to currency.

Have to figure out how this should work together with VALUE (or similar).
Your code from above didn't work right away, it should have returned something like "$7,25" I assume.

Originally Posted by Tony Valko

Try this...

=--SUBSTITUTE(SUBSTITUTE(INDIRECT("'"&A5&"'!C2"),"$",""),".",",")

If C2 contains the TEXT string $7.25 and 7,25 is a number format for your location then that should work.

The double unary minus -- does the same thing as the VALUE(...) function.

That did the trick - OH YEAH!

Thank you so much Tony, this was really awesome Excel hacking from your side! I just took the nick, but you got the savvy.

Now I'll have to save this in two versions, one for use in the US and one for use in Europe. I won't be able to test the US one, but it would be identical except for this formula I guess. Cheers and thank you one again !!!!