Automatic Calculation

[California Kid]

I have 2007 and have set up what is likely more convoluted worksheet than necessary however I think I am close to achieving what I was hoping for and have run into a problem. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have a “data” worksheet with a table of information 1-100, I have separate worksheets also named 1,2,3,…100 that would hopefully pull information from the “data” worksheet based on their worksheet name (number) for the respective information in the “data” worksheet. I then am doing calculations with the worksheet name (number) to create work orders. I have this working for a single sheet but when I copied the worksheet (1) and made a worksheet (2) it seems to work for either sheet however if I move from the work sheet and change information on the “data” worksheet when I return to the numbered worksheet I see #ref error and need to hit f9 to recalculate and then the information is corrected. I have the sheets set to automatic recalculation however it does not seem to happen? I am confused. I pull in the work sheet # with the following =RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))+1<o></o>
I add the 1 to account for the row heading. I then pull the information from the “data” worksheet with =INDIRECT("data!a"&T1&"") to fill out the work order. I have some additional complications which I can describe once I have figured out this frustration. Appreciate your assistance. Kid<o></o>

 

[GlennUK]

When using CELL("filename") I always use the cell reference argument if the information is specific to the sheet where the formula is placed. Can you try that and see if it works.

 

[California Kid]

Thanks for the reply. I think I am using that as you indicated however I am parsing out the unwanted portion. Is there a function that simply gets the "worksheet" name into a cell? I may not be understanding what you are suggesting but would be happy to try anything. kid<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[xenou]

Do you need 100 tabs? If data is available from the data sheet, how about 1 tab that can take on the value of 1 to 100 as needed? This sounds like a lot of work/maintenance/duplication. Re the problem with Cell() it might not be so bad to stick the worksheet name in a cell, or create a worksheet-level named constant. Less resource intensive, and whatever the problem is it will also go away.

 

[GlennUK]

Thanks for the reply. I think I am using that as you indicated however I am parsing out the unwanted portion. Is there a function that simply gets the "worksheet" name into a cell? I may not be understanding what you are suggesting but would be happy to try anything. kid<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

... not in the example you gave. Doing it as I suggested it would look like this:
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1)))+1

 

[California Kid]

That seems to be working however I am still not quite clear on why my option was not. I will play with it today and let you know if I have issues. Question is why the referance to A1 in the formula?

Kid

 

[GlennUK]

That seems to be working however I am still not quite clear on why my option was not. I will play with it today and let you know if I have issues. Question is why the referance to A1 in the formula?

Kid

... it's because when you omit the reference the default is the last cell changed, and that may not even be in the required sheet, so I always specify any cell in the sheet where the formula is.

 

[California Kid]

I understand now. Thank you. So I need to also set these worksheets up to have two unique photos (before and after) site conditions. Do you have some thoughts on how I could import photos based on the data sheet which I would automatically add the photo #'s into the worksheets? The typical file name is DSC#####.jpg. Should I start another thread or do you have a thought? Thanks again for your help. Kid

 

[GlennUK]

What do you mean by "automatically add the photo"? You can write a macro to add a photo based on cell contents ( e.g. generate a string of the required filename based on cell contents ) ... the easiest way to make such a macro fire is to have some user action, such as pressing an embedded button. The macro code would be very simple, like:

Sub Button2_Click()
        Range("A45").Select
        ActiveSheet.Pictures.Insert( _
        "C:\Documents and Settings\Fred\My Documents\My Pictures\DSC" & Range("B2").Text & ".jpg"). _
        Select
End Sub