two formula questions

  • Thread starter Thread starter Legacy 177405
  • Start date Start date
L

Legacy 177405

Guest
i need a formula that will pull a number from sheet one in a workbook and display it in sheet two. the specific application is for account numbers however in sheet two i only want the result of the formula to return the last four digits of the account or xxx1234. to be clear the value would be displayed in sheet one as a full 7 digit # then sheet two would have a formula linking to sheet one... i want the the formula to return only the last four digits of the number string in sheet one.

secondly, i would like to link a value in sheet two to a value in sheet one, however if the primary cell is blank i would like the formula to pull from a second cell. is this possible. hopefully i have been clear enough... let me know if i can provide any more detail. thanks in advance for the help
richard keyser
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Welcome to the Board!

For the first take a look at VLOOKUP.

To get the last four digits only you could do something like this:

="xxx"&RIGHT(VLOOKUP(),4)

For the second, you can use an IF statement:

=IF(A1,Formula1,Formula2)

If you want to post more specifics someone can give a less broad-brush example.

HTH,
 
Upvote 0
when i enter the first one, it tells me that i have entered too few arguments
 
Upvote 0
in the second example i want to have a percentage in the first sheet 1% for example then link a cell in sheet two to sheet one so that sheet two would say =sheet!a1. this would result in the answer 1% being displayed in sheet two. however if a1 is blank on sheet one i would like the percentage to be pulled from another cell in sheet 1 other than a1. hope this helps
 
Upvote 0
i think this should be fairly simple. if i have 1% listed in a1 on sheet one i want the formula in cella1 on sheet two to display1%. this would be simple just put the forumla =sheet1!a1. the problem is that i don't know how to make the formula look to cell a2 on sheet one if the first cell on sheet one "a1" is blank. thanks so much for the help.
 
Upvote 0
i think this should be fairly simple. if i have 1% listed in a1 on sheet one i want the formula in cella1 on sheet two to display1%. this would be simple just put the forumla =sheet1!a1. the problem is that i don't know how to make the formula look to cell a2 on sheet one if the first cell on sheet one "a1" is blank. thanks so much for the help.
Try

=IF(Sheet1!A1="",Sheet1!A2,Sheet1!A1)
 
Upvote 0
thanks i got that one, used "if(sheet1!a1>0,sheet1!a1,sheet1!a2)

ANY IDEAS ON THE OTHER ONE?
 
Upvote 0
ANY IDEAS ON THE OTHER ONE?
I don't really understand what you have and what you are trying to do.

- Are trying to look something up on sheet1 based on some criteria, or
- Do you just want something direct like =sheet1!a1 but just returning the right hand part, or
- Something else?

More details would help. Description and/or small screen shot of data and expected results. My signature block below contains 3 methods for posting small screen shots. Test them in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.
 
Upvote 0
you are correct. if cell a1 in sheet in is 1234567. i would like a formula in cell a1 sheet two to give the response of 4567 or xxx4567. hope this helps
richard
 
Upvote 0
you are correct. if cell a1 in sheet in is 1234567. i would like a formula in cell a1 sheet two to give the response of 4567 or xxx4567. hope this helps
richard

Try to post some examples along with the desired results... For example:

A1:

A2: 1234567

A3: 1235

A4: 678

What would be the desired results?
 
Upvote 0

Forum statistics

Threads
1,224,603
Messages
6,179,854
Members
452,948
Latest member
UsmanAli786

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