1 2 Previous Next 26 Replies Latest reply: Jun 1, 2012 2:35 PM by Jerrold Green1 Go to original post
• ###### 15. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
Level 1 (0 points)

As implied by Barry, I must  apologise for not initially getting the full implications of your earlier reply.

I think I understand what you are saying about formulas in cells and somewhat surprised by your intuitiveness at guessing what I might trying to do. You are a person of few words, I think.

I need to experiment some, like you say.

To a blind man a point in the right direction can be very helpful.

I would however be rather cautious about a general suggestion that “Spreadsheets” do not work that way. I see anomalies but no absolute reason why it cannot be done.  All things are possible.

Regards and thanks for your time,

• ###### 16. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
Level 1 (0 points)

Hi Barry,

Sure you don’t.  Like you say, if you are familiar with Numbers and in particular with the use of its INDIRECT function, then it would not be peculiar to you.

In my early days of programming, Indirect was a term used to describe a case where;  a Bit, Bite, Half word, Word , Double Word  etc.  to which the start address is placed in another ‘given’ address. ( ‘Indirectly referenced’).

In this context Apple iWord team have, I think, changed that to mean something different.

Again this could be a wrong observation in that their software may be doing just that.  However, It is not obvious to Numbers users. One cannot arbitrarily forget what has already been committed to memory, even though there are times when it would be an asset.

From a new Numbers users viewpoint (to me) it looks a bit like an afterthought to resolve a need discovered in earlier software.  This is not a criticism just a peculiarity, to me, because it doesn’t seem to doing anything like indirecting.  A more obvious use of indirect is the use you put  “H2” to in your last example;  =INDIRECT(CONCATENATE("C",H2)).

I have found that CONCATENATE will not allow a number to be coupled to a string. It converts the number to a string which is not then usable as a cell reference.

INDIRECT seems to correct this and seems to allow/convert numbers in a string to be read as numbers and thus used as a cell reference.  What is indirect about that ?   Like you say with continued use, (familiarity) I will discover how extensively it can be used.

Between yourself and Badunit it seems conclusive that in Numbers, a value cannot be pushed to an offset address.

Regards and thanks for your time,

• ###### 17. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
Level 6 (10,815 points)

2Skip2 wrote:

It seems conclusive that in Numbers, a value cannot be pushed to an offset addresss.

Same is true for all spreadsheets, or at least all spreadsheets I am aware of.  Each cell determines it's own value. Spreadsheet apps may differ, however, in how they handle the addition of new rows.

• ###### 18. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
Level 7 (28,995 points)

Skip,

Examine the following example of the use of the INDIRECT function and tell me how it violates your classical interpretation of the use of the indirect addressing mode.

Jerry

• ###### 19. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
Level 6 (13,620 points)

Skip,

In my early days of programming, Indirect was a term used to describe a case where;  a Bit, Bite, Half word, Word , Double Word  etc.  to which the start address is placed in another ‘given’ address. ( ‘Indirectly referenced’).

In this context Apple iWord team have, I think, changed that to mean something different.

I think you atempting to refer to a pointer.  A pointer is a modifier to a data type which add indirection-- that is is does not provide THE value it points to where the value is:

In my example above... memory location 1 contains a pointer to an unsigned char (an unsigned, 8-bit value).  Memory location 1 does NOT contain the value it contains the location (10) where the value actually resides.  so if you retrieve that value in location 10 you will get 243.

In this sense Apple has implemented the INDIRECT() function correctly.  You place a value in a cell which is NOT THE value but an indicator (no matter how assembled) as to where the value really is.

you can concatenate using the "&" operator instead of the CONCATENATE() function:

="Table 1 :: C"&row() will result in (assuming this formula is in row 4) the string ""Table 1 :: C4"

I hope this helps

• ###### 20. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
Level 1 (0 points)

Hi  Wayne,

I could be missing something again but;  It does aggree with my interpretation. As I pointed out to Barry with his use of H2 in the formula;    =INDIRECT(CONCATENATE("C",H2))    the H2 is what I recognise as an indirect. The same cannot be said if the H2 were replaced by a number.  It then, seemingly just becomes a means of  allowing the 2 in the concatenation to be read as C2  the 2 being a number.  If you leave it off you will get a sintax error because C2 is then a string.

I think, again this is a problem of fresh eyes as apposed to eyes used to using Numbers. There is no short cut.  I just need to get stuck in, which is what I am doing. If you "old eyes" (the royal "you") don't see that then you are making the same mistake as I did with Badunit s reply and was chastised by Barry.  I make no apology for that.  Before long I expect I'll be in the same vision tunnel as you oldies.

Regards,

• ###### 21. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
Level 7 (28,995 points)

Skip,

Something to keep in mind as you consider criticizing Apple's coding of functions is that they are striving to be compatible with a long line of spreadsheet ancestors, where function behaviors were designed well before there was a product called Numbers.

Jerry

• ###### 22. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
Level 1 (0 points)

Jerry,

Duly taken on board.

It is not necessary to defend Apple so.  I am already converted and commited to using Apple OS, warts and all.   I have a number of reasons for prefering it to Windows but I am not going to bury my head in the sand and defend to the hilt. Until recent times and to this day, there are, many indutrial programs that are unavailable for  Mac OSs. That IS the fault of Apples past strategy.   It has it's faults to.  As will any OS, otherwise there would be no need to upgrade year on year. That's, chasing technology and is now a recipe for bleeding money out of customers. Apple are very adapt at that to.

I agree they have gone some way towards compatability  but If what you say were strictly true they have really made a hash of it between Excel to Numbers. Which must be there main rival/aversary.

The primary reason for me getting into the nuts and bolts of Numbers so quickly and so deeply and, stiring the pot like I seem to have done, is because I've tried bringing some of my, fairly sophisticated, developed over several years, charts and graphs from Windows Excel into Numbers and it simply could not handle them or even come close. It literally got lost. I realised then, I was going to have to redesign them in and for Numbers or another Apple-Mac based spreadsheet. Not to be unexpected then, a steep learning curve is necessary. I did not expect to have to get into all this aside banter.  In spite of what you oldies seem to think I think, I think Numbers can do what I want. It is just a little irksome having to spend the time looking for new ways of doing what has to me been familiar.   As an aside, there are some things that, in my opinion, are better in Numbers. It is not necessary to bring those to the forum therfore only the down sides are encountered.  You oldies ( old Numbers users not age) don't have to engage in the defensive banter, I know my place.  While I am very grateful for any help given by anyone spending their time debating, exchanging views and opinions with other newbies can produce good results to. if I offend the elite it is not intentional. i'd sooner not to have to keep defending myself like this. I want and need to get up to speed with all haste so as not to have to keep wasting time switching back and forth between operating systems. I am not trying to prove anything.

Sorry about the rant. It is not aimed at you especially. I just seem to be getting deeper into opinions than getting solutions to problems.

I first had to defend myself after being advised/accused of not reading the relivant part of the manual which in the end said absolutly nothing about the query. That was followed by snipers like yourself.

I don't want to spend time like this and while I appreciated all the help that has been provided I sadly  think I'll have to seek help elsewhere.

Regards.

• ###### 23. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
Level 7 (28,995 points)

Skip,

You are certainly welcome in these discussions and we enjoy the technical challenges you can bring to the fore. I personally don't care for threads that waste time opining what might be, or what is wrong with what is. It is what it is and the volunteers here try to help with understanding it. It's clear that not everyone shares my feelings, as you can see if you peruse discussions about what's new in Lion and whether the changes are good or bad. Still, I'm here for the puzzle-solving and the satisfaction of helping a Numbers user to maximize the potential of their investment - the other stuff is a distraction.

Have a great day,

Jerry

• ###### 24. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
Level 7 (29,180 points)

What Jerry said.

My wording would be different, and probably more verbose, but the basic welcome and the statement would be pretty much the same.

Regards,

Barry

• ###### 25. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
Level 1 (0 points)

Hi Jerry,

Alas to time consuming a distraction for me at the moment.

I have no objection to constructive sword play in fact I find it rather fun.

Think I may have just gone crashing in blindly and naively expecting to find an atmospere of cheerful cooperation. I can only  blame myself, I am old enough to have known better. No criticism of anyone in particular but it is not in human nature. It only takes one.

I respect, appreciate and thank you very much, your note but I think I'll do what I should have done first; Sit back look in now and then and see how others make out.

You never no I might pick a few tips doing that.

Regards

• ###### 26. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
Level 7 (28,995 points)

2Skip2 wrote:

Hi Jerry,

Alas to time consuming a distraction for me at the moment.

I have no objection to constructive sword play in fact I find it rather fun.

Think I may have just gone crashing in blindly and naively expecting to find an atmospere of cheerful cooperation. I can only  blame myself, I am old enough to have known better. No criticism of anyone in particular but it is not in human nature. It only takes one.

I respect, appreciate and thank you very much, your note but I think I'll do what I should have done first; Sit back look in now and then and see how others make out.

You never no I might pick a few tips doing that.

Regards

I think that for the most part you got cheerful cooperation. If you have the time to stick around you will find that it's a friendly community. It's also a group that doesn't like to let a misconception just lay there without taking some exception to it. You may have read this as a contentiousness, but it's not.

Please take the time to read the terms of use of the Community. We try to conform to them. The short form of the required etiquette is here, with a link to the full TOU:

Hope to see you again soon.

Jerry

1 2 Previous Next