Linking to Cells on Tabs Based on a List

[Gian624]

Good Afternoon Everyone,

A co-worker asked me a challenging question in Excel today. He has a listing of tab names written out in column B on a tab called "total order volume". He wants to return the value in cell C17 on each of the other tabs into column F on the "total order volume" tab based on the tab name in Column B. We tried writing a concatenate formul using the tab name, using a vlookup and a few others. Does anyone have experience doing this and has a solution?

Thanks,
Matt

 

[igold]

Hi Matt,

Just a couple of quick questions... What is the range or what row number in Column B does the list of tab names begin? Also what row do you want to start the returning the cell C17 values in column F on the "total order volume" tab?

igold

 

[Gian624]

Hi igold,

I need to return the first value in cell F11 on the "total order volume" tab. I run to F80. Same thing for Column B.

P.S. This file will be shared with some people that will prevent us from using a VBA solution.

Matt

 

[igold]

Matt,

I was going to provide a VBA solution. If you don't mind me asking, is the reason you cannot use VBA because when you share the file some VBA commands become unusable or is it a hardware/software compatibility issue.

igold

 

[My Aswer Is This]

Try this:
If you can use a Vba solution.
This script will put a list of your sheet names in column "B" of Sheet(1) which is the sheet on the far left of your tab bar. And will put the values from Range "C17" of every sheet other then Sheet(1) into column F of sheet(1).
If you need this modified let me know.

Sub Linking_to_Cells()
Application.ScreenUpdating = False
Dim i As Integer
Sheets(1).Activate
    For i = 2 To Sheets.Count
        Cells(i, 2).Value = Sheets(i).Name
        Cells(i, 6).Value = Sheets(i).Range("C17").Value

    Next
Application.ScreenUpdating = True

End Sub

 

[igold]

Hi My Answer Is This:

You may want to re-read the original post, you have not provided what he asked for...

igold

 

[Gian624]

Hello Again,

Thanks for the replies. My original suggestion to my co-worker was to use VBA. I have worked with it before and knew it was not a difficult thing for VBA. However, he specifically said that was not the direction he wanted to take which is why I sought an Excel formulaic solution. I appreciate the reply from "My Answer is This" because although I'm confident I could have figured out the coding myself, I'm sure it would have taken me much longer than it took you. I'll give it a try in the morning but I still need to know if it is possible without VBA.

Thanks again all,
Matt

 

[igold]

You guys better get on the same page. From how I interpret the code given by My Answer Is This, the code is going to take all the names of your existing tabs and overwrite the existing info in Column B on Sheets(1). The only way this will work given the specs in the original post, is if the workbook does not contain other worksheets with names that are not on your current list in column B Otherwise this code will overwrite your existing list in Column B with a new list based on every worksheet name in the workbook. Additionally all values are going to be written beginning on row 2 rather than row 11 as requested...

This line takes the current worksheet name and writes it to Sheets(1), Column B, Row(s) 2 and ending with the Row number that equals the number worksheets:

For i = 2 To Sheets.Count
        Cells(i, 2).Value = Sheets(i).Name

and the next line will write your C17 value in Column F with the same rows as above:

Cells(i, 6).Value = Sheets(i).Range("C17").Value

HTH

igold

 

[tonyyy]

In Cell F11, put in the formula:

=INDIRECT(CONCATENATE(B11,"!C17"))

Then drag/copy down to F80.

Cheers,

tonyyy