Using Text in another cell to use as part of a formula

**Helpappreciated107** · 05-09-2016, 12:35 AM

Hi all,

This might have been answered in a previous thread but I can't seem to find it!

I am wanting to return a value in cell F33 of Sheet 1, Sheet 2, Sheet 3...... Sheet 40 (and future sheets added) to a Summary sheet. I have created a macro which will create a hyperlink in the summary sheet to any new sheet created with the name of the new sheet.

I want a formula like ='Sheet1'!F33 however I don't want to manually type each name of a new sheet as this will be time consuming. I would rather ='*hyperlink*!F33 so that the formula can be replicated multiple times.

Any help would be much appreciated!

Thanks team!!

**Kevin#** · 05-09-2016, 02:50 AM

EDIT
This is not what you wanted - got sidetracked when I saw the word "hyperlink"
The formula you requested is in post#4

Welcome to the forum.
It depends where you want to put the links. This is an example that may point you in the right direction

The attached worksheet contains a summary sheet followed by 5 other sheets
The macro counts the number of sheets in the workbook (=6) and creates links in cells A2 to A6 in the summary sheet to cell F33 in each other sheet.
In any workbook, sheets have the tab names ("Summary", "Sheet1" etc) but can also be referred to by their index number (here Sheets(1) to Sheet(6)). This macro uses their index number.
Sheet "Summary" is Sheets(1) - so we need links to Sheets(2) to Sheets(6)
The links are placed in cells A2 to A6 - so the row number of the cell containing the link is the same as the sheet index number.

{CTRL} + q runs the macro in the attached workbook

Please Login or Register  to view this content.

EDIT
This is not what you wanted - got sidetracked when I saw the word "hyperlink"
The formula you requested is in post#4

**David A Coop** · 05-09-2016, 02:54 AM

Welcome to the Forum Kelpappreciated,

If I understated your requirements correctly, and assuming you have the Sheet Name in A3 and following down the sheet, you could use a formula like this in, say B3: =INDIRECT(A3&"!F33")

This will give you the value from the named sheet.

I hope this helps, please let me know!

Regards,

David

- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

**Kevin#** · 05-10-2016, 01:29 AM

In post#2 I included a hyperlink to F33 in each sheet instead of the actual formula you requested!
This is the "formula" version

{CTRL} + q runs the macro in the attached workbook

The attached worksheet contains a summary sheet followed by 5 other sheets
The macro counts the number of sheets in the workbook (=6) and creates formula in cells A2 to A6 in the summary sheet
In any workbook, sheets have the tab names ("Summary", "Sheet1" etc) but can also be referred to by their index number (here Sheets(1) to Sheet(6)). This macro uses their index number.
Sheet "Summary" is Sheets(1) - so we need values (in F33) from Sheets(2) to Sheets(6)
(Values are placed in cells A2 to A6 - so the row number of the cell containing the formula is the same as the sheet index number)

Please Login or Register  to view this content.

Using Text in another cell to use as part of a formula

LinkBack

Thread Tools

Rate This Thread

Display

Using Text in another cell to use as part of a formula

Re: Using Text in another cell to use as part of a formula

Re: Using Text in another cell to use as part of a formula

Re: Using Text in another cell to use as part of a formula

Thread Information

Users Browsing this Thread

Similar Threads

Formula to move part of a value to a new cell - part 2

Formula to produce result based if part of text in one cell matches another

Formula to identify part of a text in cell from range of cells & insert adjacent cell text

[SOLVED] Excel 2007 : If a cell contains this part text, or this part text, etc.., return this value, or nothing

Formula in textbox linked to cell only shows part of the text

Formula to remove part of a text from a cell by lookin at a table

Insert cell text as part of formula?

Bookmarks

Bookmarks

Posting Permissions