Formula help needed to pull number from another spreadsheet

I have WorkbookA that pull numbers from WorkbookB. In Cell D2 of WorkbookA is the name of WorkbookB. The data to be pulled from WorkbookB is in Cells H364 through S364, H being data for January and S being data for December. In January, the data from Cell H364 is pulled into WorkbookA. In February, the data from Cell I364 is pulled into WorkbookA, and so on.

Hopefully, the formula can be written such that, based on WorkbookA indicating in Cell B14 the current month, each month pulls the correct month's data from these cells in WorkbookB.

I sure hope this is clear information.

Please help if you can.

Thanks.

Can't you just use a plus the other spreadsheet? If not give me a quick spreadsheet so I can help.

I'm not quite sure I understand what you are saying. But I'll try to elaborate on my problem.

WorkbookA is a massive workbook containing data for multiple businesses; Column A is Business 1, Column B is Business 2, and so on.
WorkbookA pulls data from actually 3 other workbooks; each workbook name contained in Cells D2, D3, and D4 of WorkbookA.
The 3 workbooks referenced above contain current year, last year, and budget year data.
The workbooks are very large and cannot be uploaded on this site.

Obviously, I could use a simple formula such as = a particular cell on WorkbookB.

However, that will not work because then the formula for all companies has to be changed each month so the correct data pulls in since each of the 3 workbooks contain different data for each month during the year.

We have to make it simple enough to know where to pull the data from without needing to change the formula each month.

I know I'm not making it easy but I'm not meaning to and I'm working on a solution myself but to no avail. I can get the formula to not give me an error, however, it is not pulling the data.

Hi,

i'm not sure if I could zip the two files and send them without them disrupting the formula, so have a look at this screenshot (attached) and see if you can get anything from it....

Thanks and I think I'm getting there.
I'm not getting an error but still not getting the correct information.
My formula currently reads:

=IFERROR(HLOOKUP(B$14,INDIRECT("($D$2&!$H$364:$S$364"),2,0),"")

I think the B$14 in the formula is not correct. Now I'll try to explain.

In Cell B14 is a 1, which is for Month 1.
In Cell D14 is the word "January", also for Month 1.
In WorkbookB where the data is pulled from, Jan data is in Column 8.

So, somehow I think it still doesn't know from WorkbookA, where to pull from in WorkbookB.

I see how the example you sent works.

Will you please help more?

Yes, B$14 should then be D$14 since the HLOOKUP is trying to find the month NAME, not month NUMBER.
This also means that the month names in BookB needs to be Fullname months (or conversly, the month name in BookA must be abbreviated). IOW: The must be a match in month name length.

In WorkbookB where the data is pulled from, Jan data is in Column 8. (Don't you mean ROW 8???)

About your formula:
=IFERROR(HLOOKUP(B$14,INDIRECT("($D$2&!$H$364:$S$364"),2,0),"")

- B$14 should then be D$14
- You are missing the entire sheet and range reference that should point to BookB. The INDIRECT formula should have a reference to [BookB]Sheet1!...
- The formula assumes that the row of data you need to collect has column headings (JAN > DEC) above it...this is important so the HLOOKUP can identify which value to return for which month. That is why I had month names in my image in row 19. It scans row 19 for the month name give in cell D14 of BookA and when it finds the month name in row 19, it pulls the value from row 20.
Your BookB must have a row of Month names for this to work! You cannot just use: $H$364:$S$364

I abbreviated the month name.

Actually the Jan data I need to pull for this field is in Row 364. Column 8 is where the Jan data is. Column 9 would then contain Feb data.
WorkbookB actually contains about 400 rows of Jan data. Data for each account in the General Ledger and for inventory counts. The inventory count on Row 364 is what I'm trying to currently pull.

I'm sorry for making this as clear as mud. But these workbooks are very involved and contain much data.

Actually the row of data we need to collect does have column headings (JAN - DEC) above it. Row 1 has these headings, beginning in Column 8 with JAN.

So, if I use $H$1:$S$364, would it know to pull from Row 364 Column H for JAN.

In regards to the sheet and range reference, please help me. This is the information.

WorkbookB is referenced in WorkbookA in Cell D2. Thereby the $D$2 in the formula.
There are 20 worksheets in WorkbookB. One for each company. They are named Company1, Company2, etc.
Each company has the data in the same locations. So the inventory counts are all on Row 364.

So I knew I needed to include the worksheet name from WorkbookB where the data would need to pull from. but I totally forgot about it.

By the way, thanks for helping me.

My formula now reads:

=IFERROR(HLOOKUP(D$14,INDIRECT($D$2&"Dealer1'!$A$5:$S$498"),2,0),"")

Dealer1 is the specific worksheet in WorkbookB.
A5 - S498 is the entire range of the Dealer1 worksheet in WorkbookB.
Row 364 Column 8 contains the specific JAN data I'm trying to retrieve.

I am still not receiving an error but it is still not giving me the correct answer in WorkbookA.

I'm thinking now the ,2 at the end of the formula does not tell where to pull the data from, however, I've tried several changes and still not working.

I'll continue to work on it but any help will be appreciated.

> .. I'm thinking now the ,2 at the end of the formula does not tell where to pull the data from,
One guess ...
In your formula:
=IFERROR(HLOOKUP(D$14,INDIRECT($D$2&"Dealer1'!$A$5:$S$498"),2,0),"")
Try replacing the parameter: 2
with this term: COLUMNS($A:A)+1
Then copy across ....
---------------------------------------
Any good? Wave it, whack the little star at the bottom left of my responses

Tried but to no avail. I'm still working on it though.

I should have also added that I'm sure it is something I am doing wrong.

Max, thanks for your help.

I'm not sure I understand.

All of the workbooks are open.

They are all open. WorkbookA, along with the Current Year workbook, Last Year workbook, and Budget Year workbook.

WorkbookA has an enormous amount of formulas. It is 2500 hundred rows with Columns A thru AY and has formulas in almost every field. These formulas pull from the CY workbook, LY workbook and BY workbook. Many of the formulas are INDIRECT formulas and all are working fine.

I presume the fact this formula is giving me so much trouble is that I haven't worked with formulas in quite some time. But I need to get this formula to work asap. So I'll continue trying until it works. Any help will be appreciated.

Thanks to those who have helped already!

Although the other INDIRECT formulas on WorkbookA are working, this formula needs to be linked to a specific Column in a specific Row.

I feel I am not doing very good at explaining it all, but I'm trying.

The image in this thread did not come from me. It came from someone trying to help me since the workbooks I have are too large to upload.

It is proving very difficult for me to do a cut down version that will allow you to help me. I'll just need to continue to work the problem unless you or someone else can help me. I apologize.

I am still having problems and this formula is crutial.
Is there someone to whom I can upload the files for assistance?
Again, they are too large to upload on the forum and I can't upload a portion and still give you the ability to understand my need.
Thanks.

My formula currently reads:

=IFERROR(HLOOKUP(P$1,INDIRECT("["&$D$2&"]Dealer1!$A$1:$S$364"),$H$364,0),"")

Cell P1 of WorkbookA is the month: "JAN"

WorkbookB, where the data is pulling from, is referenced in Cell D2 of WorkbookA.
The specific worksheet in WorkbookB the data needs to pull from is named Dealer1.
The Dealer1 worksheet has month names (JAN, FEB, etc.) in Row 1, beginning with Column H for JAN.
The data to be pulled is on Row 364 and, since we are trying to pull data for JAN, it is found in Column H on Row 364.
The other months of FEB thru DEC will be found in Columns I - S on Row 364.

We are trying to write the formula so that each month, based on the month information in Cell P1 of WorkbookA, the correct data will pull into WorkbookA.

Please help if you can.