Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having to Press F2 to recognise data from another sheet in Excel
Hi there,
I don't know whether anyone can help me or not. However, if there are any Excel experts out there, I am a little stuck. I download some information from an Access into Sheet A. I then have to pull the information from Sheet A into Sheet B using the following formula =SUMIF('Sheet A'!$E:$E,CONCATENATE($B3,"$",D$2),'Sheet A'!$D:$D) This does not enter the information into Sheet B until you go back into the cell in Sheet A and press F2 to edit and then press Enter. Is there a way of preventing this? If not, how can I produce a macro to allow me to automatically. When I record one it comes up with this: Range("D2").Select ActiveCell.FormulaR1C1 = "44160" Range("D3").Select ActiveCell.FormulaR1C1 = "21600" Range("D4").Select I would appreciate some assistance if possible... Cheers AJ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having to Press F2 to recognise data from another sheet in Excel
I'm not quite sure what your'e trying to do here, (the formula doesn't help)
are you trying to manipulate the data from Sheet A or just copy it? "AJButler" wrote: Hi there, I don't know whether anyone can help me or not. However, if there are any Excel experts out there, I am a little stuck. I download some information from an Access into Sheet A. I then have to pull the information from Sheet A into Sheet B using the following formula =SUMIF('Sheet A'!$E:$E,CONCATENATE($B3,"$",D$2),'Sheet A'!$D:$D) This does not enter the information into Sheet B until you go back into the cell in Sheet A and press F2 to edit and then press Enter. Is there a way of preventing this? If not, how can I produce a macro to allow me to automatically. When I record one it comes up with this: Range("D2").Select ActiveCell.FormulaR1C1 = "44160" Range("D3").Select ActiveCell.FormulaR1C1 = "21600" Range("D4").Select I would appreciate some assistance if possible... Cheers AJ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having to Press F2 to recognise data from another sheet in Exc
I am trying to manipulate the information....
ie.. If Delivery date is equal to "*" and Item Code is equal to "*" then enter the Delivery Qty from this line to this cell... It is quite complex for me and I have had a bit of help.. Without showing the actual spreadsheet, it is difficult to describe.. "bigwheel" wrote: I'm not quite sure what your'e trying to do here, (the formula doesn't help) are you trying to manipulate the data from Sheet A or just copy it? "AJButler" wrote: Hi there, I don't know whether anyone can help me or not. However, if there are any Excel experts out there, I am a little stuck. I download some information from an Access into Sheet A. I then have to pull the information from Sheet A into Sheet B using the following formula =SUMIF('Sheet A'!$E:$E,CONCATENATE($B3,"$",D$2),'Sheet A'!$D:$D) This does not enter the information into Sheet B until you go back into the cell in Sheet A and press F2 to edit and then press Enter. Is there a way of preventing this? If not, how can I produce a macro to allow me to automatically. When I record one it comes up with this: Range("D2").Select ActiveCell.FormulaR1C1 = "44160" Range("D3").Select ActiveCell.FormulaR1C1 = "21600" Range("D4").Select I would appreciate some assistance if possible... Cheers AJ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having to Press F2 to recognise data from another sheet in Excel
If you're seeing the formula and not the results, then make sure the cells are
formatted as General (or anything but Text) and then Select those cells Edit|Replace what: = (equal sign) with: = replace all Excel will see that you've reentered each formula. If you what excel evaluates the formula, but it isn't correct, then make sure that calculation is set to automatic. (Tools|Options|Calculation tab) If you have calculation set to automatic and still see the incorrect results, then select the cells and do that edit|replace stuff. Excel will still reevaluate each formula. AJButler wrote: Hi there, I don't know whether anyone can help me or not. However, if there are any Excel experts out there, I am a little stuck. I download some information from an Access into Sheet A. I then have to pull the information from Sheet A into Sheet B using the following formula =SUMIF('Sheet A'!$E:$E,CONCATENATE($B3,"$",D$2),'Sheet A'!$D:$D) This does not enter the information into Sheet B until you go back into the cell in Sheet A and press F2 to edit and then press Enter. Is there a way of preventing this? If not, how can I produce a macro to allow me to automatically. When I record one it comes up with this: Range("D2").Select ActiveCell.FormulaR1C1 = "44160" Range("D3").Select ActiveCell.FormulaR1C1 = "21600" Range("D4").Select I would appreciate some assistance if possible... Cheers AJ -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having to Press F2 to recognise data from another sheet in Exc
The cells in the sheet I am trying to pull the information into have a '0' in
it. I tried the 'replace' suggestion you mentioned, but there isn't an '=' in the cells. Do you have anymore suggestions? "Dave Peterson" wrote: If you're seeing the formula and not the results, then make sure the cells are formatted as General (or anything but Text) and then Select those cells Edit|Replace what: = (equal sign) with: = replace all Excel will see that you've reentered each formula. If you what excel evaluates the formula, but it isn't correct, then make sure that calculation is set to automatic. (Tools|Options|Calculation tab) If you have calculation set to automatic and still see the incorrect results, then select the cells and do that edit|replace stuff. Excel will still reevaluate each formula. AJButler wrote: Hi there, I don't know whether anyone can help me or not. However, if there are any Excel experts out there, I am a little stuck. I download some information from an Access into Sheet A. I then have to pull the information from Sheet A into Sheet B using the following formula =SUMIF('Sheet A'!$E:$E,CONCATENATE($B3,"$",D$2),'Sheet A'!$D:$D) This does not enter the information into Sheet B until you go back into the cell in Sheet A and press F2 to edit and then press Enter. Is there a way of preventing this? If not, how can I produce a macro to allow me to automatically. When I record one it comes up with this: Range("D2").Select ActiveCell.FormulaR1C1 = "44160" Range("D3").Select ActiveCell.FormulaR1C1 = "21600" Range("D4").Select I would appreciate some assistance if possible... Cheers AJ -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having to Press F2 to recognise data from another sheet in Exc
If you would like to have a look at the spreadsheet I am refering to, I have
attached a link and you may be able to see what I am talking about. Just don't try to refresh of run the macros as you will not be able to do this. Sheet A is 'Deliveries Download' and Sheet B is 'SONOCO Deliveries'. Each sheet after this picks up the deliveries from the 'SONOCO Deliveries' sheet. The link I have created is: http://www.molgen.mpg.de/cgi-bin/web...4_Schedule.xls This might help explain things a little clearer... Cheers "AJButler" wrote: The cells in the sheet I am trying to pull the information into have a '0' in it. I tried the 'replace' suggestion you mentioned, but there isn't an '=' in the cells. Do you have anymore suggestions? "Dave Peterson" wrote: If you're seeing the formula and not the results, then make sure the cells are formatted as General (or anything but Text) and then Select those cells Edit|Replace what: = (equal sign) with: = replace all Excel will see that you've reentered each formula. If you what excel evaluates the formula, but it isn't correct, then make sure that calculation is set to automatic. (Tools|Options|Calculation tab) If you have calculation set to automatic and still see the incorrect results, then select the cells and do that edit|replace stuff. Excel will still reevaluate each formula. AJButler wrote: Hi there, I don't know whether anyone can help me or not. However, if there are any Excel experts out there, I am a little stuck. I download some information from an Access into Sheet A. I then have to pull the information from Sheet A into Sheet B using the following formula =SUMIF('Sheet A'!$E:$E,CONCATENATE($B3,"$",D$2),'Sheet A'!$D:$D) This does not enter the information into Sheet B until you go back into the cell in Sheet A and press F2 to edit and then press Enter. Is there a way of preventing this? If not, how can I produce a macro to allow me to automatically. When I record one it comes up with this: Range("D2").Select ActiveCell.FormulaR1C1 = "44160" Range("D3").Select ActiveCell.FormulaR1C1 = "21600" Range("D4").Select I would appreciate some assistance if possible... Cheers AJ -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having to Press F2 to recognise data from another sheet in Exc
This is a secure link by the way.. The file has been virus checked!
"AJButler" wrote: If you would like to have a look at the spreadsheet I am refering to, I have attached a link and you may be able to see what I am talking about. Just don't try to refresh of run the macros as you will not be able to do this. Sheet A is 'Deliveries Download' and Sheet B is 'SONOCO Deliveries'. Each sheet after this picks up the deliveries from the 'SONOCO Deliveries' sheet. The link I have created is: http://www.molgen.mpg.de/cgi-bin/web...4_Schedule.xls This might help explain things a little clearer... Cheers "AJButler" wrote: The cells in the sheet I am trying to pull the information into have a '0' in it. I tried the 'replace' suggestion you mentioned, but there isn't an '=' in the cells. Do you have anymore suggestions? "Dave Peterson" wrote: If you're seeing the formula and not the results, then make sure the cells are formatted as General (or anything but Text) and then Select those cells Edit|Replace what: = (equal sign) with: = replace all Excel will see that you've reentered each formula. If you what excel evaluates the formula, but it isn't correct, then make sure that calculation is set to automatic. (Tools|Options|Calculation tab) If you have calculation set to automatic and still see the incorrect results, then select the cells and do that edit|replace stuff. Excel will still reevaluate each formula. AJButler wrote: Hi there, I don't know whether anyone can help me or not. However, if there are any Excel experts out there, I am a little stuck. I download some information from an Access into Sheet A. I then have to pull the information from Sheet A into Sheet B using the following formula =SUMIF('Sheet A'!$E:$E,CONCATENATE($B3,"$",D$2),'Sheet A'!$D:$D) This does not enter the information into Sheet B until you go back into the cell in Sheet A and press F2 to edit and then press Enter. Is there a way of preventing this? If not, how can I produce a macro to allow me to automatically. When I record one it comes up with this: Range("D2").Select ActiveCell.FormulaR1C1 = "44160" Range("D3").Select ActiveCell.FormulaR1C1 = "21600" Range("D4").Select I would appreciate some assistance if possible... Cheers AJ -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import data into Excel sheet from CSV File | Excel Discussion (Misc queries) | |||
Data Matching Button for MS Excel 2007 or later | Excel Worksheet Functions | |||
macro | Excel Discussion (Misc queries) | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
After concatenating data, how do I get Excel to recognise date? | Excel Discussion (Misc queries) |