| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
link data
I don't know if this is possible but what I want to do is link cells based on
if another cells is marked. Example: Have a sheet called Dinners, on that sheet you have the persons name and then columns for certain dinners. Then I have seperate sheets for each dinner. I want to automatically transfer the name (s) of the person (s) that want that specific dinner. I know I can do a sort and then copy paste but I don't want to do that. I want it to update as they are entered. Is this possible? Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
link data
On Sep 11, 8:51*am, Emma wrote:
I don't know if this is possible but what I want to do is link cells based on if another cells is marked. *Example: *Have a sheet called Dinners, on that sheet you have the persons name and then columns for certain dinners. *Then I have seperate sheets for each dinner. *I want to automatically transfer the name (s) of the person (s) that want that specific dinner. *I know I can do a sort and then copy paste but I don't want to do that. *I want it to update as they are entered. *Is this possible? Thanks! Is is possible. On Dinners sheet I have the heading "Name" in A1 then different meal headings in B1 C1 and D1. I have used Data Validation dropdowns in columns B to D with yes & no as choices to indicate which meal/s for each Name. On the first meal sheet in A1 is the formula... =IF(Dinners!B2="yes",ROW(),"") On the second meal sheet in A1 is the formula... =IF(Dinners!C2="yes",ROW(),"") On the third meal sheet in A1 is the formula... =IF(Dinners!D2="yes",ROW(),"") Thes formulas is filled down to a suitable row depth so that all the rows on the Dinner sheet are accounted for. Then for each of the meal sheets, in B1 is the heading "Name" and in B2 is the formula... =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Dinners!A:A,SMA LL(A:A,ROWS($1:1)))) also filled down to a suitable row depth. On each of the meal sheets you can hide column A, which is just a helper column for the formula in column B. Ken Johnson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
link data
On Sep 11, 10:15*am, Ken Johnson wrote:
On Sep 11, 8:51*am, Emma wrote: I don't know if this is possible but what I want to do is link cells based on if another cells is marked. *Example: *Have a sheet called Dinners, on that sheet you have the persons name and then columns for certain dinners. *Then I have seperate sheets for each dinner. *I want to automatically transfer the name (s) of the person (s) that want that specific dinner. *I know I can do a sort and then copy paste but I don't want to do that. *I want it to update as they are entered. *Is this possible? Thanks! Is is possible. On Dinners sheet I have the heading "Name" in A1 then different meal headings in B1 C1 and D1. I have used Data Validation dropdowns in columns B to D with yes & no as choices to indicate which meal/s for each Name. On the first meal sheet in A1 is the formula... =IF(Dinners!B2="yes",ROW(),"") On the second meal sheet in A1 is the formula... =IF(Dinners!C2="yes",ROW(),"") On the third meal sheet in A1 is the formula... =IF(Dinners!D2="yes",ROW(),"") Thes formulas is filled down to a suitable row depth so that all the rows on the Dinner sheet are accounted for. Then for each of the meal sheets, in B1 is the heading "Name" and in B2 is the formula... =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Dinners!A:A,SMA LL(A:A,ROWS($1:1)))) also filled down to a suitable row depth. On each of the meal sheets you can hide column A, which is just a helper column for the formula in column B. Ken Johnson My English isn't really that bad, I'm just a lousy editor. Ken Johnson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
link data
"Ken Johnson" wrote: On Sep 11, 10:15 am, Ken Johnson wrote: On Sep 11, 8:51 am, Emma wrote: I don't know if this is possible but what I want to do is link cells based on if another cells is marked. Example: Have a sheet called Dinners, on that sheet you have the persons name and then columns for certain dinners. Then I have seperate sheets for each dinner. I want to automatically transfer the name (s) of the person (s) that want that specific dinner. I know I can do a sort and then copy paste but I don't want to do that. I want it to update as they are entered. Is this possible? Thanks! Is is possible. On Dinners sheet I have the heading "Name" in A1 then different meal headings in B1 C1 and D1. I have used Data Validation dropdowns in columns B to D with yes & no as choices to indicate which meal/s for each Name. On the first meal sheet in A1 is the formula... =IF(Dinners!B2="yes",ROW(),"") On the second meal sheet in A1 is the formula... =IF(Dinners!C2="yes",ROW(),"") On the third meal sheet in A1 is the formula... =IF(Dinners!D2="yes",ROW(),"") Thes formulas is filled down to a suitable row depth so that all the rows on the Dinner sheet are accounted for. Then for each of the meal sheets, in B1 is the heading "Name" and in B2 is the formula... =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Dinners!A:A,SMA LL(A:A,ROWS($1:1)))) also filled down to a suitable row depth. On each of the meal sheets you can hide column A, which is just a helper column for the formula in column B. Ken Johnson My English isn't really that bad, I'm just a lousy editor. Ken Johnson I am getting it to work, sorta, but I have a questions. Can I make the Yes & No a dollar amount? Things worked when I set it up with Yes and No but when I changed it to $40 and change the formula to"$40" it didn't work. Can I use a dollar amount? Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
link data
"Ken Johnson" wrote: On Sep 11, 10:15 am, Ken Johnson wrote: On Sep 11, 8:51 am, Emma wrote: I don't know if this is possible but what I want to do is link cells based on if another cells is marked. Example: Have a sheet called Dinners, on that sheet you have the persons name and then columns for certain dinners. Then I have seperate sheets for each dinner. I want to automatically transfer the name (s) of the person (s) that want that specific dinner. I know I can do a sort and then copy paste but I don't want to do that. I want it to update as they are entered. Is this possible? Thanks! Is is possible. On Dinners sheet I have the heading "Name" in A1 then different meal headings in B1 C1 and D1. I have used Data Validation dropdowns in columns B to D with yes & no as choices to indicate which meal/s for each Name. On the first meal sheet in A1 is the formula... =IF(Dinners!B2="yes",ROW(),"") On the second meal sheet in A1 is the formula... =IF(Dinners!C2="yes",ROW(),"") On the third meal sheet in A1 is the formula... =IF(Dinners!D2="yes",ROW(),"") Thes formulas is filled down to a suitable row depth so that all the rows on the Dinner sheet are accounted for. Then for each of the meal sheets, in B1 is the heading "Name" and in B2 is the formula... =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Dinners!A:A,SMA LL(A:A,ROWS($1:1)))) also filled down to a suitable row depth. On each of the meal sheets you can hide column A, which is just a helper column for the formula in column B. Ken Johnson My English isn't really that bad, I'm just a lousy editor. Ken Johnson One more thing, when it copies info over it puts in blank rows. Is there anyway to have it not do that? And, What would the formula be if you wanted to update info from two columns. Name & Address columns. Thanks!!!!! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
link data
On Sep 11, 11:22*am, Emma wrote:
"Ken Johnson" wrote: On Sep 11, 10:15 am, Ken Johnson wrote: On Sep 11, 8:51 am, Emma wrote: I don't know if this is possible but what I want to do is link cells based on if another cells is marked. *Example: *Have a sheet called Dinners, on that sheet you have the persons name and then columns for certain dinners. *Then I have seperate sheets for each dinner. *I want to automatically transfer the name (s) of the person (s) that want that specific dinner. *I know I can do a sort and then copy paste but I don't want to do that. *I want it to update as they are entered. *Is this possible? Thanks! Is is possible. On Dinners sheet I have the heading "Name" in A1 then different meal headings in B1 C1 and D1. I have used Data Validation dropdowns in columns B to D with yes & no as choices to indicate which meal/s for each Name. On the first meal sheet in A1 is the formula... =IF(Dinners!B2="yes",ROW(),"") On the second meal sheet in A1 is the formula... =IF(Dinners!C2="yes",ROW(),"") On the third meal sheet in A1 is the formula... =IF(Dinners!D2="yes",ROW(),"") Thes formulas is filled down to a suitable row depth so that all the rows on the Dinner sheet are accounted for. Then for each of the meal sheets, in B1 is the heading "Name" and in B2 is the formula... =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Dinners!A:A,SMA LL(A:A,ROWS($1:1)))) also filled down to a suitable row depth. On each of the meal sheets you can hide column A, which is just a helper column for the formula in column B. Ken Johnson My English isn't really that bad, I'm just a lousy editor. Ken Johnson One more thing, when it copies info over it puts in blank rows. Is there anyway to have it not do that? * And, *What would the formula be if you wanted to update info from two columns. Name & Address columns. Thanks!!!!! Hi Emma, On the Dinners Sheet are $40 and Cell left blank the only options? If your answer is Yes then the formula in column A for the 1st meal could be... =IF(Dinners!B2=40,ROW(),"") the B in Dinners!B2 should be changed to C, D, E... etc for the rest of the meals. If dollar amounts other than $40 are to be used then the formula in column A for the 1st meal could be... =IF(ISNUMBER(Dinners!B2),ROW(),"") One more thing, when it copies info over it puts in blank rows. Is there anyway to have it not do that? You shouldn't be getting blanks in the list of Names the formula produces on each of the meal sheets, I don't get any on mine, even if I have blank rows on the Dinners Sheet. The helper formula in column A produces blank rows, but that column is not for viewing, it should be hidden. Is it possible for you to email me an example workbook? My email is a gmail account... kencjohnson followed by the at symbol followed by gmail followed by a dot followed by com A formula that returns the addresses on the meal sheets will depend on the position of the address column relative to the Name column on the Dinners sheet. If Names are in column A and addresses in column B on the Dinners sheet and if the transferred Names on each meal sheet are in column B (Column A is hidden), then this formula should return the correct addresses on each of the meal sheets... =IF(B2="","",OFFSET(Dinners!$B$1,MATCH(B2,Dinners! A:A,0)-1,0)) filled down as far as needed. Ken Johnson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| link data to worksheets | Excel Worksheet Functions | |||
| Importing Data: OLE DB and data link properties dialog | Excel Discussion (Misc queries) | |||
| Link with Data | Excel Discussion (Misc queries) | |||
| how do i link up 2 sets of data into 1 set of data in excel | Excel Worksheet Functions | |||
| Link Data | Excel Discussion (Misc queries) | |||
Linear Mode
