Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row
Hi
I have employee time sheets with a list of names with formulas attached to adjacent cells to count holidays and sick. I want to know if there is a way to insert a row so I could add a new name but also so the formula follows the name. The formula I have is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&{"18 jan","25 jan"}&"'!b10:h10"),"hols")) So I need the b10:h10 to move down automatically to b11:h11. Any ideas greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row
Hi,
Go to the left margin of the spreadsheet and click on the row where you want to insert, do insert, it will insert a new whole row, copy the formulas from above and you are done If this was helpful, please say yes. Thank you "Pootle" wrote: Hi I have employee time sheets with a list of names with formulas attached to adjacent cells to count holidays and sick. I want to know if there is a way to insert a row so I could add a new name but also so the formula follows the name. The formula I have is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&{"18 jan","25 jan"}&"'!b10:h10"),"hols")) So I need the b10:h10 to move down automatically to b11:h11. Any ideas greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row
One of the reasons for using Indirect is so that references *don't* change.
As you have only two conditions, one solution would be: =countif('18 jan'!b10:h10,"hols")+countif('25 jan'!b10:h10,"hols") Regards, Fred. "Pootle" wrote in message ... Hi I have employee time sheets with a list of names with formulas attached to adjacent cells to count holidays and sick. I want to know if there is a way to insert a row so I could add a new name but also so the formula follows the name. The formula I have is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&{"18 jan","25 jan"}&"'!b10:h10"),"hols")) So I need the b10:h10 to move down automatically to b11:h11. Any ideas greatly appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row
You could slightly modify formula to:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"18 jan","25 jan"}&"'!B"&ROW(A10)&":H"&ROW(A10)),"hols")) Now you have cell references determining the row number, and they will adjust accordingly. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Pootle" wrote: Hi I have employee time sheets with a list of names with formulas attached to adjacent cells to count holidays and sick. I want to know if there is a way to insert a row so I could add a new name but also so the formula follows the name. The formula I have is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&{"18 jan","25 jan"}&"'!b10:h10"),"hols")) So I need the b10:h10 to move down automatically to b11:h11. Any ideas greatly appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row
Thanks for your responses.
The reason I wouldn't want to use Fred's example is because I want to continually use this so would end up with 52 sheets for 52 weeks of the year and thus still have another 50 additions to the formula. "Fred Smith" wrote: One of the reasons for using Indirect is so that references *don't* change. As you have only two conditions, one solution would be: =countif('18 jan'!b10:h10,"hols")+countif('25 jan'!b10:h10,"hols") Regards, Fred. "Pootle" wrote in message ... Hi I have employee time sheets with a list of names with formulas attached to adjacent cells to count holidays and sick. I want to know if there is a way to insert a row so I could add a new name but also so the formula follows the name. The formula I have is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&{"18 jan","25 jan"}&"'!b10:h10"),"hols")) So I need the b10:h10 to move down automatically to b11:h11. Any ideas greatly appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row
Luke,
I notice your formula works great however it seems to be dependent on me updating all my worksheets (18 Jan, 25 Jan etc). Is there no way around this as this would be a real bug bear when I get a new employee half way through the year and I have to adjust all worksheets and not just the ones he would be inserted into. Many thanks "Pootle" wrote: Thanks for your responses. The reason I wouldn't want to use Fred's example is because I want to continually use this so would end up with 52 sheets for 52 weeks of the year and thus still have another 50 additions to the formula. "Fred Smith" wrote: One of the reasons for using Indirect is so that references *don't* change. As you have only two conditions, one solution would be: =countif('18 jan'!b10:h10,"hols")+countif('25 jan'!b10:h10,"hols") Regards, Fred. "Pootle" wrote in message ... Hi I have employee time sheets with a list of names with formulas attached to adjacent cells to count holidays and sick. I want to know if there is a way to insert a row so I could add a new name but also so the formula follows the name. The formula I have is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&{"18 jan","25 jan"}&"'!b10:h10"),"hols")) So I need the b10:h10 to move down automatically to b11:h11. Any ideas greatly appreciated. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row
As long as it follows a pattern or you have the info located somewhere, you
can automate it. But, you didn't say what you wanted the dates to change to. I'm not sure I understand what you're exactly trying to do. It sounds like you're trying to do a 3D reference, but neither COUNTIF nor INDIRECT support such a thing. If you could describe in more detail what your sheet layout looks like, "we'll" probably be able to come up with a solution. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Pootle" wrote: Luke, I notice your formula works great however it seems to be dependent on me updating all my worksheets (18 Jan, 25 Jan etc). Is there no way around this as this would be a real bug bear when I get a new employee half way through the year and I have to adjust all worksheets and not just the ones he would be inserted into. Many thanks "Pootle" wrote: Thanks for your responses. The reason I wouldn't want to use Fred's example is because I want to continually use this so would end up with 52 sheets for 52 weeks of the year and thus still have another 50 additions to the formula. "Fred Smith" wrote: One of the reasons for using Indirect is so that references *don't* change. As you have only two conditions, one solution would be: =countif('18 jan'!b10:h10,"hols")+countif('25 jan'!b10:h10,"hols") Regards, Fred. "Pootle" wrote in message ... Hi I have employee time sheets with a list of names with formulas attached to adjacent cells to count holidays and sick. I want to know if there is a way to insert a row so I could add a new name but also so the formula follows the name. The formula I have is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&{"18 jan","25 jan"}&"'!b10:h10"),"hols")) So I need the b10:h10 to move down automatically to b11:h11. Any ideas greatly appreciated. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row
I will have 52 worksheets for 52 weeks of the year. I will have another
'summary' sheet where the formula you helped me with is seated. In the 52 worksheets is a list of employees and across the top is the day of the week. This is designed to record whether an employee was in, sick or on holiday. The idea of the summary sheet is to keep a running count of each employee's sick and holidays during the year. So as I fill in the worksheet through the week it is automatically counted on my summary sheet. I hope this makes sense. Name Sun Mon Tues Weds Thurs Fri J.Bloggs in hol hol hol hol sick So on my summary sheet I will have my formulas counting 4 holidays and 1 sick day for J.Bloggs. Cheers Paul "Luke M" wrote: As long as it follows a pattern or you have the info located somewhere, you can automate it. But, you didn't say what you wanted the dates to change to. I'm not sure I understand what you're exactly trying to do. It sounds like you're trying to do a 3D reference, but neither COUNTIF nor INDIRECT support such a thing. If you could describe in more detail what your sheet layout looks like, "we'll" probably be able to come up with a solution. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Pootle" wrote: Luke, I notice your formula works great however it seems to be dependent on me updating all my worksheets (18 Jan, 25 Jan etc). Is there no way around this as this would be a real bug bear when I get a new employee half way through the year and I have to adjust all worksheets and not just the ones he would be inserted into. Many thanks "Pootle" wrote: Thanks for your responses. The reason I wouldn't want to use Fred's example is because I want to continually use this so would end up with 52 sheets for 52 weeks of the year and thus still have another 50 additions to the formula. "Fred Smith" wrote: One of the reasons for using Indirect is so that references *don't* change. As you have only two conditions, one solution would be: =countif('18 jan'!b10:h10,"hols")+countif('25 jan'!b10:h10,"hols") Regards, Fred. "Pootle" wrote in message ... Hi I have employee time sheets with a list of names with formulas attached to adjacent cells to count holidays and sick. I want to know if there is a way to insert a row so I could add a new name but also so the formula follows the name. The formula I have is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&{"18 jan","25 jan"}&"'!b10:h10"),"hols")) So I need the b10:h10 to move down automatically to b11:h11. Any ideas greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting new row | Excel Worksheet Functions | |||
inserting row | Excel Discussion (Misc queries) | |||
Inserting? | Excel Discussion (Misc queries) | |||
Inserting a tab | Excel Discussion (Misc queries) | |||
Inserting a row below | Excel Discussion (Misc queries) |