Links between worksheets

[WhoCanDo]

There doesn't seem to be a specific worksheet discussion page so can someone let me know how to =(another worksheet.cell reference) please.

Does Nemetschek have any help for worksheet formula/help?

[WhoCanDo]

The experimenting I've got it.

=('Worksheet Name':I16)

However, does Nemetschek have any help for worksheet formula/help?

[WhoCanDo]

Now this has caused a different problem.

 

On the first worksheet I have in cell C56, =(Product.Weight), which for 8 objects displays 9.38 kg correctly.

 

On the second worksheet I have =(FirstWkSht:C56) with the result 18.76 kg.

 

The link to the first worksheet is on a database defined "Item" line that is not the same database as C56 "Product" and has 2 items listed.

 

So, the weight on the first worksheet is correct, but when reflected on the second worksheet, the reflected value is multiplied by the record count on that line.

 

=(FirstWkSht:C56)/Count(R in ['Item']) doesn't work

 

Is there a solution to this?

 

[Pat Stanford]

Do you have multiple instances of FirstWkSht in the drawing? Possibly on a design layer and then also showing in a sheet layer viewport?

 

If I have two instances of a worksheet in a drawing and then create a database in a second worksheet with database criteria of All Objects, I then get two lines in the database. One for each worksheet instance.

 

Exactly what criteria are you using in the database in second worksheet. My guess is that you need to use more specific criteria so that multiple instances are excluded.

 

Another way to check this would be to remove any SUM icons temporarily from the database and see how may subrows you get. Then perhaps you can right click on the row headers and choose Select Item to see exactly what item is being reported in each sub row..

 

Write back if you need more help.

 

 

[WhoCanDo]

Hi Pat,

 

There are not instances of FirstWkSht on the drawing since it's a list of material to cut and therefore has a total length and total weight listed. I want the weight listed on the second worksheet but the format of the second worksheet means that the link is on the database line of a different database item.

 

So the first worksheet, line 56, is database criteria =DATABASE((R IN ['Kickplate'])) with the cell C56 containing =(Product.Weight) ..... the answer is 9.38 kg

The second worksheet, line 5, is database criteria =DATABASE((R IN ['A/Panel'])) with the cell G5 containing =('FirstWkSht':C56) ...... the link shows 18.76 kg

 

I tried to divide the link answer by COUNT(R IN ['A/Panel']) but that gave an answer of 2, however if I put =COUNT(R IN ['A/Panel']) in a "spreedsheet" cell (say E14) then divide the link answer by that spreedsheet cell, it does work. eg. =('FirstWkSht':C56)/E14

 

The thing is, I don't want a line of unrelated numbers showing up, and even though I could hide this line, I shouldn't have too.

 

Can you see a fault in my formulas?

 

[Pat Stanford]

I have an idea, but I don't think it is with your formulas, but more with how worksheet work.  My guess is that the database in the second worksheet is returning 2 items and those are being SUMerized into a single line. Your C56 formula is applying to both lines so the SUMerized total is 2 times what you are expecting.

 

Any chance you can share the file? Either here or in a direct message to me and I will take a look.

[WhoCanDo]

You are correct Pat,

 

So I have put a Sum at the top of the column with the link. It's correct now :-)

 

I didn't realise that a link would act the same as a criteria.

 

Thanks for that suggestion.

 

Regards

[rowbear97]

I too am working on leveraging the power of worksheets and reports and have, perhaps the most basic of questions. I am going to want to take the quantity of plants from my plant schedule and use a worksheet to calculate the canopy coverage each tree earns you a credit in area for that species. I want the worksheet 'canopy coverage' to update when I recalculate after I've added plants to the plan. My earlier attempts at this have failed. Would anyone be able to help?

[Pat Stanford]

I don't think there is a way to have it update automatically. As far as I know Worksheet must be manually recalculated in order to show the most current data.

 

I have not tested, but an old post I was looking for seemed to indicate that calling Recalculate from one worksheet actually recalculates all the worksheets.

[michaelk]

4 hours ago, Pat Stanford said:

I have not tested, but an old post I was looking for seemed to indicate that calling Recalculate from one worksheet actually recalculates all the worksheets.

 

I think that is correct.  At least it seems to be the default condition.  There may be an option to have it not update all worksheets.  

 

 

[rowbear97]

Thanks for the follow up. I did not phrase my question correctly. It is simply that when I try to connect the result of a report into a worksheet that has a function my entry seems to be incorrect (see attached screen grab). I want scientific name and the quantities from the plant schedule to propagate in the "tree canopy calculation" worksheet and then have the calculations done with ultimately the sum of the total. In this current version the cells have values that I enter manually and the calculations done by functions.

[Pat Stanford]

Can't do it the way you are trying. Database subrows can't be individually referenced between worksheets.  Since the data in worksheets is dynamic, it would give you a LOT of room for future problems.  In your example, what would happen if someone changed the sort order of the first worksheet? Cell C5.1 would now have different data than you want.

 

How many different types of trees do you usually have in a project? If it is a "reasonable" amount, you could probably do a manual tree schedule and pull from there. Or maybe you just need to manually edit the proposed conditions worksheet. You could use something like =count((S='Your Symbol Name')) or =count(('Plant'.'latin name'='Your plant name')) to get the quantities. I think if you copied and pasted the names from the database into Column A, your could even use a formula like =count(('Plant'.'latin name'=A3))

 

 

[rowbear97]

Thanks Pat. Your example is a good one yet I was hoping....