Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old October 18th 05, 08:12 AM
Rowan Drummond
 
Posts: n/a
Default How to insert a value in one cell into many cells

Hi Thomas

Glad I could help.

I have some books by John Walkenbach on excel programming but nothing on
worksheet functions. There are some good resources on the internet. Some
that spring to mind a
www.cpearson.com
www.contextures.com
www.xldynamic.com
www.j-walk.com

Good luck
Rowan

Thomas wrote:
Dear Rowan,

Do you have any references (book, etc) for me to learn this kind of stuff

Cheers

"Rowan Drummond" wrote:



  #12   Report Post  
Old October 18th 05, 08:46 AM
Max
 
Posts: n/a
Default How to insert a value in one cell into many cells

"Thomas" wrote:
Just try to figure out why the formula shows nothing
(blank cell) when it is copied to another cells
such as L1 or AA1 (it does not show any result)


Simply because MID(...) would return: "" (empty text)
as the start_num* would evaluate to a figure greater
than the length of text in A1

*start_num part:
... COLUMN()-1+LEN(SUBSTITUTE($A1," ","")+0)-9 ..

The "max" length of the value in A1 was given in the orig. post as 9 digits,
so the decomposition by the formula was across a max of 9 cols (from the
starting cell B1 copied across to J1)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


  #13   Report Post  
Old October 18th 05, 09:10 AM
Thomas
 
Posts: n/a
Default How to insert a value in one cell into many cells

So how to make it work on the other cell then ?

"Max" wrote:

"Thomas" wrote:
Just try to figure out why the formula shows nothing
(blank cell) when it is copied to another cells
such as L1 or AA1 (it does not show any result)


Simply because MID(...) would return: "" (empty text)
as the start_num* would evaluate to a figure greater
than the length of text in A1

*start_num part:
... COLUMN()-1+LEN(SUBSTITUTE($A1," ","")+0)-9 ..

The "max" length of the value in A1 was given in the orig. post as 9 digits,
so the decomposition by the formula was across a max of 9 cols (from the
starting cell B1 copied across to J1)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--



  #14   Report Post  
Old October 18th 05, 09:29 AM
Max
 
Posts: n/a
Default How to insert a value in one cell into many cells

"Thomas" wrote:
So how to make it work on the other cell then ?


Depending on where is your new source cell, and
where you want to decompose the digits,
the formula could be revised/adapted to suit ..

For example, if your "other" source cell is in say, K1,
and you want to decompose it within say, L1-T1

Put in L1, copy across 9 cols to T1:

=IF(ISERROR(MID(SUBSTITUTE($K1," ","")+0,COLUMNS($K$1:K1)
+LEN(SUBSTITUTE($K1," ","")+0)-9,1)),"",
MID(SUBSTITUTE($K1," ","")+0,COLUMNS($K$1:K1)
+LEN(SUBSTITUTE($K1," ","")+0)-9,1)+0)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


  #15   Report Post  
Old October 18th 05, 10:06 AM
Thomas
 
Posts: n/a
Default How to insert a value in one cell into many cells

Dear Max,

It works
Thank you very much indeed for your kindness help and time,
In addition, could you kindly advise for any references especially
book,websites, etc that it might help me to learn or mastering this kind of
matter.
It would be greatly appreciated for your advise and help

Regards,

Thomas


"Max" wrote:

"Thomas" wrote:
So how to make it work on the other cell then ?


Depending on where is your new source cell, and
where you want to decompose the digits,
the formula could be revised/adapted to suit ..

For example, if your "other" source cell is in say, K1,
and you want to decompose it within say, L1-T1

Put in L1, copy across 9 cols to T1:

=IF(ISERROR(MID(SUBSTITUTE($K1," ","")+0,COLUMNS($K$1:K1)
+LEN(SUBSTITUTE($K1," ","")+0)-9,1)),"",
MID(SUBSTITUTE($K1," ","")+0,COLUMNS($K$1:K1)
+LEN(SUBSTITUTE($K1," ","")+0)-9,1)+0)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--





  #16   Report Post  
Old October 18th 05, 10:44 AM
Max
 
Posts: n/a
Default How to insert a value in one cell into many cells

well.. here's a couple of links** you may want to check out ..
(Listing is certainly *not* exhaustive, and in no particular order .. )
**some are mentioned in Rowan's response to you

David McRitchie's .. :
http://www.mvps.org/dmcritchie/excel....htm#tutorials
http://www.mvps.org/dmcritchie/excel...tm#vbtutorials

Debra Dalgleish's:
http://www.contextures.com/tiptech.html
Her page: http://www.contextures.com/xlbooks.html
houses a remarkably comprehensive Excel book list
to suit every taste !

Bob Phillip's:
http://www.xldynamic.com/source/xld.UsingMenu.html

Chip Pearson's:
http://www.cpearson.com/excel/topic.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/index.html

John Walkenbach's:
http://j-walk.com/ss/excel/index.htm

Tushar Mehta's:
http://www.tushar-mehta.com/

Jon Peltier's:
http://www.geocities.com/jonpeltier/

Ron de Bruin's [Google search Add-In]:
http://www.rondebruin.nl/Google.htm

And .. don't forget these wonderful excel newsgroups <bg
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Thomas" wrote in message
...
Dear Max,

It works
Thank you very much indeed for your kindness help and time,
In addition, could you kindly advise for any references especially
book,websites, etc that it might help me to learn or mastering this kind

of
matter.
It would be greatly appreciated for your advise and help

Regards,

Thomas



  #17   Report Post  
Old October 18th 05, 10:56 AM
Max
 
Posts: n/a
Default How to insert a value in one cell into many cells

Oops ..

Bob Phillip's:


should read:

Bob Phillips':


--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


  #18   Report Post  
Old October 18th 05, 11:03 AM
Max
 
Posts: n/a
Default How to insert a value in one cell into many cells

and some updates / clarifications ..

Jon Peltier's new homepage is at:
http://peltiertech.com/

Ron de Bruin's homepage is at:
http://www.rondebruin.nl

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Cell Shading (based on the shading of other cells) Tubby Excel Worksheet Functions 2 June 20th 06 10:03 PM
copying cell names Al Excel Discussion (Misc queries) 12 August 11th 05 03:01 PM
Maintaining cell references when inserting cells elsewhere Stephen Jefferson Excel Discussion (Misc queries) 3 August 5th 05 09:30 PM
How do I make a cell equal to another cells value and not it's fo. TroutKing Excel Worksheet Functions 2 January 17th 05 06:15 PM
Convert data of cells to any type: Number, Date&Time, Text Kevin Excel Discussion (Misc queries) 0 December 30th 04 06:55 AM


All times are GMT +1. The time now is 02:51 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017