Help
I'm trying to figure out a way to be able to enter different values into a one cell on one worksheet and have this data be listed as individual items in a rows on a different sheet. Then the overall amount listed, be shown in the first worksheet in a different cell?
Any thoughts on how do do this?
Thanks!
Page 1 of 1
Enter a value into same cell and have it create a log on another sheet
#1
northernlad74 
- Newbie
-
- Group: New Members
- Posts: 1
- Joined: 02-February 12
Posted 02 February 2012 - 05:10 AM
#2
EGM
- Member
-
- Group: Members
- Posts: 79
- Joined: 05-January 05
Posted 05 February 2012 - 07:45 AM
I assume that (a) the values in the first worksheet have some sort of standard separator, like a comma or a space, and that you already know how to refer to a cell on one worksheet from another by using the name of the first worksheet followed by "!."
In that case, you need a function on the second worksheet. The function will be slightly different in the cell that picks up the first value and in the rest of them. To pick up the first value, first you have to find the separator in the original string with the FIND function. If your source string is "123 456," with space as a separator, =FIND(" ",[source cell reference]) will return 4. Save that in a cell; you'll need it later. Then, use that 4 to pick up the preceding characters of the string using the MID function: =MID([source cell reference],1,[wherever you put the 4]-1). That will give you the "123" part of your original string.
From there on down, you'll use similar functions, but they'll be a bit different. The FIND should start one character past where you found the previous separator, so you'll find the next one. (With this example, it would start at character 5 of the source string. That's why you saved the FIND result rather than embedding the FIND inside the third argument of the MID function.) The MID should start in the same place. If you arrange this all in a column, you can write the formula for the second value and replicate it down for as many values as you have.
This is not difficult if all your source strings will have the same number of individual values. If that number can vary, set up as many rows as you might possibly need, and use IF for a #VALUE error message from FIND to leave them blank if there aren't that many values this time.
If that was too sketchy, please post a follow-up question.
In that case, you need a function on the second worksheet. The function will be slightly different in the cell that picks up the first value and in the rest of them. To pick up the first value, first you have to find the separator in the original string with the FIND function. If your source string is "123 456," with space as a separator, =FIND(" ",[source cell reference]) will return 4. Save that in a cell; you'll need it later. Then, use that 4 to pick up the preceding characters of the string using the MID function: =MID([source cell reference],1,[wherever you put the 4]-1). That will give you the "123" part of your original string.
From there on down, you'll use similar functions, but they'll be a bit different. The FIND should start one character past where you found the previous separator, so you'll find the next one. (With this example, it would start at character 5 of the source string. That's why you saved the FIND result rather than embedding the FIND inside the third argument of the MID function.) The MID should start in the same place. If you arrange this all in a column, you can write the formula for the second value and replicate it down for as many values as you have.
This is not difficult if all your source strings will have the same number of individual values. If that number can vary, set up as many rows as you might possibly need, and use IF for a #VALUE error message from FIND to leave them blank if there aren't that many values this time.
If that was too sketchy, please post a follow-up question.
Share this topic:
Page 1 of 1