• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

In this example how to change sheet1 to sheet 2 and sheet 3 as we scroll down?

C

common-man

New Member
=VLOOKUP("ABC",'[Date wise Price.xlsx]Sheet1'!A$2:I$200,5,FALSE)

In this example how to change sheet1 to sheet 2 and sheet 3 as we scroll down?
 
Hi Common-man..
Welcome to the forum...

Can you please try this....
=VLOOKUP("ABC",INDIRECT("'[Date wise Price.xlsx]Sheet " & ROW(A1) & "'!A$2:I$200"),5,FALSE)
 
Debraj said:
Hi Common-man..
Welcome to the forum...

Can you please try this....
=VLOOKUP("ABC",INDIRECT("'[Date wise Price.xlsx]Sheet " & ROW(A1) & "'!A$2:I$200"),5,FALSE)
Click to expand...
Dear Debrajji, Many thanx for ur prompt help. However, i am very new to excel and i tried to do as per ur instruction, didn't get the desired result. Hence i am uploading the dummy file for ur reference.

In B2 cell of bb file i have already got the desired result. What i wanted to do is to have the desired result in b3, b4, b5 etc with the expansion of b2. The source data for bb file is in different sheet of date wise file. For eg, for b3 cell of bb file is in 3 of data wise file. I hope i made my problem clear to you. If further clarification required pls do let me know.
 

Attachments

Hi, common-man!

If you try this formula in cell B2 of worksheet Sheet1 of file BB.xlsx:
=BUSCARV('[Date Wise.xlsx]1'!A2;'[Date Wise.xlsx]1'!A$2:I$9;5;FALSO) -----> in english: =VLOOKUP('[Date Wise.xlsx]1'!A2,'[Date Wise.xlsx]1'!A$2:I$9,5,FALSE)
you'll get a somehow scrolled down data changing, but I'm not sure if it's what you want, since as per your sample files it works only for the 1st 8 cases where you've placed data in column A of all the identical (???) worksheets of 2nd workbook Date Wise.xlsx.

I think there's a missing table to hold the relation between 1, 2, 3, ... of 1st WB 1st WS 1st column data, and the data at 1st column of 1st (if not, then which?) WS of 2nd WB, i.e., ABC, XYZ, DMS, ...

Regards!
 
Hi common-man,

In workbook BB.xlsx put this formula in B2 and copy down.

=IFERROR(VLOOKUP("ABC",INDIRECT("'[Date_Wise.xlsx]"&ROWS($B$2:B2)&"'!$A$2:$I$9"),5,FALSE),"")

Save both files in same location.

Just advise if any issue.

Regards!
 
Somendra Misra said:
Hi common-man,

In workbook BB.xlsx put this formula in B2 and copy down.

=IFERROR(VLOOKUP("ABC",INDIRECT("'[Date_Wise.xlsx]"&ROWS($B$2:B2)&"'!$A$2:$I$9"),5,FALSE),"")

Save both files in same location.

Just advise if any issue.

Regards!
Click to expand...

Thanks somendraji, problem solved. Thanx to sir jb7 and debraj ji too.
 
common-man said:
Thanks somendraji, problem solved. Thanx to sir jb7 and debraj ji too.
Click to expand...
Dear Somendraji,
I have come across another obstacle. In the same file bb, in sheet 1 there are company names that i have put under drop down list. Is it possible to choose any one company from that list and to have the same companies data in the sheet 2? If possible please suggest me as it will help to save my time immensely.
 

Attachments

Hi common-man,

Use Sheet2!$A$1 instead of "ABC" inside vlookup function as lookup value. than the formula will be dynamic.

Regards!
 
common-man said:
Thanks a lot somendra ji.
Click to expand...
Dear somendraji,

It has been long time and with your help i have been working with data in excel. However i have come across an obstacle and seeking help from you. I have uploaded two files. Date wise price is the file where i have stored the raw data and analysis is the file where i have put some formulas. First kindly open both files then go to analysis file and in sheet company name choose chilime hyrdopower co. on green highlighted area then go to bb sheet. The formula is working properly as the stock is traded daily. But in the case of stock like summit microfinance the above mentioned bb, pivot and rsi sheet is not working as the stock is not traded daily and many of the cells are blank. As a result the formula is not working. So is there any way i can eradicate the blank cells automatically and the formula gets me desired result whenever i choose stocks like summit microfinance in the green highlighted area of first sheet.

As always waiting for your guidance.
 

Attachments

common-man said:
Dear somendraji,

It has been long time and with your help i have been working with data in excel. However i have come across an obstacle and seeking help from you. I have uploaded two files. Date wise price is the file where i have stored the raw data and analysis is the file where i have put some formulas. First kindly open both files then go to analysis file and in sheet company name choose chilime hyrdopower co. on green highlighted area then go to bb sheet. The formula is working properly as the stock is traded daily. But in the case of stock like summit microfinance the above mentioned bb, pivot and rsi sheet is not working as the stock is not traded daily and many of the cells are blank. As a result the formula is not working. So is there any way i can eradicate the blank cells automatically and the formula gets me desired result whenever i choose stocks like summit microfinance in the green highlighted area of first sheet.

As always waiting for your guidance.
Click to expand...
Hi there,

If the cells are blanks that's means that the data is not present on the sheet. What you want to be there instead of a blank cells there.



Regards,
 
Dear Somendraji,

The blank cells means that the stock is not traded on the particular day. Thus, the formula that i have put there doesn't provide the true picture when the cell is blank. So i was wondering is there someway the formula would work properly by deleting the blank cells in BB sheet and rearranging the formula to have the desired result. For eg. when i put summit microfinance in green cell of Company sheet the formula shall rearrange by deleting the blank cells.
 
@common-man

As far as I know, Formulas can't delete cells, columns or rows. You can put a text as "No trade" in cells where you get blank cells. Or you may have to shift to VBA for achieving such a result.

Regards,
 
You must log in or register to reply here.
Back
Top