dynamic offset if statement

Godwin117

Board Regular
Joined
Dec 19, 2019
Messages
61
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have this formula, if the first sheet is blank, then it will go to the second sheet first row with data and continue. It is similar to the vba pasting under that last row, however I don't want to use vba if I don't have to for this workbook. This hopefully is an easy fix to this problem. Any help would be greatly appreciated.

=IF('Sheet1'!A2<>"", 'Sheets1'!A2, OFFSET(Sheet3!A2, -'Sheet2'!G$1, 0))

Sheet2!G1 = countif(A:A, "<>")
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,653
Office Version
  1. 365
Platform
  1. Windows
then it will go to the second sheet first row with data and continue.
If you're saying that you want the formula in the first sheet to 'send' something to the second sheet, then that is not possible. A formula can only show a result in the cell that it is located in.
 

Godwin117

Board Regular
Joined
Dec 19, 2019
Messages
61
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
If you're saying that you want the formula in the first sheet to 'send' something to the second sheet, then that is not possible. A formula can only show a result in the cell that it is located in.
Correct, I'm just looking for the result of the cells. For example Sheet1 has data from A2:A5, and Sheet 3 has data from A2:A4, Sheet2 will display the data from Sheet1 on A2:A5, then from A6:A8 it will display Sheet 3 data. Hopefully this makes sense. I can put it in a table as well if that helps.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,653
Office Version
  1. 365
Platform
  1. Windows
That will not be possible with formulas you will need to use vba.

There might be an office 365 only formula workaround if it is just sheet1 and sheet3 being pulled into sheet2, but any more sheets or pulling each one into the other 2 would not work.
 
Solution

Godwin117

Board Regular
Joined
Dec 19, 2019
Messages
61
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
That will not be possible with formulas you will need to use vba.

There might be an office 365 only formula workaround if it is just sheet1 and sheet3 being pulled into sheet2, but any more sheets or pulling each one into the other 2 would not work.
Ok thank you for your assistance.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,015
Messages
5,639,558
Members
417,098
Latest member
steverob

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
Top