• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cell linkage

Busymanjohn

Busymanjohn

Member
Hi guys, I have a worksheet names FSM and a second one named FSM Paste, in FSM I have cell A1 the text FSM Paste, in the same worksheet I want a formula in cell B1 which refers to A1 and returns the value from the FSM Paste sheet and say cell B2 in that sheet. In other terms, in FSM sheet, cell B1 I want to have something like this =('A1',B2), I know it's not as simple as that, but can't get this to work. Andy ideas?
 
Good day Busymanjohn


My first reaction when reading was..what the "%*&..but I will have a stab what I think you mean and I will probably be wrong :(


In A1 you have the TEXT "FSM Paste" and you want B1 to refer to cell A1 and return the value in FRM Paste sheet, you refer to B2 in FSM Paste sheet is that where the data is that you want returning if so why not just have B1 in the FSM sheet just refer to the B2 in FRM Paste sheet, I am not sure what A1 does or why!!!!!
 
Hi bobhc, maybe I didn't explain correctly ,,,, I have multiple sheets in a workbook ,,, over 100 ( yeah I know, gross, but not my workbook, helping out a colleague here ). So, what I would like to be able to do is this ...... there is a worksheet named 'FSM Paste' and another called 'FSM' ,, the FSM Paste sheet contains the raw data, the FSM sheet is the one I want to populate ( remember there are mulitple sheets here ), if I put in cell A1 ( FSM sheet ) the text 'FSM Paste', using A1 as an anchorpoint for returning values from that same named sheet, in B1 I want to be able to say, "go to FSM Paste sheet and return the value in cell B2". Being able to reference the sheet name in a formula ( rather than manually entering each sheet name, because there are over 100 of them )would be less time consuming and easier for editing in future. Make more sense?
 
Hi Busymanjohn


I do not think a formula would be the best way to do what you want, a similar question was asked here perhaps SirJB7's VBA will help with the necessary adjustments for your needs.


http://chandoo.org/forums/topic/consolidate-copy-data-from-multiple-sheets-into-one-sheet
 
Hi ,


This is a very typical situation calling for the INDIRECT function.


Suppose the sheet tab labelled FSM has the text FSM Paste in cell A1.


Suppose the sheet tab labelled FSM Paste has the number 33 in cell B2.


Suppose in the sheet tab labelled FSM , you enter the following formula in cell B1 :


=INDIRECT("'"&A1&"'!"&"B2")


B1 will now display 33.


Is this what you wanted ?


Narayan
 
Good day NARAYANK991


I did not think of a one sheet formula as the OP states over 100 sheets and I was thinking the formula for all these sheets would be long and complicated, how would you construct a formula to fit 100 plus sheets?
 
Hi Bob ,


I was going by the following sentence :



Being able to reference the sheet name in a formula ( rather than manually entering each sheet name, because there are over 100 of them )would be less time consuming and easier for editing in future.




Narayan
 
You must log in or register to reply here.
Back
Top