Posts by mitank

Thanks PecoFlyer and royUK, the formula is working fine, even after closing the excel file

Thanks a lot

Further I have also mentioned the range name in Name Manager

Sorry to bother you once again. Although the formula is well applied without any error,

But when I close the excel file completely and reopen the file the named table again changes to Range value

For Eg. the formula =IFERROR(VLOOKUP(B4,Table4[[Table B]:[Table D]],3,FALSE),"") after save the file and close the excel file completely and again reopen the file the formula again changes back to =IFERROR(VLOOKUP(B3,Sheet2!$B$2:$D$21,3,FALSE),"")

Attaching the file for reference

I could Do it, Thanks Very Much for Help.

I was actually applying the formula without formatting the Data.

ok, will try

I have created a table on Sheet1

Reference to Lookup is in Sheet2, wherein the entire table range has been named Table

Now in Sheet1 I want to create a formula based on range name but there seems to be an error in the formula which I cannot figure it out.

The Formula I want to create is in Sheet1 Row C

The formula should lookup the value in Row B, and put the correct figure from Sheet2, row D

What I want is shown from C6 onwards. I want to create a formula based on Range named as Table

The formula I have used us shown in Sheet1, Cell C3 that has a error

Attached file for reference

I have list of Expenses in Sheet2

Sheet1 contains the detailed expenses of each item

In Sheet3 in column B, When I select the list of expenses ( Already Done )

Then ( What I Want to Do is )

Column C should show only the expenses related to items selected in Column B, The list of Expenses is shown in Sheet1

I have a question on Excel Power Query, could you please let me know if this is the correct forum for help

I have list of numbers in column B

I want to create VBA such that it selects all numbers that the total would be equal to or nearest to number specified in cell F2

All selected numbers should be written in column D adjacent to selected one from column B

Then it should total the numbers and written in cell D19

Works perfectly fine, Thank You.

Yes the word Summary can be found ONLY in column A from A10 onwards till down, and anywhere.
I have attached a sample sheet as to what exactly it should look after VBA is applied.

Row A6 to AH6 is the data that needs to be copied, this data is already written.

In column A I have shown example of only 5 word where Summary are given but there can be many more or less than 5 in column A to down
Above the word Summary the entire Row A6 to AH6 is copied after VBA is applied.

I have data in columns A6:AH6

There are many other details in the entire sheet, lets say a word "Summery" comes many times from column A10 onwards
Whereever there is the word "Summary" from column A10 onwards I want VBA to copy entire text from A6:AH6 and paste it above the word Summery

I already have already created an empty row above the word Summary so no text would be replaced.

Great thanks, works superb

I have used your formula and it is giving error.

Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated.

I am attaching the excel sheet where I have applied the formula.

I am very much sorry for incomplete posting.

I have revised my attachment.

In sheet Apr, May and Jun there are more than 1 for places named 1, 2 and 5

What I require is

In sheet1 Cell B1, it should total all the cells from sheets Apr to Jun that matches Cell A8 in sheet1 and also matches cell B4 in sheet1

In my final sheet there are Apr to Mar (12 months) sheets.

Once again I apologize for incomplete post.

I have created where there are multiple sheets, lets say from Apr to Jun ( In actual work sheets I have from Apr to Mar )

In sheet 1 I am trying to figure out a formula with multiple criteria.

First the text should match A8 and B4
Then it should pick up and sum up those numbers whose criteria matches as mentioned above.

Example attached

Re: Exact Multiply in Excel

Thanks for help

Please see the excel sheet.

Please note that I have entered random between formula that may change values in your system.

I have entered =RANDBETWEEN(100,999)/99 in cell C8 and have placed comma style to 2
The value of randbetween I got is 8.52.

Now if we manually multiply 8.52 x 1000 we should get 8520,
whereas Excel calculate it as 8515.

I would like excel to calculate it as 8520 only.

I tried the rounding formula like =ROUND((C8*E8),-1), but that did not help as I have many other data where -1 does not give correct result.

Re: Pressing Enter Changes Data

thanks for help

I have made an excel sheet with many Radombetween data.
But problem is that whenever I press Enter or F2 and enter all the data changes.

1. I would not like any data to change unless I press the command button as shown in the example sheet.

2. I would like this command button to appear in all sheets, but this button should not be printed when I give print command.