Linking question

Hi, this is my first post but not my first visit. I have a couple of issues but I will start with just one..

I have a vbscript that will read a text file (html source of report that shows all items sold in a day) and writes the counts to cells in an .xls
I am tweaking my process which might not be the best. As of now it will add the day to a week.xls. So at the end of the week I have a book with 7 sheets, those sheets have daily counts.

Now I have another workbook that will link cells to corresponding days of the month as I copy these sheets to the workbook. The issue is, the sheets are always named the same. "sheet1 (2)" "sheet1 (3)" - "sheet1 (29)" with the main linked page in there linking to these sheets. Now when I put in a blank "main sheet" that has the formulas it's trying to update where the information is coming from but the sheets are there. If I press the "=" in the formula bar and leave the formula alone and press "ok" the value is calculated. I can also update links right back to the file I am in but that seems so futile. Why is it asking for the source when the sheets are right there? How can I get it to just calculate?

The xls is at work but reading another thread below I have to ask a question that might answer my question..

Would hlookup or vlookup work?

Thanks for your time.

I am sorry for being a jackass and saying this but can't someone point me in the right direction? Am I not being clear? Should I upload what I have for clarity? I just think it's most likely a simple solution and I see much more complex questions being answered. Again sorry for the bump.

Thanks Bryan. Heres my work so far. Put par.xls into 11-7.xls you will see the references pointing to sheets that are there. I could solve this by using the par workbook OUTSIDE of 11-7.xls and point it to this periods workbook (now 11-7.xls) but that isn't my goal.

Hi,

For any lookup() or match() function to work properly,
the very first rule is to have consistent references ...

For example Cell A3 in sheet Par MUST show
Steak Nachos
and not just Nachos ...
to find automatically row 119 in sheet2 ...

HTH
Carim

To explain a bit further, with the same example ...

Assuming cell A3 = Steak Nachos ...
copy following formula in cell C3 ...

Please Login or Register  to view this content.

Hope this clarifies

Carim

Originally Posted by Bryan Hessey

Hi,

to answer your query, three noted options.

=INDIRECT("'Sheet1 (26)'!A119")

=INDIRECT("'Sheet1 (26)'!A"&119)

or (to avoid the rubbish)

=IF(ISERROR(INDIRECT("'Sheet1 (26)'!A111")),"",INDIRECT("'Sheet1 (26)'!A111"))

or, perhaps my favoured option, when you create a specific sheet1() then create the formula in the required cells, however your creation/transfer code was not included so at what point you will need to establish, but along the lines of: variable = sheetnr

Cells(5,21).formula = "=INDIRECT(""'Sheet1 (" & sheetnr & ")'!A111""))"

(note, the IsError test not required, as the sheet is known to exist)

Let me know how you go
---

I went with option 3 and it seems to be exactly what I want. A complete workbook will have all days filled but I will have to wait till the end of the month/period. The average cell would have been rubbish without the complete month OR your third ISERROR option.

Thanks so much, I am extremely glad I found this site and how much easier it will be to make myself look good in work. You rock.

Thanks Carim also.