I would like to fill data in table on another worksheet, lets call it Sheet2 that starts with the activecell from Sheet1 and then use offsets. The activecell changes, but the remaining offsets for rows and cells always remain the same.
I am pretty sure , I can figure it out this out using VBA, but is there a formula way to do this?
Either looking at the activecell from Sheet1 as a start or using formatting like bold the start cell?
Thanks
Re: Active Cell As Reference
Hi whisperinghill,
I am not sure of your intention however, you may wish to explore dynamic range names on this link
http://www.ozgrid.com/Excel/DynamicRanges.htm
This is a very powerful method for when data interaction
Cheers,
GrahamB
Re: Active Cell As Reference
I'm not sure you can set up a function based on an activecell. Can you give an example please?
Re: Active Cell As Reference
Graham, thanks for the tip. I can surely use it. Though, the word dynamic is a little less dynamic than I want. I would like the dynamic cell reference to be the active cell on a specific sheet, The cell will change when I select a new starting point. The offsets will not change, thought the reference will change.
What I am trying to do, is create a dynamic genealogy/pedigree chart.
I can place the information for dogs mom/dad, grandmother/grandfather etc in layout, so that when I select a particular dog, it will filling the chart with the parentage back 3 or 5 generations, however I set the chart up.
I am positive I can use a variation of this macro that I found on the forum:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim C As Integer
C = Target.Row
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = 11 Then
With Sheets("Sheet1").Range("A" & C)
.Copy Destination:=Target.Offset(1, 0)
End With
End If
End SubI would just add a bunch more offset lines to the macro.
But seems I should be able to do this with a formula too. The dynamic name idea will surelly work for a particular reference, or I can rename the reference each time...
Ideas?
Re: Active Cell As Reference
What exactly do you mean by a "formula"? I think your event-based VBA route is the one to pursue.
Re: Active Cell As Reference
I sense a challenge…
How is the data arranged, is it like
Col a Col b Col c
Row 1 Child1 Mum1 Dad1
Row 2 Child2 Mum1 Dad2
Row 3 Child3 Mum2 Dad1
… … … …
Row 31432 Child 85 Mum16 Dad12
If it is this way, then the dynamic range is (put directly into the “insert/name/define”) and call it something like "totaldata"
=OFFSET(MySheet!$A$1,0,0,COUNTA(MySheet!$A:$A),3)
This sets up the dynamic range to count all entries as they are added/or deleted. If you eliminate or leave blank lines you will have to adjust the formula for that.
When you wish to select a child, you could use a drop down box using the dynamic range again but this time it would be and call it something like "kids"
=OFFSET(MySheet!$A$1,0,0,COUNTA(MySheet!$A:$A),1)
on the selection cell (where you select the kid for review), add your drop down box "data/validation/list" and put "=kids"
then to get the names of mum, dad, grandma, grand pa, offspring 1,2 and 3 etc you could use vlookups using "totaldata" as the source array and base the selection on the kid, then mum, then dad etc.
HTH
G
Re: Active Cell As Reference
Graham, Your approach would definitely work. Using a dropdownk, but then when I added offspring, I would end up adding another named range and another reference to the dropdown.
Arthur states VBA is probably the way to go, and it probably is and it is something, I can perform with the knowledge I have and the forum tips.
Just would be nice change to do something with formulas and use offsets. But since I didn't know how you could reference an active cell or say one with font bolded in a formula, it is very easy to do in VBA.
Searched excel help and no luck on formulas referencing what I want, probably not possible to reference font properties in a formula? like, bold or color, except to write the color, I believe.
Appreciate the replies
Re: Active Cell As Reference
Hi whisperinghill,
The function you maybe looking for is
CELL
Returns information about the formatting, location, or contents of the upper-left cell in a reference.
Syntax
CELL(info_type,reference)
Info_type is a text value that specifies what type of cell information you want. The following list shows the possible values of info_type and the corresponding results.
Reference is the cell that you want information about. If omitted, information specified in info_type is returned for the last cell that was changed. The following list describes the text values CELL returns when info_type is "format", and reference is a cell formatted with a built-in number format.
I copied the above from Excel help - so you can get from that info type settings.
My preference for your problem would be to do it with formulas as it speeds everything up and it means not having to do a macro action.
Hope this helps,
GB
Re: Active Cell As Reference
Yes, I could use a reference in an adjacent cell to where I want to start and then use the offsets to fill in the rest of the info. I'll give it a whirl.
Thanks for the tip
Re: Active Cell As Reference
Quote from whisperinghill;372688Display MoreI would like to fill data in table on another worksheet, lets call it Sheet2 that starts with the activecell from Sheet1 and then use offsets. The activecell changes, but the remaining offsets for rows and cells always remain the same.
I am pretty sure , I can figure it out this out using VBA, but is there a formula way to do this?
Either looking at the activecell from Sheet1 as a start or using formatting like bold the start cell?
Thanks
Best solution is use cell("address") thats it!
Don’t have an account yet? Register yourself now and be a part of our community!