IF and AND formula

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Hello formula experts
I have these 3 sheets in the workbook and I want to get the data in sheet C from sheet B depending on the sheet A. In short, it is 2 if conditions in one formula. If the cell match 2 conditions then get Taxable value in cell H2 else show blank.
1. =IF(LEFT(B!A7,2,=A!$A$1
2. =IF(B!I7='C'!$H$1
Can someone please tell me the correct formula to apply in cell C2 and get the right answer.?
something is wrong with XL2BB. When I select the Capture range > select all, the buffering doesn't stop and I have to close the sheet.
Get Amount in GST class.xlsm
 
If the left 2 characters in B match with A then the amount is to be posted to one column under same rate from H to M.
If the characters do not match then the amount is to be posted to one column under same rate from N to S.
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I got the first row correct. But I have to drag down the formulas till the end depending on how many rows of data the workbook contains.
 
Upvote 0
If there is no other way then it is better to enter 12 formulas in one row and then drag it down. I think that will work. Let me know if you have any other better option.
 
Upvote 0
sorry not following

can ypu put in the spreadsheet all the formulas that are working so i can see what happens / needed when dragged
 
Upvote 0
I am still trying. I will post it once I get all the rows and columns are posted correctly.
 
Upvote 0
doesnt need to be all, enough so i can see whats going on
 
Upvote 0
Still working on it horizontally so that I can drag it down
 

Attachments

  • Test.png
    Test.png
    14.2 KB · Views: 4
Upvote 0
cant see the image - which are difficult to follow

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed
 
Upvote 0
xlbb is not working today. Maybe it is under upgradation. I will share a link in some time.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top