Automatic Calculation

California Kid

New Member
Joined
Mar 26, 2008
Messages
15
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
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:p></o:p>
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:p></o:p>
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,184
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

New Member
Joined
Mar 26, 2008
Messages
15
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
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.
 
Last edited:

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,184
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
... 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

New Member
Joined
Mar 26, 2008
Messages
15
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,184
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

New Member
Joined
Mar 26, 2008
Messages
15
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,184
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:
Code:
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
 

Forum statistics

Threads
1,081,798
Messages
5,361,366
Members
400,628
Latest member
teresajm

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top