• 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.

Dynamic Link between Sheets

WEBBERR

New Member
in the attached workbook there are a few different sheets, the first sheet is consolidation of all of the other sheets.

Some columns are missing from the sheets which feed into the consolidation sheets and some rows are too.

I am trying to build a function that I will use on the summary sheet to link to the correct cells on the other sheets.

eg. the function is to use sheets name contained in column A, and then generates a row number by matching the row that the item is on, and a column number by matching the header name, that the information is in, the function will return the value that is in the target cell.

if either the sheet, row or column are missing, I would like the function to return "S", "R" or "C" to identify which piece of information is missing. if there is no slave sheet with the same name the function should only return "S"

I was thinking that an offset from Cell A1 on each sheet would be able to generate the range that the information is in, if it is there, and return the value of what ever is in that cell.
 

Attachments

  • Dynamiclinkedcell.xlsx
    14.1 KB · Views: 7
Thanks for your reply Vletm, The sheet isn't really what I was looking for although it does has some similarities and I might be able to use some of it.

I am trying to write a function, that I can use in a cell formula, to return the value depending on which sheet the summary refers to and then which row and column the matching information is on in the source sheet.

I have 3 pieces of information to match.

I was thinking about starting with

Function "dynamiclink (ws as worksheet, item as string, type as string)"

where
WS is = to a cells contents (in column A)
Item = a string in a cell in (column B) this is a header column.
Type = a string in columns C,D,E,F,G etc this is a header row

I could put the information into a table but have not done so at the moment.

the data in the linked cells is numeric (financial)

I see your code uses Match, which is what I would have started with, I would try to match the row and the column, and use them to offset from A1, on the right sheet to get the information to the summary.
 
the file is showing the anticipated result on the first page. But the reason I would like it as a function is that I would like to use it to build into various formulas.

the "Indirect function" almost gives me what I need,

I can use it like .... =Indirect("'"$A6"@!&D69") which allows me to choose the sheet it refers to according to the contents of cell A6, but D$69 is always fixed in the example and I need it to change to E$69 when I drag the formula across the page and it doesn't because it is treated as text.

using code is fine in certain circumstances, but It does very specific things, and I would like to use the "Function" for several different purposes.

My stating point was something like

Function DyLink3(WS As String, Colref As String, Rowref As String) As String
TRnge As Range
Dim X As Range
Dim Y As Range
X = WorksheetFunction.Column(Colref)
Y = WorksheetFunction.Row(Rowref)
TRnge = Range("A1").Offset(X, Y)
DyLink3 = WorksheetFunction.indirect("'" & WS & "'!" & TRnge)
End Function

however I know that this is a long way from doing what I want it to.
 
Hi

I hope you can see the screen shot fully.

the upper left part is the sheet where the formula will go, it will hopefully refer to the target sheet /column and Row.


In this one the sheet is in column A, the column will vary as I drag the formula across the sheet, and the row will be fixed at 69. in this particular example
 

Attachments

  • screenshot.png
    screenshot.png
    92.3 KB · Views: 6
@WEBBERR ... hmm ... Where are all fruits?
For some reason, I think that screenshot belongs to that another workbook which we're working too.
hmm?
 
Both are just examples. I would like to use it in a few different sheets. which is the main reason I'm avoiding using a fixed method of coding.

The important thing is that I need the function to look at 3 components. all of which are contained in a summary sheet but refer to other location in the workbook.

the WB with fruits already display the "Manual answers" you asked me for so I tried again with another sheet to try to help your understanding.

I've tried using the indirect function but it fixes the row and column as they are inserted as text. and I need to have those elements relative too.

Maybe I am being too optimistic about the possibilities.
 
@WEBBERR You wrote in #1:
if either the sheet, row or column are missing, I would like the function to return "S", "R" or "C" to identify which piece of information is missing. if there is no slave sheet with the same name the function should only return "S".
You need the result, not just function!
Do You know, what kind of result You should/would like to get, from those 'fruits'?
 
Hi vletm

the attached screen shot shows what I am trying to achieve
 

Attachments

  • Apples results.png
    Apples results.png
    58 KB · Views: 3
@WEBBERR oookay!
I had that kind of view almost ready ... only colors missed.
I just left those 'only values' for white background.
Screen Shot 2015-12-01 at 18.42.18.png
These values are from You 'fruits-sample'.
Is this something that You are looking for?
 
Yes that looks good. I don't need the colours though, I just added this to make it easier to understand the notes.
 
@WEBBERR
Okay, I didn't add colors to the code.
How about those letters?
hmm? You try to find missing informations,
so You need letters and but numbers should write with lighter font.
 
yes the letters are to show me where information is missing and also to deal with errors, to prevent code bugging out.
the font colour /shade not important.
 
@WEBBERR ... ooo ...
I tested without colors ... and after that this looks like this.
Screen Shot 2015-12-01 at 23.51.52.png
I added summary part. ... do You need a copy?
 
@WEBBERR It's a sub with basic functions.
It works with sheets which name start with letter 'C' and the format of sheet should be same. Top - dates from C1 to right & 'fruits' from B2 to below.
If needs to work with another format of sheet, just adjust coordinates :)
1st it finds out all dates and fruits.
2nd copies values
3rd ... if xxxx then makes 'reply #11' ... if You want so
4th marks letters and colors
5th makes summary
Questions?
 

Attachments

  • Dynamiclinkedcell.xlsb
    39.4 KB · Views: 5
oh my goodness, there is a lot of work in that file. I'm going to need a while to get to grips with what it is doing. let alone understand it! o_O

In the mean time I want to thank you for the amount of time you have spent already I am very grateful for that.

it is doing the job, albeit somewhat differently to how I envisaged, and with a few rules/constraints. but it is doing the Maths and getting the correct answer. which I can't thank you enough for. :)
 
@WEBBERR
That is one way to do this ..
You can run it step by step and see what's going on.
If You can find 'an one formula' for You 'challenge'
... please let me know too.
If You need some details or so, please ask.
 
Back
Top