Re: Call a formula in a cell and use it on the next cell
Follow up question, If I protect the sheet the solution will not work anymore.. any ideas on how to deal with it? I really need to protect the formula cells.. thanks!
Re: Call a formula in a cell and use it on the next cell
Follow up question, If I protect the sheet the solution will not work anymore.. any ideas on how to deal with it? I really need to protect the formula cells.. thanks!
Re: Call a formula in a cell and use it on the next cell
Quote from NBVC;632401You preselect and create the table only initially with only the currently occupied rows/columns.
Adding rows below afterwards, increments the table.
Thanks! I tried that and it worked! Really appreciate your help
Re: Call a formula in a cell and use it on the next cell
Quote from NBVC;632374Select the table, or any cell within it, and go to Insert tab, then select Table and follow any prompts.
After you are finished, if yuo have formulas in the table, they should copy down once you start adding info to a new row directly beneath the table.
ok.. the solution works if i pre-select the table that needs to be filled up. Is there any other way that the new data will just automatically use the formula in the previous row and not pre-selecting the entire table using the solution? the reason that I don't this is because when you print the sheet, there will be blank rows since I pre-selected it already. TIA!
Re: Call a formula in a cell and use it on the next cell
Quote from NBVC;632290You can use the List feature in the Data menu (if in Excel 2003 or earlier)
You can use the Table feature in the Insert Tab (if in Excel 2007 or earlier)
when you add new lines, the formulas should automatically copy down to those rows...
Thanks for your reply. I'm using Excel 2010 and trying to figure out how to do my inquiry in the Data Table feature. Any ideas?
Thanks!
Hi is it possible to just enter the formula once in a cell and call it on the next cell if needed? What I'm doing now is copy the formula in the succeeding cells to make it work.
for example:
I have a formula in M6 which is =(I6+J6+K6)*0.115, O6 which is =(J6+M6+I6+K6)*0.13 and P6 which is =J6+M6+O6+K6+I6. So if I enter an amount in I6, J6 and K6 the formulas will run. Is there a way that it will be automatic in the next row 7 without dragging the formula all the way down?
Thanks in advance!
Re: Macro to auto-fill columns based on row number
Hi rajiv, i've shared the link of my sheet in my first post. Im looking to make a macro or any kind of means to be able to enter an id# located in column A and select the kind of SET pricing wherein the values are predefined already.
Thanks for your help..
Re: Macro to auto-fill columns based on row number
Hi Rajivalwar, thanks a lot for the reply. Meanwhile, if I wanted to add more SET values say I need to add SET C - SET G? Is there a way I can do that? Also ID #226 is not a constant value I should be able to enter different ID numbers and SET letters.
Thanks a lot again for your assistance.
hi guys,
I'm trying to create a macro for my data entry to autofill the columns from AE to AO.
basically, in the macro i should have the ability to enter the ID# from Column A and the data to be filled out in columns AE-AO
here are the values to be entered in columns AE to AO
SET A:
AE=20.60, AF=8.45, AG=9.36, AH=4.92, AI=2.65, AJ=1.35, AK=.98, AL=.69, AM=.57, AN-=.66, AO=.50
SET B:
AE=22.60, AF=10.88, AG=11.28, AH=6.32, AI=3.31, AJ=1.75, AK=1.14, AL=.77, AM=.61, AN-=.75, AO=.63
so in the macro, if i enter ID # 226 from Column A and enter SET A, the columns in row of ID# 226 will be filled out with SET A values.
appreciate your help in advance.
here is the link to my spreadsheet that I'm working on. >> http://www.4shared.com/office/…_bid_template_Excel_.html
thanks!
Re: Search and output total
Hi Guys,
Need your help again.. I've created another pivot table using the PALLET # search field. however, for some strange reason if I type in Pallet # 57 or 21 in the search field and hit enter, it doesn't show up.. i don't know if there are other PALLET #s that doesn't show up probably there is.. but if you use the dropdown menu and chose the pallet #21 or 57, it will show up.. is this a bug or something?
Appreciate if you can take a look at it again co'z this is really strange. It is in the Pallet Checker sheet. Here my link to the spreadsheet (http://www.4shared.com/zip/3lZ…ORT__2_.html?refurl=d1url)
Thanks in advance!
Re: Search and output total
hi PCI,
i'm just using the exact pivot table that your created in the previous sample sheet.
yes.. no merged cells
what is the complete email address? sorry i'm just a newbie.. appreciate your patience..
Re: Search and output total
Hi PCI.. thanks for the reply. I did see that you used MyData as the range in the sample sheet. However, I still cannot change the source data. After I copied your pivot table to my working sheet, and tried to change the range by highlighting the columns and rows in my working sheet, i get an error "reference not valid" and when I tried to Refresh Data I get an error Cannot open Pivot Table source file REPORT-NEW1.xls" which is obviously the sample work sheet Any ideas?
Thanks!
Re: Search and output total
Hi PCI, I'm playing around with your pivot table. How can you update the data source file? I copied the entire pivot table in the sample spreadsheet you attached to my working spreadsheet. however, when I try to refresh the data, it is pointing the the sample spreadsheet where it came from. any ideas? thanks a lot!
Re: Formulas and Conditions in Excel
Hi NBVC,
I got my mistake.. I pasted the same formula used in H11 to J11 and L11
Thanks a lot for your help!
Re: Formulas and Conditions in Excel
Hi NBVC,
If I do your suggestion above, all calculations and output is always on N11. Nothing shows up on H11, J11 and L11
Any ideas?
Thanks!
Re: Formulas and Conditions in Excel
HI NBVC,
I tried your suggestion but the output is wrong and it shows up in all columns. Is there a way that the output shows up in the column as per the weight?
Thanks!
Hi guys, please help me in my spreadsheet in regards to the conditions and applying the formula to get the correct output.
Here's the 1st scenario:
If I enter 0-1000 under the Weight Column, it should use the multiplier 0.0314 and output the value in the Default Price
1001-2000 use multiplier 0.0215
2001-3000 use multiplier 0.0182
3001-4000 use multiplier 0.0165
and so on if i needed to add more columns and multipliers
Please see my attached spreadsheet.
Thanks a lot guy and I have to say this forum really helped me a lot so many times!! :):)
Again thanks in advance!
Regards!
Re: Search and output total
hi Jindon and PCI, thanks a lot for your help! I'll try out your macros today appreciate everything!
Re: Search and output total
sorry guys.. mistakenly uploaded the incorrect spreadsheet in my original post. Here is the correct one. Appreciate your help. thanks!
Re: Search and output total
Hi PCI, thanks for taking time to check my posts. I can't figure out how to use the dashboard you have added in my spreadsheet. It seems is has only few of my entries in the original spreadsheet. Also, can't the search function in include the PART# and LOT CODE? It should be "AND" not "OR" so meaning, I need to output the counts of this PART# and LOT CODE
Thanks a lot!
Hi,
Can anyone help me again to create a macro that will find and calculate based on the details of 4 columns on the same spreadsheet.
Basically, here is what I need to do:
1. Need to calculate how many QTY IN (Column F) and PALLETS IN (Column I) of a specific PART# (Column A) and LOT CODE (Column B) For example if enter a PART # and LOT CODE, it will calculate how many QTY IN and PALLET IN of that PART # and LOT CODE.
2. Need to do the same for QTY OUT (Column G) and PALLETS OUT (Column J) also.
3. Output should contain the PALLET # , QTY IN and PALLETS IN based on the PART # and LOT CODE search. I just want to simplify the search function in this spreadsheet because it is very cumbersome if you need to find how many items left for that PART # and LOT CODE.
Appreciate your help in advance. Attached is the spreadsheet..
Thanks!